Google таблицы как база данных для веб-приложения. Часть 1

Кому-то тема данной статьи может показаться странной, но тем не менее такая возможность существует. Информация в сети есть, но она почти вся на английском и в разрозненном виде. Чтобы сложить картинку воедино мне с коллегой пришлось немало побродить по интернетам, поэтому я бы хотел для себя лично зафиксировать эту информацию в письменном виде.

В рамках цикла статей по данной теме, мы пошагово создадим небольшое приложение в виде чек-листа задач, в котором будут возможности создания, чтения, обновления и удаления элементов (CRUD). В качестве БД мы будем использовать Google Sheets, а в качестве серверного языка — Google Apps Script.

У этого подхода есть ряд своих минусов и ограничений, о которых я обязательно расскажу в процессе.

Создание скрипта и подключение к таблице

Для начала нам необходимо создать новую таблицу (сделать это можно из Google Drive, авторизовавшись под учетной записью Google). Назовем саму таблицу “tasklist”, а единственный лист — “tasks”.

Возвращаемся в Google Drive и создаем новый Google Apps Script (“Создать — Еще — Google Apps Script”). Назовем его также: “tasklist”.

Теперь переходим к написанию первого кода. В самом начале объявляем две переменные. Первая будет отвечать за подключение к таблице по URL. (Я не проверял, но скорее всего для успешного подключения вы должны являться и владельцем таблицы и владельцем скрипта).

//Получаем доступ к таблице по ссылке с уникальным идентификатором
var tasklist = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1M1OKuBxGoЧXXXXXXXXXXXXXXXXXXXXXXXXXXXS3EWj6Bs/edit#gid=0");
//Получаем доступ к странице по ее имени
var tasks = tasklist.getSheetByName("tasks");

Роутинг

Наш скрипт будет выполнять более чем одну функцию, ведь мы запланировали возможности создания, чтения, редактирования и удаления данных. Для этого мы должны как-то объяснить нашему скрипту, какое действие при каком запросе ему следует выполнять. За прослушивание запросов, отправленных методом POST у нас будет отвечать стандартная функция doPost (e), а для запросов, отправленных методом GET doGet (e), соответственно. Для маршрутизации запросов мы разместим внутри них switch, которые будут принимать переменную operation и в зависимости от того, какое мы передадим туда значение, тот или иной switch будет перенаправлять входящие запросы на соответствующие функции.

//Стандартная функция Google Apps Script для прослушивания входящих запросов, отправленных методом POST
function doPost (e) {
  var operation = e.parameter.action;//получаем параметр "action"
  
  switch (operation) {
    case "addTask": return addTask (e);
  }

}

//Стандартная функция Google Apps Script для прослушивания входящих запросов, отправленных методом GET
function doGet (e) {
  var operation = e.parameter.action;//получаем параметр "action"
  
  switch (operation) {    
    case "getTasks": return getTasks ();
  }

}

Добавление новых строк

Теперь мы готовы к тому, чтобы написать код, позволяющий с нашей таблицей конкретно взаимодействовать. К сожалению, Google Apps Script не поддерживает классические SQL команды, типа: INSERT, SELECT, UPDATE, DELETE, но все это сделать возможно немного иными путями и командами. Давайте посмотрим на примере функции, где для добавления новой строки мы будем использовать метод appendRow():

//Функция, отвечающая за добавление новых задач
function addTask (e) {
  var dateTime = Utilities.formatDate(new Date(), "GMT+4", "dd.MM.yyy HH:mm:ss");//определяем 
  дату в нужном формате и часовом поясе
  var task = e.parameter.task;//получаем название задачи в переданном параметре
  var status = 0; //ноль будет обозначать статус "ожидает", так как при создании задачи, она 
  не может быть уже выполненной
  
  tasks.appendRow([dateTime,task,status]); //обращаемся к нашей странице “tasks” определяем 
  крайнюю свободную строку и вставляем полученные значения. Аналогично INSERT.
  return ContentService.createTextOutput('Задача успешно добавлена!');//возвращает в ответ 
  текстовое сообщение об успехе
}

Получение строк из таблицы

Получить строки из таблицы можно двумя разными способами. В этой части мы рассмотрим самый простой из них, который подразумевает, что мы можем просто выбрать массив данных и вернуть его в виде JSON. Для этого мы будем использовать комбинацию методов getRange() и getValues().

//Функция, отвечающая за получение строк и отправку данных клиенту
function getTasks () {
  var lastrow = tasks.getLastRow();//получаем номер последней строки в таблице
  var data = tasks.getRange("A1:C" + lastrow).getValues();//получаем массив нужных колонок
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);//возвращает в ответ полученные данные в JSON формате
}

Если требуется получить список задач с определенным статусом или за определенные даты, то мы можем пробежаться по полученному массиву в цикле и выбрать только нужные строки.

Первая публикация

