Выгружаем таблицу google sheets в Bitrix24

Задача

Выгрузить облачную google таблицу в Bitrix24

В данном примере показывается процесс настройки передачи данных из googleSheets в Bitrix24 с помощью встроенного функционала Apps script

Сценарий

Сценарий следующий:

Есть таблица с данными. В качестве первой строки — заголовки таблицы. Выгрузить данные этой таблицы в MoreKit, обработать данные внутри логической схемы, передать в Bitrix24.

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

Пример таблицы, данные из которой будем передавать

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

Подключение Bitrix24 в Morekit

Перед добавлением сервиса bitrix24 в morekit, необходимо установить приложение MoreKit в маркетплейсе. Для установки приложения требуется подписка на маркетплейс Bitrix24

После установки приложение MoreKit в маркетплейс bitrix24, можно приступать к добавлению сервиса в Morekit. Для этого в проекте нажмите Добавить сервис, найдите в списке Bitrix24. При подключении сервиса будет запрошена ссылка на ваш битрикс24

Введите ссылку в формате https://мой-домен.bitrix24.ru. После этого произойдет редирект на страницу с вашим битриксом, подтвердите авторизацию

Если все правильно — сервис появится в списке сервисов и у него будет статус Готово

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

После подключения Bitrix24 в MoreKit создадим процесс. При создании процесса нужно выбрать тип Другое

На данный процесс будем отсылать данные из таблицы, которые затем будут передаваться в Битрикс24

На данной стадии достаточно только создать процесс и скопировать ссылку из первоначального блока, чтобы указать ее в скрипте Appscript

Настройка отправки в таблице

После создания процесса — настроим отправку данных из таблицы

В таблице переходим в Расширения —> Apps script

После перехода в Apps Script вы увидите пустую функцию, полностью сотрите ее

Далее, скопируйте скрипт и вставьте его в Appscript

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

function sendData() {
  sendUncoloredRows();
}

function deleteYellowRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //Номер листа
  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);
    });
    Logger.log("Удалено строк: " + rowsToDelete.length);
  } else {
    Logger.log("Нет строк для удаления");
  }
}

function sendUncoloredRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //Номер листа
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
  var data = dataRange.getValues();
  var coloredRanges = dataRange.getBackgrounds();
  var rowsToSend = [];
  var uniqueRowsSet = new Set();
  var startTime = new Date().getTime();
  var elapsedTime = 0;
  var totalSent = 0;
  var totalSkipped = 0;

  // !!!!!!!!!! НАСТРОЙКИ !!!!!!!!!!!
  var url = "https://ep.morekit.io/xxxxxxxxxxxxxxxxxxxxxxxxxx"; 
  var batchSize = 50; 
  var timeout = 355000; // ~5 минут
  var maxRetries = 3;
  // !!!!!!!!!! НАСТРОЙКИ !!!!!!!!!!!

  Logger.log("Начало обработки. Всего строк: " + (lastRow - 1));
  
  for (var i = 0; i < lastRow - 1; i++) {
    // Проверка на желтый фон и непустые строки
    if (coloredRanges[i].join() !== Array(sheet.getLastColumn()).fill('#ffff00').join() && 
        data[i].some(cellValue => cellValue !== '' && cellValue !== null && String(cellValue).trim() !== '')) {
      
      var jsonObject = {};
      for (var j = 0; j < data[i].length; j++) {
        var header = sheet.getRange(1, j + 1).getValue();
        jsonObject[header] = data[i][j];
      }

      // Нормализация JSON для проверки уникальности
      var normalizedJson = JSON.stringify(jsonObject)
        .replace(/\s+/g, '')
        .toLowerCase();
      
      if (!uniqueRowsSet.has(normalizedJson)) {
        uniqueRowsSet.add(normalizedJson);
        rowsToSend.push(jsonObject);
        
        // Закрашиваем сразу, но будем проверять успешность отправки
        paintRowYellow(sheet, i + 2, sheet.getLastColumn());
        
        Logger.log("Добавлена строка " + (i + 2) + " для отправки");
      } else {
        totalSkipped++;
        Logger.log("Пропущен дубликат строки " + (i + 2));
      }
    }

    // Проверка на отправку пакета
    elapsedTime = new Date().getTime() - startTime;
    if (rowsToSend.length >= batchSize || elapsedTime >= timeout) {
      Logger.log("Отправка пакета из " + rowsToSend.length + " строк...");
      var success = sendWithRetry(rowsToSend, url, maxRetries);
      
      if (success) {
        totalSent += rowsToSend.length;
        Logger.log("Пакет успешно отправлен");
      } else {
        Logger.log("Ошибка при отправке пакета");
      }
      
      rowsToSend = [];
      startTime = new Date().getTime();
    }
  }

  // Отправка оставшихся строк
  if (rowsToSend.length > 0) {
    Logger.log("Отправка последнего пакета из " + rowsToSend.length + " строк...");
    var success = sendWithRetry(rowsToSend, url, maxRetries);
    
    if (success) {
      totalSent += rowsToSend.length;
      Logger.log("Последний пакет успешно отправлен");
    } else {
      Logger.log("Ошибка при отправке последнего пакета");
    }
  }

  Logger.log("Обработка завершена. Итого: отправлено " + totalSent + ", пропущено " + totalSkipped);
  SpreadsheetApp.getUi().alert("Готово!", "Обработка завершена.\nОтправлено: " + totalSent + "\nПропущено: " + totalSkipped, SpreadsheetApp.getUi().ButtonSet.OK);
}

