Отправка данных из HTML-формы в Google Sheet

Опубликовано 13 сентября 2020 в 01:38 (Обновлено 2 ноября 2023 в 03:05)

Время чтения: 7 мин

Рассмотрим как отправлять данные из HTML-форм в таблицу Google Sheet используя Google Sheets API и Javascript.

Google Sheet
Google Sheet

Идея

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

Эти данные надо где-то хранить и иметь для этих целей какую-то маркетинговую платформу для хранения всех собранных данных.

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

В итоге, подходящий сервис я не нашел и решил создать его сам. Разберемся дальше как можно всё это достаточно быстро реализовать.

Стек технологий

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

Для меня - это вариант стека MERN с Serverless в качестве фреймворка хостинга.

Serverless в переводе с английского - бессерверный.

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

Boilerplate в переводе с английского - шаблон.

Когда я смотрел на проект, я руководствовался следующими ключевыми моментами:

  1. Нам нужно было использовать HTTP-запрос, чтобы подтвердить ввод формы и выдать ошибку, видимую пользователю.
  2. Если все выглядело хорошо, тогда нам нужно было поговорить с Google об обновлении таблицы. А поскольку это была сторонняя компания, нам нужно было действовать ответственно и ограничивать наши запросы.

Поэтому любое взаимодействие с Google должно происходить в очереди с рабочей функцией. Это идеальное приложение для Serverless и FIFO.

В конечном счете, базовая архитектура, которую я набросал, выглядела следующим образом:

Имея эту структуру, мне нужно было перейти к специфике каждой логической части.

Работа с Google Sheets API

Мой конечная точка HTTP (endpoint) будет получать полезные данные POST, например:

{
    "DOB": "6/20/1997"
    "Name": "Jane Doe",
    "Email": "jane@applemail.com",
}

Мне нужно было преобразовать это в лист вроде:

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

Примечание: Все эти примеры используют API Google Sheets, v4. Документация API.

const { google } = require('googleapis');

class ExecuteSheetUpdateCommand {
  /**
   * @param {string} spreadsheetId ID of the Google Spreadsheet.
   * @param {Object} data Object that contains the data to add to the sheet as key-value pairs.
   * @param {google.auth.OAuth2} auth An Google OAuth client with a valid access token: https://github.com/googleapis/google-api-nodejs-client.
  */
  static async exec(spreadsheetId, data, auth) {
    const sheets = google.sheets({ version: 'v4', auth });

    const rows = [data.Name, data.Email, data.DOB];
    // Add our new data to the bottom of the sheet.
    await sheets.spreadsheets.values.append({
      spreadsheetId,
      range: 'A1',
      valueInputOption: 'RAW',
      insertDataOption: 'INSERT_ROWS',
      resource: {
        values: [rows],
      },
    });
  }
}

Вуаля! С помощью одной простой функции мы автоматически сопоставляли данные форм в Google Sheets.

Очевидно, что эта функция не очень хороша. Он связывает заголовки формы со структурой листа с помощью этой строки:

const rows = [data.Name, data.Email, data.DOB];

Вам действительно не стоит так делать.

Например, если бы я переместил столбец в своей электронной таблице, эта функция продолжала бы вставлять данные на старое место, и на моем листе были бы неверные данные.

Сложнее автоматически сопоставить поля формы с заголовками листа, и я оставляю эту часть для будущих материалов ради этого примера.

Добавление конечной точки REST с помощью SQS worker

Итак, у нас есть функция, которая может отправлять объект JSON в Google Sheet, но как это сделать с помощью HTML-формы? Ответ - HTTP + SQS.

Часть HTTP довольно проста, если вы знакомы с Node и Express.

Вы можете с такой же легкостью развернуть ее на другом Node-friendly окружении, но я покажу вам, как работать с Serverless и AWS.

Я использую пакет aws-serverless-express для отправки моих экспресс-приложений в качестве функций Serverless Lambda. В сочетании с пакетом serverless-api-cloudfront невероятно легко развернуть масштабируемый API.

