Выгрузка данных из Google Sheets в amoCRM


Автоматизируем выгрузку данных из Google Sheets в amoCRM.

Задача

Передать редактируемую строку из таблицы Google Sheets в amoCRM по условию изменения этой строки.

В данном примере показывается процесс настройки передачи данных из сервиса Google Sheets в amoCRM. Сначала нужно передать данные в MoreKit, после чего передать их в amoCRM из MoreKit.

Сценарий

Рассмотрим следующий сценарий:
Есть таблица Google Sheets, в которой содержатся данные по пользователям. Эти данные могут изменяться в таблице динамически. При изменении каких-либо данных по пользователю необходимо получить их в MoreKit для реализации дальнейшей логики передачи в amoCRM.

Создание процесса

Создание процесса в MoreKit

Нужно создать процесс в MoreKit с типом «Другое». Данный процесс будет принимать в себя данные из Google Sheets. Ссылку, генерируемую первоначальным блоком необходимо передать в скрипт.

Новому процессу даем произвольное название. Ссылка, которая сгенерирована первоначальным блоком — это и есть наш адрес, на который будут отправляться данные из Google Sheets.

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

Настройка скрипта

После создания процесса в MoreKit переходим к таблице. Нужно создать скрипт и прикрепить его к таблице, в которой он будет работать.

Настройка скрипта-обработчика таблицы происходит во вкладке Расширения —> appScript:

После перехода в appScript откроется страница с пустой функцией. Нужно все стереть и вставить скрипт из инструкции. Данный скрипт отправляет в MoreKit ту строку, которая будет отредактирована в таблице.

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

Код скрипта

function sendColumnData(row) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];
  if (sheet) {
    
var data = sheet.getRange("A"+row+":K"+row).getValues();
var formattedData = [];
for (var rowIndex = 0; rowIndex < data.length; rowIndex++) {
var row = data[rowIndex];
var rowData = {};
for (var columnIndex = 0; columnIndex < row.length; columnIndex++) {
var cellReference = String.fromCharCode(65 + columnIndex) + (rowIndex + 1);
rowData[cellReference] = row[columnIndex];
}
formattedData.push(rowData);
}
var jsonData = {
rowData
};
var options = {
"method": "post",
"contentType": "application/json",
"payload": JSON.stringify(formattedData)
};
var response = UrlFetchApp.fetch("Ссылка из первоначального блока", options);
Logger.log(response.getContentText());
}
}
function onEdit(e) {
if (e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
if (range.getRow() > 1) {
sendColumnData(range.getRow());
}
}
}

Результат вставки скрипта

Немного про Google AppScript

После того, как скрипт установлен — необходимо осуществить его развертывание, чтобы он начал работать с таблицей.

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

Справа вверху будет кнопка развертывания. Нажимаем “Начать развертывание” → “Новое развёртывание”.

Тип развертывания необходимо выбрать «Веб приложение». Задаем любое описание, выдача доступа — «Только у меня», чтобы ограничить возможность редактирования скрипта другим пользователям, имеющим доступ к таблице.

Нажимаем «Начать развертывание»

После того, как скрипт развернут осталось настроить триггер, в результате срабатывания которого будет отправляться скрипт

Настройка триггера

На странице appScript слева присутствует иконка часиков под названием «Триггеры». Заходим в триггеры и добавляем новый триггер.

Параметры триггера

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

  • Выберите функцию — выбираем onEdit, так как именно данная функция запускает функцию запуска отправления данных, когда редактируется строка.
  • Выполняемое развертывание — выбираем «основное развертывание».
  • Выберите источник мероприятия — выбираем «Из таблицы», так как работаем с таблицей.

Проверка событий в MoreKit

После того, как все сделано можно пробовать редактировать строку.

Журнал выполнения

При редактировании строки произойдет ее отправка в MoreKit. Чтобы убедиться в успешной отправке можно перейти во вкладку «Количество выполнений», она находится прямо под иконкой часов «Триггеры». В ней можно посмотреть статистику по результатам выполнения скрипта.

Получаем данные

После отправки данных можно проверить вкладку «События» в MoreKit и посмотреть на данные, которые получили из MoreKit.
Пример полученных данных:

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

Обновление данных в amoCRM

Обновляем данные

