Google таблицы
Содержание:
Действия
Получить строку по номеру
Возвращает значение столбцов от 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.
Решения для сервиса Google таблицы
Оставить комментарий