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

Автоматизируем выгрузку данных из amoCRM в Google Sheets с помощью MoreKIT.

Содержание

Задача

Передаем данные из CRM в таблицу по событию в CRM.

В данном примере: выгрузка в таблицу ID сделки, фамилии и имени основного контакта в сделке и ответственного при создании новой сделки в выбранной воронке продаж и этапе.

результат выгрузки

Подготовка таблицы

Создаем таблицу и добавляем скрипт по инструкции.

Код скрипта:

function doPost(request) {
  var data = JSON.parse(request.postData.contents),
    sheet = SpreadsheetApp.getActive().getActiveSheet(),
    table_headers = get_table_headers(sheet),
    search_by = request.parameter.search_by;
  if (table_headers.length == 0) {
    table_headers = Object.keys(data);
    fill_headers(sheet, table_headers);
  }
  if (!search_by) {
    return ContentService.createTextOutput(JSON.stringify({
      'error': 'search_by not found'
    }));
  }
  if (is_object(data)) {
    var fill_func = fill_object;
    if (is_array(data)) fill_func = fill_array;
    fill_func(data, table_headers, sheet, search_by);
  } else {
    return ContentService.createTextOutput(JSON.stringify({
      'error': 'Unknown data format'
    }));
  }
  return ContentService.createTextOutput(JSON.stringify({
    'result': 'ok'
  }));
}

function is_array(data) {
  return typeof(data) === 'object' && data.length;
}

function is_object(data) {
  return typeof(data) === 'object';
}

function fill_array(data, table_headers, sheet, search_by) {
  data.forEach((v) => {
    fill_object(v, table_headers, sheet, search_by)
  });
}

function fill_object(data, table_headers, sheet, search_by) {
  var column_id = table_headers.indexOf(search_by);
  if (column_id < 0) return;
  var row = get_row_id_by_lead_id(data[search_by], column_id + 1, sheet),
    data = object_to_list(data, table_headers);
  sheet.getRange(row, 1, 1, data.length).setValues([data]);
}

function fill_headers(sheet, headers) {
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}

function get_table_headers(sheet) {
  var cols = sheet.getLastColumn();
  if (cols > 0)
    return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  else
    return [];
}

function object_to_list(data, table_headers){
    var result = [];
    for (var i in table_headers){
        var is_null = data[table_headers[i]] === undefined || data[table_headers[i]] === null;
        result.push(is_null ? '' : data[table_headers[i]]);
    }
    return result;
}

function get_row_id_by_lead_id(lead_id, column, sheet) {
  var last_row = sheet.getLastRow();
  if (last_row > 1) {
    var data = sheet.getRange(2, column, last_row, 1).getValues();
    for (i in data) {
      if (data[i] && data[i][0] == lead_id) return parseInt(i) + 2;
    }
  }
  return parseInt(last_row) + 1;
}

Создаем процесс

При создании процесса есть выбор его типа.

AmoCRM DP — позволяет собирать данные из выбранной воронки и этапа продаж. Ссылку в этом случае добавляем в Digital Pipeline.

AmoCRM вебхук — позволяет собирать данные из всей CRM. Ссылку в этом случае добавляем в Web Hooks в амоМаркет (справа вверху) и выбираем событие срабатывания.

Блок-схема процесса

Выстраиваем блок-схему, как на рисунке выше. Нужные блоки вытаскиваем из левого выпадающего меню.

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

Блоки #1 и #2 из раздела подключенной CRM. Если в проекте более одной CRM? то вы будете видеть их все, поэтому выбирайте внимательно, из нужного раздела.

Блоки #3 и #4 из общих функций.

Настройка процесса

Стартовый блок

Cсылка создается автоматически. Её необходимо добавить в созданный хук на стороне CRM. При необходимости добавляем дополнительные проверки по полям, если нужны не все сделки.

Блок #1

Выбрать - "самый старый" — это стандартное поле, для определения выбора сделок при наличии дублей. В данном процессе оно не важно, так как работаем с одиночными сделками.

По входящему событию (создание новой сделки) ищем ID сделки.

Так же можно собирать любые поля из полученных данных.

Блок #2

По найденной сделке ищем основной контакт.

Блок #3

Используя данные найденной сделки получаем данные об ответственном за неё пользователе CRM.

Блок #4

Складываем данные в WebHook, с указанием необходимых собранных полей (ID сделки, название основного контакта, имя ответственного).

WebHook передает только ID клиента и ответственного, поэтому здесь мы уточняем их имя и фамилию (выбираем нужные поля).

Далее MoreKIT самостоятельно собирает из CRM данные по факту создания сделки и заполняет нужные поля в Google Sheets, согласно скрипту.

URL — адрес скрипта в Google.

GET-параметр — search_by параметр скрипта, который не даст создать дубли. Если сделка уже была добавлена то скрипт найдёт ID сделки в таблице и обновит данные по клиенту и ответственному. А при отсутствии, запишет значения в новую сроку.

