Простой пример бота с хранением данных в Google Excel на Google App Script

Рассмотрим выборку данных, редактирование, добавление и удаление строк из таблицы.

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

Дополнительно обязательно в таблице, в моем примере это таблица pages, первой строкой укажите заголовки. Для моего примера это hash и name

Пример моего заполнения таблицы <b>pages</b>
Пример моего заполнения таблицы pages

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

Результатом будет массив объектов, в которых ключами будут заголовки таблицы

/**
 * Выборка данных из таблицы по условию
 * table   таблица выборки
 * params  объект данных для условия, где ключ == заголовку в таблице
 */
function getRows(table_, params = {}) {
  // получим объект таблицы
  const table = file.getSheetByName(table_);
  // получим все данные из таблицы
  const rows = table.getDataRange().getValues();
  // запомним названия столбцов
  const headers = rows.shift();
  // получим все ключи условий
  const paramsKeys = Object.keys(params);
  // отфильтруем по условию если условия есть
  const data = paramsKeys.length 
    ? rows.filter((line, idx, arr) => {
      // добавим в конец элемента - ключ элемента в массиве строк таблицы
      // далее в объекте он будет под ключем key_row
      arr[idx].push(idx + 1);
      // вернем результат проверки условий
      return (paramsKeys.filter(key => line[headers.findIndex(header => header == key)] == params[key])).length == paramsKeys.length;
    }) 
    : rows;
  // вернем результат массивом объектов
  return setObjects(headers, data);
}

/**
 * Преобразуем в объекты
 */
function setObjects(headers, data) {
  // проверим
  if(data.length) {
    // пройдем по массиву и преобразуем элементы в объекты
    return data.map((item) => {
      // создадим новый объект
      let object = {};
      // переберем заголовки и заполним объект данными
      headers.forEach((head, key) => object[head] = item[key]);
      // добавим номер строки - нужен для редактирования или удаления из таблицы
      object.key_row = item.at(-1);
      // вернем объект
      return object;
    });
  }
  // вернем результат
  return data;
}

Применение метода может быть таким

// найдем записи где uid = 12345678 и status = "active"
const data = getRows("users", {uid: 12345678, status: "active"})

// получим все данные из таблицы
const data = getRows("users")

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

  1. /start - выведет объект из таблицы, где значение hash будет равно "qdekR45d"
  2. /random - выведет рандомный элемент из таблицы
  3. /add - добавится строка в таблице
  4. /update_[\w]{8} - например /update_qdekR45d, при вводе этой команды заголовок смениться на значение "Новый заголовок"
  5. /delete_[\w]{8} - например /delete_qdekR45d, строка где будет совпадать переданный hash будет удалена

Полный код бота

const config = {
  // токен бота
  token: "34069----:-------wdiVQs23jSzxKVW3qILFpY", 
  // id файла в инструкции ранее назывался sheet
  file: "116bvuicI8qR----------erSytE2OIJfWhB0kHLU",
  // название таблицы
  table: "pages",
  // адреc веб-приложения App Script
  webhook: "https://script.google.com/macros/s/AKfycb...9tmQ/exec",
  // Телеграм api
  apiUrl: "https://api.telegram.org/bot",
};

// получим объект таблицы
const file = SpreadsheetApp.openById(config.file);

/**
 * Обрабатываем POST 
 */
function doPost(request) {
  // если в параметрах есть токен
  if(request.parameter.token === config.token) {
    // парсим данные
    let update = JSON.parse(request.postData.contents);
    // запускаем обработку
    try {
      // передаем в роутер
      route(update);
    } 
    // перехватываем ошибку
    catch(e) {
      // логируем ошибку
      logger(e.message);
    }
  }
}

/**
 * Набор команд
 */
const routers = [
  {
    template: /^\/start$/,
    method: 'start'
  },
  {
    template: /^\/random$/,
    method: 'random'
  },
  {
    template: /^\/add$/,
    method: 'addRow'
  },
  {
    template: /^\/update_[\w]{8}$/,
    method: 'update'
  },
  {
    template: /^\/delete_[\w]{8}$/,
    method: 'deleteRow'
  }
];

/**
 * Порверим вхождение команды в текстовое значение из телеграма
 */
function checkRouters(text) {
  // если есть команды
  if (routers.length) {
    // переберем
    for (let router of routers) {
      // если шаблон проходит тест
      if (router.template.test(text)) {
        // ставим флаг проверки в true 
        router.result = true;
        // вернем роутер
        return router;
      }
    }
  }
  // по умолчанию вернем объект с флагом false
  return {
    result: false
  };
}

/**
 * Роутер
 */
