Google таблицы

Содержание:

• Действия
  • Получить строку по номеру
  • Поиск строки по значению столбца
  • Получить значения ячеек заданного диапазона
  • Добавить значения с новой строки
  • Обновить строку
• Триггеры
  • Общая информация
  • Изменение таблицы (автоматическое и ручное)
  • Редактирование таблицы (только ручное)
• Подключение
  • Настройка Google Sheets API в Google
  • Настройка подключения в APInita

Действия

Получить строку по номеру

Возвращает значение столбцов от A до Z по указанному номеру строки.

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

Указывается название столбца (например D) и значение для поиска. Значение может использовать символ * для поиска по вхождению: например, для поиска значения Иван можно использовать Ива*, *ван или *ва*. Метод вернет первую строку соответствующую значению поиска. Найденный номер строки находится в массиве:

{{ s1['body']['updatedData']['values']['0']['0'] }}

Важно! Формула для поиска прописывается на листа таблицы в столбце АА1. Если значение не будет найдено, метод все равно вернет массив, но в значении будет не номер строки, а ошибка:

#N/A (Did not find value 'Иван' in MATCH evaluation.)

Необходимо корректно обрабатывать ответ перед дальнейшим выполнением сценария, например через IF модуль:

{{ '#N/A' not in s1['body']['updatedData']['values']['0']['0'] }}

или PHP:

$response = $s1['body']['updatedData']['values']['0']['0'];
if (stripos($response, '#N/A') !== false) {
    return [];
}

return [
    'line' => $response,
];

Получить значения ячеек заданного диапазона

Данное действие возвращает значения ячеек заданного диапазона в виде вложенных массивов по строкам или по столбцам.

Необходимо заполнить поля:

• ID таблицы;
• Название листа;
• Диапазон ячеек: можно указать только буквы столбцов, например A:D. В этом случае метод вернет все заполненные строки указанных столбцов;
• Измерение: строки или столбцы;
• Подключение (см. раздел "Подключение" ниже).

Измерением определяется то, как данные будут распределены по массивам.

К примеру, данная таблица может быть представлена по-разному

В измерении строк:

В измерении столбцов:

Добавить значения с новой строки

Данное действие построчно добавляет данные в таблицу с новой строки

Необходимо заполнить поля:

• ID таблицы;
• Название листа;
• Значения ячеек строки по столбцам от A до Z
• Подключение (см. раздел "Подключение" ниже).

Обновить строку

Данное действие изменяет значения ячеек в заданной строке

Необходимо заполнить поля:

• ID таблицы;
• Название листа;
• Номер строки;
• Режим передачи незаполненных полей: "Игнорировать" или "Заменять значения пустыми ячейками";
• Значения ячеек строки по столбцам от A до Z;
• Подключение (см. раздел "Подключение" ниже).

Режим "Игнорировать - значения ячеек не изменяются" производит изменение строки только в тех столбцах, поля которых заполнены (не пусты).

Режим "Заменять значения пустыми ячейками" производит изменение всей строки от A до Z - незаполненные поля очищают ячейки в соответствующих столбцах.

Триггеры

Общая информация

Чтобы осуществлять отправку событий из конкретной таблицы, необходимо создать прикрепленный к ней проект Apps Script. Из открытой таблицы переходим в "Расширения -> AppsScript":

Откроется редактор кода с шаблоном функции:

Вместо шаблона функции разместим следующий код, после чего сохраним проект:

const webhookURL = "{scenario_webhook_URL}";
const versionScript = '1.0.0';
const scriptProperties = PropertiesService.getScriptProperties();

