Сохранение данных веб-формы в Google таблицы

Таблицы Google представляют собой удобный инструмент совместного просмотра\редактирования каких-либо данных. При помощи небольшого скрипта в Google Apps мы напишем обработчик, который будет служить action’ом для любой формы на сайте и сохранять информацию в google-таблицу. Его можно использовать, например, для абсолютно анонимного опроса, отправляя данные из формы сразу в облако, минуя backend-часть сайта.

1. В google spreadsheets создаем новый документ. Далее нужно выполнить 3 простых действия:
a. дать листу нормальное название. Например, Answers
b. заполнить первую строку таблицы идентификаторами полей формы, которую мы будем отправлять. В примере это input1, input2 и input3
c. скопировать id документа из адресной строки. Он находится между /d/ и /edit

2. Переходим в Script Editor:

3. В поле для скрипта копируем код:

// обработчик get-запросов
function doGet(e){
    return handleResponse(e);
}

// обработчик post-запросов
function doPost(e){
    return handleResponse(e);
}

// основной метод
function handleResponse(e) {
    // блокировка скрипта от одновременного выполнения в нескольких копиях
    var lock = LockService.getPublicLock();
    lock.waitLock(30000);

    try {
        // идентификатор документа, куда мы будем записывать данные
        // можно сделать константой или передавать в запросе. Например, в параметре GoogleSheetId
        var doc = SpreadsheetApp.openById(e.parameter["GoogleSheetId"]);
        // название листа в документе, куда будут записываться данные
        var sheet = doc.getSheetByName("Answers");
        // заголовки столбцов из первой строки
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        // номер последней заполненной строки + 1, туда запишутся данные
        var nextRow = sheet.getLastRow()+1;
        // контейнер для записываемых данных
        var row = [];

        // цикл по заголовкам столбцов таблицы
        for (i in headers) {
            // значение столбца с датой записывается с использованием встроенного типа данных DateTime или Date
            if (headers[i] == "timestamp") {
                row.push(new Date());
            } else {
                // e.parameter - массив параметров запроса
                // ключи должны совпадать с заголовками столбцов таблицы
                var val =  e.parameter[headers[i]];
                // если в запросе нет нужного значения для столбца - записываем пустую строку
                if (val === undefined) {
                    val = '';
                }
                row.push(val);
            }
        }
        // записываем данные в строку nextRow
        sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
        // в ответ вернем json с номером строки и флаге успешности запроса
        return ContentService
        .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
        .setMimeType(ContentService.MimeType.JSON);
    } catch(e) {
        // в случае ошибки вернем json с флагом неуспешности запроса и выброшенным исключением
        return ContentService
        .createTextOutput(JSON.stringify({"result":"error", "error": e}))
        .setMimeType(ContentService.MimeType.JSON);
    } finally {
        // снимаем блокировку со скрипта
        lock.releaseLock();
    }
}

Код содержит довольно много комментариев и должно быть все понятно :) Если в двух словах, основная часть алгоритма — цикл по заголовкам столбцов первой строки таблицы. В нем мы ищем соответствующие значения из параметров запроса и формируем строку для записи в документ.

4. Сохраняем приложение с произвольным именем

5. Далее нам нужно опубликовать скрипт как веб-приложение — меню Publish — Deploy as web app… Здесь важно в последнем вопросе дать возможность выполнять скрипт всем без авторизации

6. При первом сохранении Google запросит разрешения на изменение документов от имени приложения

7. Скрипт мы писали сами, поэтому верификация не нужна. Можно смело нажимать Got project (unsafe)

8. Разрешаем доступ

9. Финальный шаг — копируем ссылку. Можно вставлять ее в action формы на сайте и сохранять данные в google таблицу

Для проверки работы проще всего добавить в ссылку приложения нужные get-параметры и перейти по ней в браузере.

Например, ссылка из моего примера с параметрами input1=hello и input2=world — https://script.google.com/macros/s/AKfycbyXTAN1BX1ORA6gtuGvqqgo4ZFizT35QiFiucZhvRLu_XLXiw/exec?GoogleSheetd=1X_nDKOYI-DGhJ5-qDWND_R8JZNi-89NivoR8NGZ0YsU&input1=hello&input2=world

После перехода данные сразу появятся в файле https://docs.google.com/spreadsheets/d/1X_nDKOYI-DGhJ5-qDWND_R8JZNi-89NivoR8NGZ0YsU/edit?usp=sharing

Источник вдохновения: https://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/