function route(data) {
  // если это объект сообщения из телеграмма
  if('message' in data) {
    // если это текстовое сообщение
    if('text' in data.message) {
      // получим результат проверки на команду
      const router = checkRouters(data.message.text);
      // если команда 
      if(router.result) {
        // получим параметры
        let params = data.message.text.split("_");
        // удалим первый элемент
        params.shift();
        // вызовем функцию из параметра метод
        (new Function(["data", "params"], "return " + router.method + "(data, params);"))(data, params);
      }
    }
  } 
}

/**
 * Выборка данные из таблицы по условию
 * table  таблица выборки
 * params   объект данных для условия, где ключ == заголовку в таблице
 */
function getRows(table_, params = {}) {
  // получим объект таблицы
  const table = file.getSheetByName(table_);
  // получим все данные из таблицы
  const rows = table.getDataRange().getValues();
  // запомним названия столбцов
  const headers = rows.shift();
  // получим все ключи условий
  const paramsKeys = Object.keys(params);
  // отфильтруем по условию если условия есть
  const data = paramsKeys.length 
    ? rows.filter((line, idx, arr) => {
      // добавим в конец элемента - ключ элемента в массиве строк таблицы
      // далее в объекте он будет под ключем key_row
      arr[idx].push(idx + 1);
      // вернем результат проверки условий
      return (paramsKeys.filter(key => line[headers.findIndex(header => header == key)] == params[key])).length == paramsKeys.length;
    }) 
    : rows;
  // вернем результат массивом объектов
  return setObjects(headers, data);
}

/**
 * Преобразуем в объекты
 */
function setObjects(headers, data) {
  // проверим
  if(data.length) {
    // пройдем по массиву и преобразуем элементы в объекты
    return data.map((item) => {
      // создадим новый объект
      let object = {};
      // переберем заголовки и заполним объект данными
      headers.forEach((head, key) => object[head] = item[key]);
      // добавим номер строки - нужен для редактирования или удаления из таблицы
      object.key_row = item.at(-1);
      // вернем объект
      return object;
    });
  }
  // вернем результат
  return data;
}

/**
 * Старт бота
 */
function start(data, params) {
  // получим элемент таблицы по hash
  const texts = getRows(config.table, {hash: "qdekR45d"})
  // проверим наличие
  if(texts.length) {
    // направим текстовое сообщение пользователю
    sendMessage(data.message.from.id, texts[0].name);
  }
}

/**
 * Выведем рандомный элемент
 */
function random(data, params) {
  // получим все элементы таблицы
  const texts = getRows(config.table)
  // удалим заголовок
  texts.shift();
  // проверим наличие
  if(texts.length) {
    // определим рандомный элемент
    const rand = Math.floor(Math.random() * texts.length);
    // направим текстовое сообщение пользователю
    sendMessage(data.message.from.id, texts[rand].name);
  }
}

/**
 * Отредактируем
 * data     объект телеграм
 * params   [hash]
 */
function update(data, params) {
  // получим все элементы таблицы
  const texts = getRows(config.table, {hash: params[0]})
  // проверим
  if(texts.length) {
    // получим index столбца name
    const column = Object.keys(texts[0]).findIndex((head) => head == "name");
    // получим объект таблицы
    const table = file.getSheetByName(config.table);
    // заменим значение у значения name
    table.getRange(texts[0].key_row, column + 1).setValue("Новый заголовок");
    // уведомим пользователя
    sendMessage(data.message.from.id, "Отредактировали");
  }
}

/**
 * Удалим строку
 * data     объект телеграм
 * params   [hash]
 */
function deleteRow(data, params) {
  // получим все элементы таблицы
  const texts = getRows(config.table, {hash: params[0]})
  // проверим
  if(texts.length) {
    // получим таблицу
    const table = file.getSheetByName(config.table)
    // удалим строку по index key_row
    table.deleteRow(texts[0].key_row);
    // уведомим пользователя
    sendMessage(data.message.from.id, "Удалили");
  }
}

/**
 * Добавим новую строку
 */
function addRow(data, params) {
  // создадим hash
  const hash = getRandomStr(8);
  // получим таблицу
  const table = file.getSheetByName(config.table)
  // добавим строку
  table.appendRow([hash, "Название для " + hash]);
  // уведомим пользователя
  sendMessage(data.message.from.id, "Добавили");
}

/**
 * Отправим текстовое сообщение
 */
function sendMessage(chat_id, text) {
  // подготовим набор данных
  let payload = {
    method: "sendMessage",
    chat_id: String(chat_id),
    text: text,
    parse_mode: "HTML"
  };
  // вернем результат отправки
  return query(payload);
}

/**
 * Направляем запрос в Телеграм
 */
function query(payload) {
  let data = {
    method: "post",
    payload: payload
  };
  return JSON.parse(UrlFetchApp.fetch(config.apiUrl + config.token + "/", data).getContentText());
}

/**
 * Логгирование
 */