function onChangeSendHTTP(e) {

  var sendHTTP = true;
  var activeSheet = e.source.getActiveSheet();
  var currentChange = e.source.getActiveRange();

  var payloadContents = {
      'triggerType': 'onChange',
      'changeType': e.changeType,
  };

  if (e.changeType == 'EDIT' || e.changeType == 'INSERT_ROW' || e.changeType == 'INSERT_COLUMN' || e.changeType == 'REMOVE_ROW' || e.changeType == 'REMOVE_COLUMN') {  
    payloadContents.rangeA1Notation = currentChange.getA1Notation();    
    payloadContents.range = {
      'rowEnd': currentChange.getEndRow(),
      'rowStart': currentChange.getRowIndex(),
      'columnEnd': currentChange.getEndColumn(),
      'columnStart': currentChange.getColumnIndex(),
    }
    payloadContents.values = currentChange.getValues();
    payloadContents.sheetName = e.source.getSheetName();
    payloadContents.sheetID = e.source.getSheetId();
  } else if (e.changeType == 'INSERT_GRID' || e.changeType == 'REMOVE_GRID') {
      var getSheetsArray = e.source.getSheets();
      sheetsListArray = [];
      for (n in getSheetsArray) {
        let sheetname = getSheetsArray[n].getSheetName();
        let sheetid = getSheetsArray[n].getSheetId();
        sheetsListArray.push({'name': sheetname, 'id': sheetid});
      }
      payloadContents.sheetsList = sheetsListArray;
  } else if (e.changeType == 'FORMAT' || e.changeType == 'OTHER') {
        sendHTTP = false;
  }

  if (sendHTTP == true) {
    UrlFetchApp.fetch(webhookURL, {
      'method': 'post',
      'contentType': 'application/json',
      'headers': {
        'version-Script': versionScript
      },
     'payload': JSON.stringify(payloadContents)
    });
  }
}

function onEditSendHTTP(e) {
  var currentEdits = scriptProperties.getProperty('edits');
  if (currentEdits == null || currentEdits == "null" || typeof(currentEdits) == 'null') {
    currentEdits = {"triggerType": "onEdit", "contents": []};
  } else {
    currentEdits = JSON.parse(currentEdits);
  }
  currentEdits.contents.push({    
    'range': e.range,
    'rangeA1Notation': e.range.getA1Notation(),
    'value': (e.value) ? (e.value) : (""),
    'sheetName': e.source.getSheetName(),
    'sheetID': e.source.getSheetId()  
  });
  scriptProperties.setProperty('edits', JSON.stringify(currentEdits));

  Utilities.sleep(10000);

  currentEdits = scriptProperties.getProperty('edits');
  if (currentEdits) {    
    UrlFetchApp.fetch(webhookURL, {      
      'method': 'post',
      'contentType': 'application/json',
      'headers': {
        'version-Script': versionScript
      },
      'payload': currentEdits
    });    
    scriptProperties.deleteProperty('edits');
  }
}

В первой строке заменить {scenario_webhook_URL} (вместе с фигурными скобками) на URL вида https://wh.apinita.ru/983848c.....d1bc/925/57/93, скопированный при создании триггера или модуля Webhook в сценарии APInita.

В функции onEditSendHTTP() в выражении Utilities.sleep(10000); значение 10000 означает время в милисекундах (10 секунд), в течение которого изменения собираются в одно событие. Данное значение можно изменить, или вообще удалить строку из кода, чтобы каждое изменение передавалось отдельным событием.

Далее из левого меню перейдем в раздел "Триггеры" и добавим триггер для функции onChangeSendHTTP (тип события: При изменении), затем таким же образом - для функции onEditSendHTTP (тип события: При редактировании):

Далее выбрать свой аккаунт:

Развернуть ссылку Advanced и перейти к проекту:

И, наконец, разрешить доступ (Allow):

Окно закроется и новый триггер появится в списке:

После добавления триггеров настройка Google Apps Script завершена.

Примечание: если в сценарии APInita будет использован не специальный триггер сервиса Google Sheets, а общий модуль Webhook, события будут приниматься по каждому добавленному в Apps Script триггеру. Это значит, что события могут повторяться - например, при ручном вводе значений сработают оба триггера: "при редактировании" и "при изменении". В зависимости от поставленных задач вы можете добавлять в Apps Script только необходимые триггеры.

Изменение таблицы (автоматическое и ручное)

Событие отправляется как при автоматическом изменении значений ячеек, так и при ручном вводе. Данные содержат тип триггера (onChange) и тип изменения.

При типах изменений: редактировать, вставить строку, вставить столбец, удалить строку, удалить столбец - данные содержат название и ID листа, диапазон и значения (если доступны).

При типах изменений: создать лист, удалить лист - данные содержат массивы существующих листов, содержащие название и ID листа.

Редактирование таблицы (только ручное)

Событие отправляется при ручном изменении значений ячеек пользователем. Данные содержат тип триггера (onEdit), название и ID листа, и массив изменений, содержащий значения и соответствующие диапазоны.