function sendWithRetry(batchData, url, maxRetries) {
  var attempts = 0;
  var success = false;
  
  while (attempts < maxRetries && !success) {
    attempts++;
    Logger.log("Попытка " + attempts + " из " + maxRetries);
    
    success = sendBatch(batchData, url);
    
    if (!success && attempts < maxRetries) {
      Utilities.sleep(1000 * attempts);
    }
  }
  
  return success;
}

function sendBatch(batchData, url) {
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(batchData),
    'muteHttpExceptions': true,
    'followRedirects': true,
    'validateHttpsCertificates': true
  };

  try {
    Logger.log("Отправка пакета на " + url);
    var response = UrlFetchApp.fetch(url, options);
    var responseCode = response.getResponseCode();
    var responseText = response.getContentText().substring(0, 1000); // Длина лога
    
    Logger.log("HTTP код: " + responseCode);
    Logger.log("Ответ сервера: " + responseText);
    
    return responseCode >= 200 && responseCode < 300;
  } catch (e) {
    Logger.log("Ошибка при отправке: " + e.message);
    return false;
  }
}

function paintRowYellow(sheet, rowIndex, columnCount) {
  try {
    var range = sheet.getRange(rowIndex, 1, 1, columnCount);
    range.setBackground("#ffff00");
    Logger.log("Строка " + rowIndex + " закрашена в желтый");
    return true;
  } catch (e) {
    Logger.log("Ошибка закрашивания строки " + rowIndex + ": " + e.message);
    return false;
  }
}

Если у вас в таблице несколько листов, укажите порядковый номер листа (отсчет начинается с нуля). Например, если у вас два листа, выгрузить нужно со второго — вместо [0] напишите [1]

Далее — найдите часть с настройками в скрипте. В нее вставьте скопированную ссылку из MoreKit для вашего процесса. Ссылка должна быть в кавычках. После ковычек не потяряйте ;

После вставки ссылки нажмите сверху на дискету, либо используется комбинацию клавиш CTRL + S, чтобы сохранить скрипт

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

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

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

Тип развертывания: Веб — приложение

У кого есть доступ: Все

Далее нажимаем Начать развертывание. Если развертывание попросит доступ

После подтверждения выдачи прав доступа к таблице — увидим окно с выполненным развертыванием

Далее — переходим назад в таблицу и обновляем страницу. Если все сделано верно — над таблицей увидите кнопку Выгрузка

Далее — попробуем отправить данные. Нажимаем на кнопку Выгрузка и Отправить. После нажатия — строки начнут закрашиваться в желтый цвет

Так как в таблице 4 строки с данными — в MoreKit должно придти 4 события.

Проверим это: вернемся в схему, перейдем во вкладку События

Видим, что события успешно дошли. Если развернуть событие и перейти во входящие данные — увидим данные, которые были в строке

Теперь останется выстроить логическую схему передачи данных в Битрикс24

В данном примере схема будет выполнять следующие действия:

• Будет искаться контакт по номеру телефона и email в битрикс24, чтобы не создавать дубли по контактам
• Если контакта нет — создаваться новый контакт и сделка к нему
• Если контакт есть — создаваться сделка
• Перед постановкой задачи будет проверяться значение Нужно связаться из таблицы. Если там будет написано Да, будем ставить задачу в сделку

Схема будет выглядеть следующим образом

Описание блоков схемы

#1 Найти контакты — поиск контакта по номеру телефона, который приходит из таблицы

#2 Найти контакты — Если контакт не нашелся по номеру телефона, ищем его по email адресу

#3 Создать контакты — если контакт не был найден ни по телефону, ни по email, создаем новый контакт

#4 Создать сделку — создаем сделку. Привязываем к сделке найденный или созданный контакт, выбираем стадию и воронку сделки, заполняем поле Уникальный код

#5 Проверка — Проверяем значение столбцы Нужно связаться. Если в таблице было Да — схема пойдет направо, по зеленой галочке. Если нет — вниз, т.е. процесс завершится

#7 Дата и время — создаем дату со сдвигом на день вперед. Дата будет нужна для подстановки в срок выполнения задачи

#6 Создать задачу — создаем задачу. Заполняем срок, название, описание

Тестирование схемы

Проверить работоспособность можно следующим образом:

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

Повторим событие из MoreKit, жмите на значок перезапуска события

После перезапуска — значок исчезнет. Это значит — что данные переотправляются на процесс и применяются к новой схеме. Можно идти смотреть в Битрикс24 результат создания. После повтора четырех наших событий — видим 4 сделки в системе

Перейдем в сделку, где ставилась задача

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

Подведем итоги

В данном кейсе рассмотрели, как можно выгрузить данные из Google Sheets в MoreKit с помощью Apps Script.

В MoreKit получили данные из таблицы, выстроили логическую схему, убедились в успешной передаче данных внутри битрикс24 по итогам выгрузки