Интеграция чат-бота с Google-таблицей при помощи HTTP-запроса

Современные конструкторы чат-ботов обещают, что для создания и настройки сценария для бота писать код не потребуется. Мы в Domino CRM тоже так говорим. И мы с коллегами из других команд в большинстве случаев правы. Но всегда есть оговорки.  Самый частый случай, когда кодить придется: если нужно встроить HTTP-запрос как элемент диалога чат-бота, чтобы получить данные из внешних сервисов.

Для большинства задач это не обязательно. В конструкторах и так есть скачиваемые шаблоны для каталогов.

Однако в случаях, когда требуется фильтрация по действительно большому массиву данных, например в eComm или гостиничном бизнесе, интеграция может оказаться полезной. Иначе придется переносить бесконечные данные в шаблоны для каталогов, с версткой. Ну а если данные часто меняются, то интеграция с внешним сервисом напрямую — и вовсе must.

Покажем, как это сделать в нашем сервисе на примере интеграции Google Таблицы с чат-ботом в Domino CRM (в данном случае, чат-бот будет работать в Telegram).

Чтобы интегрировать данные из Google-таблицы с Domino CRM, потребуется: 

• Google-таблица с данными, открытая для просмотра; 

• Ключ Google API; 

• API-эндпоинт (для упрощения можно использовать облачные функции — пример ниже); 

• HTTP-блок в сценарии Domino CRM.

Подготовка 

Чтобы показать, как работать с HTTP-блоком, создадим короткую базу и откроем к ней доступ. В нашем примере нам потребуется знать номер строки для конкретной записи. Поэтому в первом столбце мы укажем идентификатор, который фактически является номером строки.

Затем установим доступ с правами для просмотра документа для всех по ссылке. 

Далее необходимо включить Sheets API. Для этого перейдите по ссылке https://developers.google.com/workspace/guides/enable-apis#sheets-api. Затем нажмите на «Enable Sheets API», далее следуйте инструкциям.

Затем необходимо создать ключ API, если у вас его еще нет. Для этого перейдите по ссылке https://console.cloud.google.com/apis/credentials и создайте его (Create Credentials > API key)

После этого появится модальное окно с вашим новым ключем, он нам потребуется в дальнейшем. Ключ будет выглядеть как-то так: 

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/Sheet1!A1:D5

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

Формирование Curl запроса 

Так как мы будем использовать блок для HTTP-запроса, требуется сформировать curl запрос. Для этого нам понадобится: 

• Url нашего API; 

• ID гугл таблицы; 

• Ключ Google API; 

• Понимание критериев для фильтрации данных из таблицы. 

URL для API в нашем примере будет выглядеть примерно так: https://functions.yandexcloud.net/xxx

Аналогично, нам нужно задать идентификатор нашего документа. Здесь достаточно зайти в саму таблицу и посмотреть на адресную строку:

В нашем примере это будет: 1EV0eFjfDsiW8cO8HAs2SNcV5xlltbQHrsuW_ewJxU5E. У вас, конечно, будет другой пример. 

Определимся, по каким полям будем фильтровать данные. Еще раз взглянем на таблицу с названиями товаров, ценами, остатками на складе: 

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

'{API-URL}?doc={DOC-ID}&sheet={SHEET-NAME}&cell={CELLS}&key={G-KEY}&filters={FILTERS}'

Где:

• {API-URL} — URL API эндпоинта (например, https://functions.yandexcloud.net/xxx);

• {DOC-ID} — ID таблицы (например, 1EV0eFjfDsiW8cO8HAs2SNcV5xlltbQHrsuW_ewJxU5E);

• {SHEET-NAME} — название листа (в данном примере это Sheet1);

• {CELLS} — диапазон поиска данных. Так как первая строка содержит названия столбцов, будем начинать с A2 и до столбца E (A2:E);

• {G-KEY} — ключ Google API (например, xxxxXxXXXXXXXX-XXXXXXxXXXXXxXxXxXxXxxxx);

• {FILTERS} — параметр для фильтрации данных. Описание написания фильтра приведено ниже.

Мы хотим фильтровать данные по стоимости, поэтому наш фильтр примет следующий вид: {«2»:{«lte»:«20»}}, если необходимо получить товары стоимостью до 20 включительно.

Где:

• «2» — это номер столбца, по которому выполняется фильтрация. В нашей функции столбцы отсчитываются с 0, так что столбец стоимости имеет идентификатор 2.

• «lte» — условие фильтрации. Поскольку мы хотим найти товары до указанной цены, используем условие «меньше или равно».

• «20» — динамическое значение, которое будет получено от пользователя бота.

Если помимо цены нужно также фильтровать товары по доступности на складе, фильтр можно расширить следующим образом: {«2»:{«lte»:«20»},«3»:{«gte»:«1»}}.

Таким образом, можно добавлять несколько фильтров одновременно.

Вот отредактированный текст:

Наша функция поддерживает фильтрацию по столбцам:

• eq — полное соответствие (можно использовать для текстовых значений)

• lte — меньше или равно (для числовых значений)

• lt — строго меньше (для числовых значений)

• gt — строго больше (для числовых значений)

• gte — больше или равно (для числовых значений)

Определив, какие данные мы хотим фильтровать, мы получим следующий URL для нашего curl-запроса:

https://functions.yandexcloud.net/xxx?doc=1EV0eFjfDsiW8cO8HAs2SNcV5xlltbQHrsuW_ewJxU5E&sheet=Sheet1&cell=A2:E&key=xxxxXxXXXXXXXX-XXXXXXxXXXXXxXxXxXxXxxxx&filters={«2»:{«lte»:«20»},«3»:{«gte»:«1»}}

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

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

Перед началом создания сценария рекомендуется сначала добавить поле в настройках бота для хранения ключа Google API. Это можно сделать на странице редактирования бота:

Теперь приступим к непосредственному созданию сценария:

1. Создаем новый сценарий;

2. Для удобства доступа добавляем триггер /google;

3. Добавляем текстовый блок с кнопкой;

4. Добавляем блок с вопросом.

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

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

Теперь добавим HTTP-блок и вставим в него сформированный ранее запрос.

В примере выше мы хотим использовать переменную бота и ответ от пользователя. Сделать это можно с помощью функции вставки переменной. Финальный вид запроса будет следующий:

curl

https://functions.yandexcloud.net/xxx?doc=1EV0eFjfDsiW8cO8HAs2SNcV5xlltbQHrsuW_ewJxU5E&sheet=Sheet1&cell=A2:E&key=@{Bot:G-key}&filters={«2»:{«lte»:«20»},«3»:{«gte»:«1»}}

Далее, в настройках HTTP-блока, укажем, что данные, полученные из API, будут сохраняться в переменную «Товары» типа «Массив». Перед этим убедимся, что переменная была создана заранее.

Итак, наша облачная функция возвращает ответ, содержащий два объекта: f и r. Объект f представляет собой данные фильтра, где каждый ключ обозначает определенное условие, а r — массив, содержащий найденные товары. Каждый элемент массива r представляет собой массив с данными о товаре.

Пример ответа выглядит так:

{

  «f»: { «2»: { «lte»: «20» }, «3»: { «gte»: «1» } },

  «r»: [

    [«1», «Wheel», «20», «4», «3/1/2016»],

    [«2», «Door», «15», «2», «3/15/2016»]

  ]

}

Далее в настройках HTTP-блока укажем что мы хотим сохранять полученные из API данные в переменную «Товары» типа массив. Предварительно создав ее.

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

Укажем заголовок как [1], так как хотим выводить название товара (2-й столбец), а контент кнопки укажем как идентификатор товара [0] (1-й столбец).

Сохраним сценарий и протестируем результат.

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

Создадим новый HTTP-блок и свяжем его с динамическими кнопками.

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

В новом запросе укажем, что нам нужны данные, начиная со столбца B и до столбца E. Необходимую строку определим из переменной, которая сохраняется при выборе контактом динамической кнопки — в нашем случае это переменная «ВыбранныйТовар».

curl 'https://functions.yandexcloud.net/xxx?doc=1EV0eFjfDsiW8cO8HAs2SNcV5xlltbQHrsuW_ewJxU5E&sheet=Sheet1&cell=B@{Flow:ВыбранныйТовар}:E@{Flow:ВыбранныйТовар}&key=@{Bot:G-key}'

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

Если всё настроить правильно, HTTP-блок вернет ответ следующего вида:

{  «f»: {},  «r»: [    [      «Door»,      «15»,      «2»,      «15/2/2024»    ]  ]}

Следовательно путь до наименования товара будет следующий r[0][0], для цены r[0][1], для остатков r[0][2], для даты доставки r[0][2]. Настройки блока будут выглядеть следующим образом: 

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

Теперь при нажатии контактом на динамическую кнопку пользователь получит подробные данные о товаре из Google-таблицы.

Вот и все!

Код облачной функции для текущего примера

module.exports.handler = async function (event, context) {

    const cell = event.queryStringParameters.cell;

    const key = event.queryStringParameters.key;

    const doc = event.queryStringParameters.doc;

    const sheet = event.queryStringParameters.sheet;

    const filters = event.queryStringParameters.filters ? JSON.parse(event.queryStringParameters.filters) : {};

    const isMultiVal = /:/g.test(cell);

    let url = 'https://sheets.googleapis.com/v4/spreadsheets';

    url += '/' + doc; // document id

    url += '/values';

    url += '/' + sheet; // sheet name

    url += '!' + cell;

    url += '?key=' + key;

    const response = await fetch(url);

    const body     = await response.json();

    if (body && body.values){

        let result = body.values;

        if(isMultiVal){

            let index = result.length — 1;

            while (index >= 0) {

                let item = result[index];

                let hasMatch = true;

                

                for (var f in filters) {

                    for(op in filters[f]) {

                        if (op == «eq» && !(filters[f][op].toString() === item[Number(f)].toString().trim())) {

                            console.log('item:', item[Number(f)], 'filter:', filters[f][op])

                            hasMatch = false;

                            // result.splice(index, 1);

                            continue;

                        }

                        if (op == «gt» && !(Number(item[Number(f)]) > Number(filters[f][op]))) {

                            console.log('item:', item[Number(f)], 'filter:', filters[f][op])

                            hasMatch = false;

                            continue;

                        }

                        if (op == «gte» && !(Number(item[Number(f)]) >= Number(filters[f][op]))) {

                            console.log('item:', item[Number(f)], 'filter:', filters[f][op])

                            hasMatch = false;

                            continue;

                        }

                        if (op == «lt» && !(Number(item[Number(f)]) < Number(filters[f][op]))) {

                            console.log('item:', item[Number(f)], 'filter:', filters[f][op])

                            hasMatch = false;

                            continue;

                        }

                        if (op == «lte» && !(Number(item[Number(f)]) <= Number(filters[f][op]))) {

                            console.log('item:', item[Number(f)], 'filter:', filters[f][op])

                            hasMatch = false;

                            continue;

                        }

                    }

                }

                if (!hasMatch){

                    result.splice(index, 1);

                }

                

                index -= 1;

            }

        }

        else {

            result = body.values[0][0];

        }       

        return {

            body: { f: filters, r: result },

        };

    } else {

         return {

            code: response.status,

            body: { message: «» } 

        };

    }

};