Join
P
@pro_getcourse
April 2, 2023

Передача данных из GetCourse в Google-таблицы

Иногда требуется передавать из Геткурса в Гугл-таблицы данные про заказам, регистрациям, платежам или другим событиям. Есть способ делать это через Гугл-формы. Но тогда мы попадаем на дополнительную возню с созданием формы и работать мы можем только с первым листом таблицы.

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

Сразу обозначу какие есть преимущества в этом решении.

  • Скрипт пишется один раз и используется во всех задачах
  • Запись можно производить в любую таблицу и в любой лист, главное чтобы был доступ на редактирование
  • Поменялась структура таблицы — просто измените поля в запросе

В общем, начнем..

Создание скрипта

У всех есть аккаунт в Гугл, так что просто переходим на https://script.google.com/

И создаем новый проект:

Получаем новый проект. Сразу стоит его как-то назвать.

В проекте сразу создается файл с названием Код.gs и пустой функцией.

Дальше вам нужно просто скопировать следующий код и вставить его вместо пустой функции.

/**
 * Добавление строки в Гугл-таблицу.
 * 
 * Скрипт вызывается GET или POST запросом с параметрами:
 * SSID: идентификатор таблицы
 * SHEET: имя или индекс листа, по умолчанию лист с индексом 0
 * DATA: набор данных, разделенных запятой
 * 
 * Больше скриптов для Геткурса здесь: https://t.me/pro_getcourse
 */

function doGet(request){
  return doRequest(request)
}

function doPost(request){
  return doRequest(request)
}

function doRequest(request) {
  try {
    const sheet = findSheet(request)

    const DATA = request.parameter['DATA']
    if( !DATA ) throw {code: 'DATA_NOT_DEFINED', message: 'No DATA'}

    const data = [...DATA.matchAll(/(?:"(.*?)"|([^,]*))(?:,?|$)/g)]
      .filter(m => m[0])
      .map(m => (m[1] ?? m[2] ?? '').trim())
      
    const lock = LockService.getScriptLock()
    lock.waitLock(60000)
    sheet.appendRow(data)
    lock.releaseLock()

    return sendResult()
  } catch ( error ) {
    console.log(error)
    return sendError(error)
  }
}


function findSheet(request) {
  const SSID = request.parameter['SSID']
  if( !SSID ) throw {code: 'SSID_NOT_DEFINED', message: 'SSID is not defined'}

  const SHEET = request.parameter['SHEET'] || '0'
  if( !SHEET ) throw {code: 'SHEET_NOT_DEFINED', message: 'SHEET is not defined'}

  let ss
  try {
    ss = SpreadsheetApp.openById(SSID)
    if( !ss ) throw ''
  } catch ( e ) {
    console.log(e)
    throw {code: 'SSID_NOT_FOUND', message: `SpreadSheet is not found: ${SSID}`}
  }

  let sheet = ss.getSheetByName(SHEET) ?? ss.getSheets()?.[parseInt(SHEET)]
  if( !sheet ) throw {code: 'SHEET_NOT_FOUND', message: `Sheet is not found: ${SHEET}`}

  return sheet
}

function sendResult(res) {
    return json({success: 1, data: res})
}

function sendError(error) {
    return json({success: 0, error})
}

function json(res) {
  return ContentService.createTextOutput(JSON.stringify(res)).setMimeType(ContentService.MimeType.JSON)
}

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

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

И так, скрипт добавили, что дальше?

Дальше нужно не забыть сохранить! Автосохранение не работает!

Для сохранения можно нажать Ctrl+S или кнопку в панели инструментов.

Развертывание скрипта

Следующий шаг — развертывание. При развертывании скрипт запускается в специальном контейнере приложений и после этого будет готов обрабатывать наши запросы.

Для обработки входящих запросов нам нужно Веб-приложение.

Далее важные моменты!

  1. В описании можно ничего не указывать. Если вы в дальнейшем делаете какие-то изменения в скрипте, а потом делаете новое развертывание, тогда в описании можно указать что вы изменили. Но это не обязательно, поэтому сейчас оставляем поле пустым.
  2. Веб-приложение запускаете от своего имени! Потому что когда запрос будет выполняться из процесса Геткурса, то процесс конечно же не будет авторизован. Но запрос должен быть выполнен от имени какого-то пользователя.
  3. И последнее — доступ к приложению должен быть у всех! По той же причине что и во втором пункте.

Насчет того что доступ для всех — не парьтесь. Доступ будет иметь только тот, кто знает ссылку на это приложение. Так что кроме вас и вашего процесса на Геткурсе никто не сможет сделать запрос.

Все, нажимаем "Начать развертывание" !

Если мы все это проделываем в первый раз, то потребуется предоставить доступ для скрипта. Для того, чтобы он мог выполнять действия (добавлять данные в таблицу) от вашего имени, т.к. мы указали что скрипт будет работать от нашего имени.

Нажимаем кнопку "Предоставить доступ". Затем выбираем наш аккаунт (этот скрин я пропущу).