Вот конечная точка Express HTTP, с которой начинается обновление Google Sheet:

const express = require('express');
const bodyParser = require('body-parser');

// An AWS SQS client
const sqsClient = require('./clients/SQSClient');

const app = express();

app.use(bodyParser.urlencoded({ extended: true }));

app.post('/form/:spreadsheetId', async (req, res, next) => {
  const { spreadsheetId } = req.params; // The Google Sheet ID
  const { body } = req; // The post body

  /* Note: You should run your own custom validation on the 
     * form before forwarding it on. In this example we just continue.
   *
     * At a minimum, make sure you have permission to update the 
   * sheet, otherwise this will break downstream.
     */
  const passedValidation = true;

  if(passedValidation) {
    // Send the data to our SQS queue for further processing
    await sqsClient.createEntry.sendMessage({
      spreadsheetId,
            body,
    });
  } else {
    throw new Error('Invalid form data');
  }

  res.status(200).send('Submitted your form');
});

А вот функция Lambda, которая извлекает данные из регулируемой очереди SQS FIFO и обрабатывает их для Google:

const { google } = require('googleapis');
const ExecuteSheetUpdateCommand = require('../commands/ExecuteSheetUpdateCommand');

exports.handle = async (event, context, callback) => {
  const messages = event.record.body;

  // This little logic helps us throttle our API interactions
  messages.reduce(async (previousPromise, nextMessage) => {
    await previousPromise;
    const { spreadsheetId, body } = nextMessage;
    const accessToken = /* Load a valid access token for your Google user */;
    // Construct an oAuth client with your client information that you've securely stored in the environment
        const oAuth2Client = new google.auth.OAuth2(
      process.env.GOOGLE_CLIENT_ID, process.env.GOOGLE_CLIENT_SECRET, null,
    );
    oAuth2Client.setCredentials({
      access_token: accessToken,
    });
    await ExecuteSheetUpdateCommand.exec(spreadsheetId, body, oAuth2Client);
    return new Promise((resolve) => {
      setTimeout(resolve, 1000); // Throttle for the Google API
    });
  }, Promise.resolve());

  callback();
};

Причина, по которой мы используем SQS с FIFO, а не просто выполняем все это в конечной точке HTTP, заключается в том, что он позволяет нам быстро отвечать пользователю, отправляющему форму, и обновлять лист, как только мы можем, при соблюдении ограничений API.

Если мы не будем думать об ограничениях API, мы можем попасть в ситуации, когда пользователю будет показан экран с ошибкой, как только он отправит форму. И это не хорошо. API Google Таблиц имеет ограничение - «100 запросов в 100 секунд на пользователя», или 1 запрос в секунду - это максимальная скорость, с которой мы можем безопасно взаимодействовать с ним.

SQS FIFO queues позволяют нам помещать обновления наших листов в одну строку, сгруппированную по идентификатору пользователя, и затем мы можем регулировать эти выполнения, используя приведенный выше фрагмент messages.reduce, чтобы убедиться, что мы никогда не превышаем наш лимит в 1 запрос в секунду на пользователя.

И мы также получаем дополнительное преимущество, позволяя AWS выполнять тяжелую работу по регулированию.

Ключевым моментом является то, что при заполнении очереди FIFO надо убедиться, что MessageGroupId установлен на идентификатор пользователя Google, который выполняет запрос OAuth.

Создание формы

Используя комбинацию этих техник и функций, вы должны подойти к тому моменту, где вы можете написать форму HTML, которая вас устроит:

<form action="https://<my-express-endpoint>/form/<my-sheet-id>" method="post">
<input type="email" name="Email" placeholder="Enter your email" required />
<input type="name" name="Name" placeholder="Enter your name" required />
<input type="submit" value="Submit" />
</form>

После чего данные будут волшебным образом отображаться в вашей таблице Google каждый раз, когда она будет отправлена:

На этом всё. Поэкспериментируйте с таблицами и напишите в комментариях как всё прошло.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.