Подключение

Настройка Google Sheets API в Google

1. Переходим на https://console.cloud.google.com/ Нажимаем кнопку выбора проекта (1) и далее кнопку "New project" (1):

2. В открывшемся окне указваем название проекта, например "APInita Google Sheets" и нажимаем кнопку "Create":

3. Переходим в созданный проект (3):

4. Нажимаем кнопку "ENABLE APIS AND SERVICES" (4):

5. В строке поиска (5) вводим "sheets", жмем Enter и выбираем "Google Sheets API" (6):

6. Нажимаем "Enable" (7):

7. Переходим в раздел "OAuth consent screen" (8), выбираем "Extenral" (9) и жмем "Create":

8. Укажите название приложения (10), например "APInita Google Sheets", Ваш Email (11):

Промотайте вниз страницы и так же укажите Ваш Email (12) и нажмите кнопку "SAVE AND CONTINUE":

9. Нажмите кнопку "ADD OR REMOVE SCOPES" (13), введите в строку поиска (14) "Google Sheets API", установите галочку у поля Google Sheets API (15) и сохраните:

Далее внизу страницы нажимаем "SAVE AND CONTINUE" и переходим на следующий шаг.

10. Нажимаем кнопку "ADD USERS" (16), добавляем Ваш Email (17), нажимаем кнопку "ADD", а затем "SAVE AND CONTINUE":

11. Далее переходим в раздел "Credentials" (18), нажимаем "CREATE CREDENTIALS" (19) и выбираем "OAuth client ID" (20):

12. Выбираем тип приложения "Web application" (21), указываем имя, например "APInita" (22), позже так же добавим redirect URI (23 - вернемся к этой части дальше в инструкции) и нажимаем внизу кнопку "CREATE":

13. Скопируйте полученные Client ID (24) и Secret ID (25):

Настройка подключения в APInita

Переходим в раздел "Подключения" (1) и нажимаем "+" (2):

В типе подключения выбираем "OAuth 2.0" (4), вводим название подключения (3) и нажмимаем "Начать настройку":

Далее заполняем поля:

1. Cliend ID (1): значение, полученное в п. 13 (24);

2. Client Secret (1): значение, полученное в п. 13 (25);

3. URL авторизации (3):

https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&response_type=code&client_id={{ client_id }}&nonce={{ random() }}&redirect_uri=https://cabinet.apinita.ru/connections/17&scope=https://www.googleapis.com/auth/spreadsheets

Здесь "https://cabinet.apinita.ru/connections/17" заменить на значение из поля "URL для редиректа после авторизации" (16).

4. URL для получения Access Token (4):

https://oauth2.googleapis.com/token

5. Метод: POST. Тип данных: application/x-www-form-urlencoded

6.

grant_type: authorization_code
client_id: {{ client_id }}
client_secret: {{ client_secret }}
redirect_uri: https://apinita.ru/connections/17
code: {{ code }}

Здесь "https://cabinet.apinita.ru/connections/17" заменить на значение из поля "URL для редиректа после авторизации" (16).

7. Оставьте поля пустыми;

8. Оставьте поля пустыми;

9. Authorization: Bearer {{ access_token }}

10. Оставьте поля пустыми;

11. 401

12. URL для обновления токена: https://oauth2.googleapis.com/token

13. Метод: POST. Тип данных: application/x-www-form-urlencoded

14.

grant_type: refresh_token
client_id: {{ client_id }}
client_secret: {{ client_secret }}
refresh_token: {{ refresh_token }}

15. Оставьте поля пустыми;

16. Скопируйте значение данного поля и установите его в настройках Google в пункт 12 (23).

После ввода всех данных - нажмите кнопку "Сохранить и авторизоваться", перед Вами появится окно, выберите в нем свой аккаунт Google:

Далее нажмите кнопку "Продолжить":

Далее так же нажмите "Продолжить":

Если все прошло корректно, Вы вернетесь на страницу подключения в APInita, а статус подключения будет "Подключено":

Для справки: если Вам потребуется отменить подключение и/или переподключиться, сделать это можно по ссылке: https://myaccount.google.com/connections Для повторного подключения снова нажмите кнопку "Сохранить и авторизоваться" в подключении APInita.