А вот потом появляется такое предупреждение. Здесь гугл сообщает что приложение, которое мы развернули, запрашивает у нас же доступ, чтобы выполнять действия от нашего имени. Звучит странно, но это просто стандартная процедура. Типа, доверяет ли тот кто будет пользоваться приложением, тому, кто создал это приложение.

Ну, мы сами себе доверяем. Поэтому нажимаем "Показать доп опции" (перевел, типа 😏). И ниже нажимаем перейти к вашему проекту.

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

Заморачиваться на этот счет опять же не нужно, т.к. мы сами себе предоставляем эти разрешения — своему скрипту, а не чужому. А вот когда у вас чужое приложение запрашивает доступ, то стоит задуматься 😉

В общем, все ок — жмем Allow (Разрешить)

А потом происходит такая хрень ))

Здесь можно просто закрыть это окно. Это глюк. Ага, у Гугла они тоже случаются. Доступ приложению в этот момент уже предоставлен, с этим все ок.

Если у вас ошибка с циклическим перенаправлением не появилась, значит вам повезло больше )

После этого возвращаемся к окошку с развертываниями, там где была кнопка "Предоставить доступ".

Если у вас что-то пошло не так из-за глюка и приложение не развернулось (такое может быть), тогда просто повторите процедуру развертывания заново. При этом подтверждать доступ к приложению уже не придется.

Вот что мы видим, когда все получилось

Версия 1 — первое развертывание. Внизу ссылка на приложение и кнопка чтобы скопировать ссылку.

Так выглядит ссылка:

https://script.google.com/macros/s/AKfycb...KPA0-mpIvyQ/exec

Сохраните где-то эту ссылку, она нам еще пригодится.

На самом деле, можно ее протестировать прямо сейчас. Можно ее вставить в адресную строку браузера и попробовать открыть.

Важно! Вы можете быть авторизованы в нескольких Гугл-аккаунтах, один их которых является основным. Если вы создали скрипт в основном аккаунте, то вы без проблем сможете открыть адрес скрипта. Если же скрипт создан не в основном вашем аккаунте, тогда при открытии адреса скрипта вы получите такое сообщение:

В этом случае можно просто открыть новое окно браузера в режиме инкогнито и все заработает. Я создавал скрипт в дополнительном аккаунте, поэтому открываю в режиме инкогнито.

И так, выполняю запрос, ввожу а адресную строку браузера ссылку на скрипт и получаю ошибку от скрипта:

Эту ошибку вернул уже сам скрипт, он обработал запрос и сообщил, что обязательный параметр SSID не указан. Все ок, все работает )

Тестируем

Создам теперь пустую тестовую таблицу и скопирую ее идентификатор

Теперь у нас есть значение для параметра SSID !

Напомню, что у нас есть еще параметр SHEET, в котором можно указать порядковый номер листа в таблице, начиная с нуля (0, 1, 2...) или можно указать имя листа (актуально, если в документе много листов и есть вероятность что их порядок может поменяться). Если этот параметр не указывать, тогда будет выбран первый лист в документе. Я не буду его указывать, т.к. у меня всего один лист.

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

Формируем нужный URL для запроса:

https://script.google.com/macros/s/AKfycb...KPA0-mpIvyQ/exec?SSID=1n8q5FvhPFt4aUTysF5otaF33UevlBu-ox8BCyIxwKXk&DATA=111,aaa,Какой-то текст,"Текст, в котором есть запятая, заключаем в кавычки",2023-04-20,В этом поле была дата,А номер телефона стоит указать с апострофом вначале,'%2B79991234567,А плюс меняем на %2B

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

Ну конечно! Для того, чтобы скрипт смог записать данные в таблицу, ему нужно предоставить права редактора для этой таблицы, в том случае, если таблица создана другим пользователем. Как раз мой случай - таблица создана под основным пользователем, а скрипт работает от имени другого. Не забывайте об этом! ))

Даем права пользователю, под которым запускается скрипт

Выполняем запрос снова. И вуаля:

Скрипт сообщил, что все прошло успешно. Смотрим в таблицу:

Все ок, строчка появилась, все данные заполнены корректно 😎

Заключение

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

Не буду описывать как вызывать данный скрипт из Геткурса. Потому что это и так все знают )) Или все же нужна такая инструкция?

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

У меня есть и другое решение, но оно работает не на Гугл-скриптах, а на базе Гугл-API. И там уже поддерживается очередь, которая позволяет отправлять хоть 1000 запросов одновременно. Пакетная загрузка данных, позволяющая записывать за один раз тысячи строк данных. Ага, когда начинает копиться очередь запросов, тогда и используется пакетная загрузка, строки начинают пачками улетать в таблицу, а не по одной. Так же присутствует поддержка обновления строк в таблице по ключевому полю, удобная функция, если меняется статус заказа, например.

Хотите больше интересных решений — велкам в мой Телеграм-канал )

@Pro_GetCourse (https://t.me/pro_getcourse)

@pro_getcourse
April 2, 2023, 13:58
0 views
4 reactions