Теперь мы готовы для публикации и первого теста. Отмечу, что первая публикация  потребует чуть больше времени и действий, чем последующие обновления. Это связано с тем, что для работы нашего приложения в сети мы, как владельцы таблицы должны предоставить необходимые разрешения скрипту для работы с ней. Поехали.

  1. На верхней панели редактора скрипта выбираем пункты: “Опубликовать — Развернуть как веб приложение”.
  2. В поле “Как запускать приложение” выбираем “От моего имени (ваша почта)”.
  3. В поле “Кто имеет доступ к приложению” выбираем “Все, включая анонимных пользователей” и нажимаем “Развернуть”.
  4. Жмем “Предоставить разрешение”.
  5. Выбираем свой аккаунт.
  6. Жмем на ссылку “Дополнительные настройки”.
  7. Далее ссылка “Перейти на страницу “Название проекта” (небезопасно)”.
  8. На запрос “Приложение “Название проекта” запрашивает разрешение на доступ к вашему аккаунту Google” нажимаем “Разрешить”.
  9. И, наконец, увидев заветное “Этот проект развернут как веб-приложение”, сохраняем куда-нибудь текущий URL приложения и нажимаем на кнопку “ОК”.

Первая версия нашего приложения развернута и уже готова к работе. Проверить это можно, например, через Postman. Для этого в качестве адреса используем недавно полученный URL-адрес нашего приложения.

Как видим, запись в табличке появляется.

Удаление строк

Для удаления строк нам по аналогии с первой функцией требуется добавить case в наш switch внутри  doPOST(e) и написать соответствующую функцию. Поехали:

function doPost (e) {
  var operation = e.parameter.action;
  
  switch (operation) {
    case "addTask": return addTask (e);
    case "deleteTask": return deleteTask (e);
  }
}

Теперь создадим функцию, отвечающую за удаление строк. Чтобы удалить строку, нам для начала необходимо ее найти. В нормальных СУБД для этого используются уникальные id, которые автоматически присваиваются строкам при добавлении, и по которым можно впоследствии однозначно найти элементы. В нашем случае такой роскоши не предусмотрено. Конечно, можно придумать какой-нибудь выход и, например, при добавлении строк генерировать уникальный хеш-код на основе текущей даты и еще чего-нибудь, но в рамках данной статьи мы будем считать, что название задачи должно быть уникальным. По нему мы и будем искать элементы, пробегаясь в цикле по нашим записям, и удалять записи при помощи метода deleteRow().

//Функция, отвечающая за удаление задач
function deleteTask (e) {
  var task = e.parameter.task;//получаем название задачи в переданном параметре
  var lastrow = tasks.getLastRow();//получаем номер последней строки в таблице
  var array = tasks.getRange("B1:B" + lastrow).getValues();//получаем массив указанных ячеек 
  колонки, в которой будем искать соответствие
  
  for (var i = 0; i <= array.length; i++) {
    if (array[i] == task) {//если элемент соответствует искомому в массиве, то...
      tasks.deleteRow(i+1);//обращаемся к нашей странице “tasks” и удаляем строку, в которой 
      было найдено совпадение. Прибавляем единичку, т.к. это был массив и у него нумерация 
      идет с нуля...
      break;//завершаем цикл, т.к. мы нашли что искали и сделали, что хотели
    }
  }
    
  return ContentService.createTextOutput("Задача успешно удалена!");
}

Обновление строк

Принцип тот же. Начинаем с doPost(e) и далее добавляем соответствующую функцию.

function doPost (e) {
  var operation = e.parameter.action;//получаем параметр "action"
  
  switch (operation) {
    case "addTask": return addTask (e);
    case "deleteTask": return deleteTask (e);
    case "updateTask": return updateTask (e);
  }

}

Алгоритм обновления данных похож на тот, что мы использовали при удалении элементов. Только в случае обновления нам потребуются два дополнительных параметра. Первый будет передавать новое значение, на которое мы будем заменять старое, а второй будет подсказывать нам в какой колонке это следует сделать. За обновление данных отвечает комбинация методов getRange() и setValue().

//Функция, отвечающая за обновление задач
function updateTask (e) {
  var task = e.parameter.task;//получаем название задачи в переданном параметре
  var newValue = e.parameter.newValue;//получаем новое значение в переданном параметре
  var where = e.parameter.where;//получаем название колонки, в которой будем заменять старое 
  значение новым
  var lastrow = tasks.getLastRow();//получаем номер последней строки в таблице
  
  switch(where){
    case "task":
      var col = "B";
      break;
    case "status":
      var col = "C";
      break;
  }
  
  var array = tasks.getRange("B1:B" + lastrow).getValues();//получаем массив указанных ячеек 
  колонки, в которой будем искать соответствие
  
  for (var i = 0; i <= array.length; i++) {
    if (array[i] == task) {//если элемент соответствует искомому, то...
      tasks.getRange(col + (i+1)).setValue(newValue);//обращаемся к нашей странице “tasks” и 
      обновляем нужную колонку, в которой было найдено совпадение
      break;//завершаем цикл, т.к. мы нашли что искали и сделали, что хотели
    }
  }
  
  return ContentService.createTextOutput("Задача успешно обновлена!");
}

Обновление проекта

Если мы сохраним изменения и захотим протестировать написанный код, отправив запросы на удаление или обновление через Postman, то… ничего не произойдет, т.к. мы не переопубликовали наш проект. Чтобы опубликовать проект с последними изменениями делаем следующее:

  1. На верхней панели редактора скрипта выбираем пункты: “Опубликовать — Развернуть как веб приложение”.
  2. В поле “Версия проекта” выбираем “Новый” и нажимаем на кнопку “Развернуть”.
  3. Описываем изменения (необязательно).
  4. В окне “Этот проект развернут как веб-приложение” нажимаем на кнопку “ОК”.