Кому-то тема данной статьи может показаться странной, но тем не менее такая возможность существует. Информация в сети есть, но она почти вся на английском и в разрозненном виде. Чтобы сложить картинку воедино мне с коллегой пришлось немало побродить по интернетам, поэтому я бы хотел для себя лично зафиксировать эту информацию в письменном виде.
В рамках цикла статей по данной теме, мы пошагово создадим небольшое приложение в виде чек-листа задач, в котором будут возможности создания, чтения, обновления и удаления элементов (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 формате
}
Если требуется получить список задач с определенным статусом или за определенные даты, то мы можем пробежаться по полученному массиву в цикле и выбрать только нужные строки.
Первая публикация
Теперь мы готовы для публикации и первого теста. Отмечу, что первая публикация потребует чуть больше времени и действий, чем последующие обновления. Это связано с тем, что для работы нашего приложения в сети мы, как владельцы таблицы должны предоставить необходимые разрешения скрипту для работы с ней. Поехали.
- На верхней панели редактора скрипта выбираем пункты: “Опубликовать - Развернуть как веб приложение”.
- В поле “Как запускать приложение” выбираем “От моего имени (ваша почта)”.
- В поле “Кто имеет доступ к приложению” выбираем “Все, включая анонимных пользователей” и нажимаем “Развернуть”.
- Жмем “Предоставить разрешение”.
- Выбираем свой аккаунт.
- Жмем на ссылку “Дополнительные настройки”.
- Далее ссылка “Перейти на страницу “Название проекта” (небезопасно)”.
- На запрос “Приложение “Название проекта” запрашивает разрешение на доступ к вашему аккаунту Google” нажимаем “Разрешить”.
- И, наконец, увидев заветное “Этот проект развернут как веб-приложение”, сохраняем куда-нибудь текущий 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, то… ничего не произойдет, т.к. мы не переопубликовали наш проект. Чтобы опубликовать проект с последними изменениями делаем следующее:
- На верхней панели редактора скрипта выбираем пункты: “Опубликовать - Развернуть как веб приложение”.
- В поле “Версия проекта” выбираем “Новый” и нажимаем на кнопку “Развернуть”.
- Описываем изменения (необязательно).
- В окне “Этот проект развернут как веб-приложение” нажимаем на кнопку “ОК”.