В POST-параметрах указываем значения тех полей, которые нужно записать в таблицу. Скрипт сам создаст названия столбцов = название поля.

Результат

В результате получаем таблицу с автоматическим заполнением данных из CRM по определенным событиям.

К примеру:

список успешно завершенных сделок с ответственными и бюджетом сделок;
список закрытых неуспешно сделок с причинами отказов и ответственными;
список сделок с подписанными договорами;
успешные сделки с источниками ЛИДа, проданным продуктом, тегами и пр.

Дополнительная информация

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

Также потребуется добавить в блок WebHook еще один POST-параметр sheetName. Значение этого параметра должно соответствовать названию нужного листа, в который вы хотите выгрузить данные.

function doPost(request) {
var data = JSON.parse(request.postData.contents),
sheetName = data.sheetName,
sheet = SpreadsheetApp.getActive().getSheetByName(sheetName),
table_headers = get_table_headers(sheet),
..............
}

Скрипт с функцией обогащения данных и выбором листа

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

function doPost(request) {
  var data = JSON.parse(request.postData.contents);
  Logger.log("Данные: " + JSON.stringify(data));

  var sheetName = data.sheetName,
      sheet = SpreadsheetApp.getActive().getSheetByName(sheetName),
      table_headers = get_table_headers(sheet),
      search_by = data.search_by || request.parameter.search_by;

  var logDetails = {
    tableHeaders: table_headers,
    searchBy: search_by,
    sheetName: sheetName
  };

  if (table_headers.length === 0) {
    table_headers = Object.keys(data);
    fill_headers(sheet, table_headers);
    logDetails.tableHeadersAfterFill = table_headers;
  }

  var dataArray = Array.isArray(data) ? data : [data];

  dataArray.forEach(entry => {
    fill_object(entry, table_headers, sheet, search_by);
  });

  logDetails.result = 'ok';
  Logger.log("Итог: " + JSON.stringify(logDetails));
  return ContentService.createTextOutput(JSON.stringify(logDetails));
}

function is_array(data) {
  return typeof data === 'object' && data.length;
}

function is_object(data) {
  return typeof data === 'object';
}

function fill_headers(sheet, headers) {
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
}

function get_table_headers(sheet) {
  var cols = sheet.getLastColumn();
  if (cols > 0)
    return sheet.getRange(1, 1, 1, cols).getValues()[0];
  else
    return [];
}

function object_to_list(data, table_headers) {
  var result = [];
  for (var i = 0; i < table_headers.length; i++) {
    var value = data[table_headers[i]];
    result.push(value !== undefined ? value : '');
  }
  return result;
}

function fill_object(data, table_headers, sheet, search_by) {
  var row;

  if (search_by) {
    var column_id = table_headers.indexOf(search_by);
    if (column_id < 0) {
      Logger.log("search_by не найден: " + search_by);
      return;
    }
    row = get_row_id_by_lead_id(data[search_by], column_id + 1, sheet);
    Logger.log(`Найдена строка по ${search_by} = ${data[search_by]} → строка ${row}`);
  } else {
    row = find_first_completely_empty_row(sheet, table_headers.length);
    Logger.log(`Запись без search_by: строка ${row}`);
  }

  var rowData = object_to_list(data, table_headers);
  Logger.log(`Обогащение строки ${row}: ${JSON.stringify(rowData)}`);

  var existingValues = sheet.getRange(row, 1, 1, table_headers.length).getValues()[0];

  for (var i = 0; i < rowData.length; i++) {
    var valueToInsert = rowData[i];
    var existingValue = existingValues[i];

    if ((existingValue === '' || existingValue === null) && valueToInsert !== '' && valueToInsert !== undefined) {
      var cell = sheet.getRange(row, i + 1);
      if (cell.getFormula()) {
        var formula = cell.getFormula();
        cell.setValue('').setFormula(formula);
      } else {
        cell.setValue(valueToInsert);
      }
    } else {
      Logger.log(`Пропущена запись в столбец ${table_headers[i]}: уже есть значение "${existingValue}"`);
    }
  }
}

function get_row_id_by_lead_id(lead_id, column, sheet) {
  var last_row = sheet.getLastRow();
  if (last_row > 1) {
    var data = sheet.getRange(2, column, last_row - 1, 1).getValues();
    for (var i = 0; i < data.length; i++) {
      if (data[i] && data[i][0] == lead_id) {
        return i + 2;
      }
    }
  }
  return last_row + 1;
}

function find_first_completely_empty_row(sheet, numCols) {
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return 2; 
    

  var data = sheet.getRange(2, 1, lastRow - 1, numCols).getValues();

  for (var i = 0; i < data.length; i++) {
    var isEmpty = data[i].every(cell => cell === "" || cell === null);
    if (isEmpty) {
      return i + 2;
    }
  }

  return lastRow + 1;
}