function logger(message, table = "Logs") {
  try {
    // если таблице в файле нет
    if (file.getSheetByName(table) === null) {
      // добавляем таблицу
      file.insertSheet(table);
    }
    // записываем
    file.getSheetByName(table).appendRow([message]);
  } catch (e) {}
}

/**
 * Установим Вебхук
 */
function setWebHook() {
  let response = UrlFetchApp.fetch(config.apiUrl + config.token + "/setWebHook?url=" + config.webhook + "?token=" + config.token);
  console.log(response.getContentText());
}

/**
 * Генерируем строку
 */
function getRandomStr(length = 16) {
  return Array(length)
    .fill("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz")
    .map(function (x) {
      return x[Math.floor(Math.random() * x.length)]
    })
    .join('')
    .toLowerCase();
}

/**
 * Проверка на существование
 */
function isSet(variable) {
  return typeof variable !== "undefined";
}

/**
 * Проверка на пустое значение
 */
function isEmpty(variable) {
  return variable === "";
}

/**
 * Проверка на NULL
 */
function isNull(variable) {
  return variable === null;
}
20 комментариев
Авторизуйтесь через Telegram, чтобы оставить комментарий.
Откройте по ссылке или QR бот @iMakeBot, нажмите кнопку Старт/Start.
Следуйте инструкциям бота.

  • Andrew° [1 год назад]

    Лучший! Просто лучший!

  • gocha _ [1 год назад]

    Здравствуйте ! Спасибо, что делитесь своими наработками.

    Подскажите, как работает эта строка:

    (new Function(["data", "params"], "return " + router.method + "(data, params);"))(data, params);

    интересует именно окончание (data, params);

    в первых скобках мы создаем / запускаем функцию, а для чего далее указывается  (data, params) ?

    + в тексте дублируется  * Порверим вхождение команды в текстовое значение из телеграма

  • iMakeBots [1 год назад → gocha _]

    В data - лежат данные, которые пришли от телеграм

    В params - массив наших переданных данных, при вызове метода update и delete там лежит hash строки - это в моем примере в своих примерах вы можете передавать свои данные

  • gocha _ [1 год назад → iMakeBots]

    что лежит в переменных я понял, не понял лишь для чего данные дополнительно  указываются вне new function

    (new Function(["data", "params"], "return " + router.method + "(data, params);")) и далее вот эта часть (data, params);

    в любом случае благодарю за ответ!

  • iMakeBots [1 год назад → gocha _]

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

  • gocha _ [1 год назад → iMakeBots]

    вот вроде ничего не изменилось, а дошло ))) еще раз спасибо !) в самой функции просто указаны принимаемые аргументы, а далее уже сами аргументы, понял )

  • EXID [1 год назад]

    Здравствуйте!
    Подскажите пожалуйста почему возникает ошибка при выполнении скрипта?

    TypeError: Cannot read properties of undefined (reading 'parameter')
    Код.gs:22

    И что конкретно заполнить в таблице?

    hash name
    qdekR45d Страница 1

    Или ещё что то?

  • iMakeBots [1 год назад → EXID]

    Что у вас в строке 22? 

  • EXID [1 год назад → iMakeBots]

    if(request.parameter.token === config.token) {

    Токен бота я сгенерил новый в отце и вставил

  • iMakeBots [1 год назад → EXID]

    Как вы указывали вебхук?  У вас есть параметр token в нем? 

  • EXID [1 год назад → iMakeBots]

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

  • iMakeBots [1 год назад → EXID]

    Вы запускали функцию setWebHook() из этого кода?

  • EXID [1 год назад → iMakeBots]

    Ну да, написал же что при выполнении произошла ошибка.
    Нажал на кнопку Выполнить

  • iMakeBots [1 год назад → EXID]

    После установки в конфиге параметра webhook - сохранили проект, выбрали метод setWebHook - нажали выполнить

    строка 22 запускается когда вызывается метод doPost

    Телеграм запускает этот метод

  • EXID [1 год назад → iMakeBots]

    Оказывается не выбрал setWebHook()
    Вроде запустился.
    Но как добавлять, редактировать из бота не понятно

  • iMakeBots [1 год назад → EXID]

    Добавлять строку направьте команду /add

  • EXID [1 год назад → iMakeBots]

    Отправить отправил /add боту и в ответ получил Добавили :)))
    В таблице: Название для hgjfkdjfg

    И что это?

  • iMakeBots [1 год назад → EXID]

    Это отработала конструкция добавления новой строки - строка добавилась

    Вы не рассматривайте этот код как универсальный бот - это лишь пример кода по работе бота с таблицей 

    На основе этого вы можете, если вам необходимо, написать бота используя эти наработки - не более

    Я уверен у вас получится лучше и понятнее

  • Андрей Чертовских [1 год назад]

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

  • iMakeBots [1 год назад → Андрей Чертовских]

    Вы про какой бот?