Выстроим небольшую логику, при которой у нас будут изменяться данные по контакту в amoCRM, после изменений контакта в таблице.

Проверяем контакт

Как видим, изменения в сделке отобразились, данные из таблицы google sheets были успешно записаны в контакт amoCRM.

Дополнительный скрипт

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

Массовая выгрузка с кнопкой

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

После развертывания данного скрипта на таблицу — появится кнопка Выгрузка в меню кнопок google sheets. При нажатии на «Отправить» — произойдет выгрузка по адресу, указанном в скрипте.

Ключи во входящих данных в morekit будут соответствовать названиям столбцов таблицы. Установка триггера для данного скрипта не требуется.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Выгрузка')
    .addItem('Отправить', 'sendData')
    .addItem('Удалить', 'deleteYellowRows') 
    .addToUi();
}
function sendData() {
  sendUncoloredRows();
}

function deleteYellowRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var coloredRanges = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getBackgrounds();

  var rowsToDelete = [];

  for (var i = 0; i < lastRow - 1; i++) {
    if (coloredRanges[i].join() === Array(sheet.getLastColumn()).fill('#ffff00').join()) {
      rowsToDelete.push(i + 2);
    }
  }
  if (rowsToDelete.length > 0) {
    rowsToDelete.reverse();
    rowsToDelete.forEach(function(rowIndex) {
      sheet.deleteRow(rowIndex);
    });
  }
}
function sendUncoloredRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
  var data = dataRange.getValues();
  var coloredRanges = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getBackgrounds();
  var rowsToSend = [];
  var startTime = new Date().getTime();
  var elapsedTime = 0;
  
  // !!!!!!!!!! НАСТРОЙКИ !!!!!!!!!!!
  //Принцип работы следующий: скрипт ищет строки, незакрашенные в желтый, собирает их пачкой, затем отправляет. Важно подобрать правильные тайминги, чтобы не потерять данные. Ограничение гугла на выполнение 360 сек. За 1 секунду проходит примерно 2,5 строки. Нужно подобрать такие значения, при которых успеет собрать строки в пачку и отправить. Отправка будет происходит по следующим криетриям:
  //Когда наберется определнное кол-во строк (переменная batchSize) или когда пройдет определенное кол-во секунд (переменная timeout). 
  //URL - здесь вставьте ссылку на процесс, куда нужно выгрузить данные
  var url = "https://ep.morekit.io/XXXXXXXXXXXXXXXXXXXXXXXXX";
  var batchSize = 50; // Количество строк для отправки за один раз, влияет на время отправки, в зависимости от того, сколько незакрашенных строк соберет в пачку. Отправка 50 строк примерно 2-3 сек. Если объем данных небольшой, можно вводить больше, если большой лучше ввести меньше. За один полноценный прогон успевает собрать 800-900 строк, в зависимости от их объема.
  //время, после которого произойдет отправка. Скрипт ломается на 360 сек, соответственно можно выставить 350-355 сек, чтобы успел отправить до поломки
  var timeout = 355000; // 355 секунд 
  // !!!!!!!!!! НАСТРОЙКИ !!!!!!!!!!!
  
  for (var i = 0; i < lastRow - 1; i++) {
    if (
      coloredRanges[i].join() !== Array(sheet.getLastColumn()).fill('#ffff00').join() &&
      data[i].some(function(cellValue) {
        return cellValue !== '';
      })
    ) {
      var jsonObject = {};
      for (var j = 0; j < data[i].length; j++) {
        var header = sheet.getRange(1, j + 1).getValue();
        jsonObject[header] = data[i][j];
      }

      sheet.getRange(i + 2, 1, 1, sheet.getLastColumn()).setBackground("#ffff00");

      rowsToSend.push(jsonObject);

      if (rowsToSend.length >= batchSize || elapsedTime >= timeout) {

        sendBatch(rowsToSend, url);
        rowsToSend = []; 
        startTime = new Date().getTime();
      }
    }

    elapsedTime = new Date().getTime() - startTime;
  }

  if (rowsToSend.length > 0) {
    sendBatch(rowsToSend, url);
  }
}
function sendBatch(batchData, url) {
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(batchData)
  };
  var response = UrlFetchApp.fetch(url, options);

}

Немного про Google AppScript

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