Как использовать Google Sheets в качестве базы данных

Привет, я Настя — младший разработчик в M2. М2 — это экосистема цифровых сервисов, которые помогают пользователям проводить сделки с недвижимостью удобно, быстро и безопасно. Мы с командой разрабатываем CMS, которая позволяет управлять контентом на сайте m2.ru

Расскажу о том, как мы используем Apps Script для парсинга данных из Google Sheets и зачем это нам. Если вам интересна только пошаговая инструкция с примерами кода, ее можно посмотреть здесь.

Где это использовать?

• Если перед вами стоит похожая задача, о которой я расскажу дальше, когда нужно быстрое и простое решение, которое сэкономит кучу часов рабочего времени вас и ваших коллег.

• В пет‑проектах, когда нет бэкенда, но нужно соорудить что‑то типа базы данных.

• А еще можно использовать в своих собственных проектах, например, мой коллега вдохновился и решил накопить на квартиру с помощью Google Sheets + Apps Script. В этой статье он делится своими наработками.

Как мы докатились до жизни такой

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

Честно говоря, загружать контент на сайт было утомительным занятием. Сначала сотрудники саппорта собирали все ответы в Google Sheets, потому что в таком формате им удобно хранить и редактировать данные. Затем им нужно было внести все те же самые данные в CMS. Автоматической интеграции к тому моменту не было, поэтому им приходилось копировать и вставлять каждый вопрос/ответ вручную. И, наконец, уже оттуда опубликовать на сайт. Эта рутинная работа занимала много времени, поэтому коллеги обратились к нам с задачей облегчить этот процесс. Тем более, впереди нас ожидало глобальное обновление всего раздела саппорта, а, значит, мы рисковали на много часов потерять коллег.

Зачем вообще нужна такая прослойка в виде CMS и почему бы не загружать напрямую из Google Sheets? Во‑первых, это невозможно технически, а, во‑вторых, в целях безопасности, так как все данные сохраняются у нас в базе, и если однажды Google совсем не будет доступен в России, то у нас ничего не сломается.

После небольших раздумий нам пришла в голову идея: если ребятам удобно работать в Google Sheets, то мы можем использовать ее, как некую базу данных и написать скрипт, который будет парсить все эти данные и выгружать в наш сервис нажатием на одну кнопку. Таким образом, мы сделаем их работу более удобной и быстрой. А еще эта идея была самой простой и бюджетной в реализации.

На помощь нам пришел Apps Script — платформа на основе JavaScript, которая дает возможность быстро разрабатывать приложения, позволяющие автоматизировать, настраивать и расширять возможности работы с Google Sheets и не только с ними. 

Этот вариант подошел нам больше всего, так как Google Sheets были исходными данными, а там есть только Apps Script. В качестве альтернативы можно было бы рассмотреть реализацию своих «гугл‑таблиц» или поднятие бекэнда для яндекс-таблиц, но мы искали быстрое и недорогое решение.

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

Инструкция — как использовать Google Sheets в качестве базы данных

Шаг 1

Заходим в таблицу, с которой мы будем работать. В меню выбираем пункт Расширения → Apps Script.

Шаг 2

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

/* spreadsheetId - id таблицы columnMapping - маппинг названий колонок (колонки, которые тут не перечислены, не будут включены в json) dropdownMappings - маппинг раскрывающихся списков (при необходимости), чтобы сразу же использовать их без последующей конвертации spreadSheetMapping - маппинг названий листов (листы, которые тут не перечислены, не будут включены в json) startRow - указываем номер строки, с которой начинается таблица (включая строку с заголовками) */  let config = {   spreadsheetId: '',   columnMapping: {},   dropdownMappings: {},   sheetsMapping: {},   startRow: 2, };

Соответственно, в данном конфиге ключ — это название сущности в таблице, а значение — то, как мы хотим это видеть в JSON.

Шаг 3

Далее приступаем к написанию функций. Первая функция convertRowToJson преобразует каждую строку в таблице в объект JSON:

function convertRowToJson(row, headers, config, sheetName) {   const record = {};    headers.forEach((header, index) => {     if (config.columnMapping.hasOwnProperty(header)) {       const value = row[index];       const mappedHeader = config.columnMapping[header];              if (config.dropdownMappings.hasOwnProperty(header) && config.dropdownMappings[header].hasOwnProperty(value)) {         record[mappedHeader] = config.dropdownMappings[header][value];       } else {         record[mappedHeader] = value;       }     }   });    record.sheetName = sheetName;    return record; }

Шаг 4

Следующая функция convertRangeToJson преобразовывает диапазон данных из таблицы в массив объектов JSON, используя config и имя листа. Здесь же мы используем нашу предыдущую функцию convertRowToJson:

function convertRangeToJson(data, config, sheetName) {   const jsonArray = [];      if (!data || data.length < config.startRow) {     return jsonArray;   }    const headers = data[config.startRow - 1];    data.slice(config.startRow).forEach(row => {     const record = convertRowToJson(row, headers, config, sheetName);     jsonArray.push(record);   });    return jsonArray; }

Шаг 5

Функция processSheet обрабатывает данные с каждого листа, указанного в конфиге:

function processSheet(sheet, config) {   const sheetName = sheet.getName();    if (!config.sheetsMapping.hasOwnProperty(sheetName)) {     return [];   }    const mappedSheetName = config.sheetsMapping[sheetName];    const data = sheet.getDataRange().getValues();    if (data.length < config.startRow) {     return [];   }    return convertRangeToJson(data, config, mappedSheetName); }

Шаг 6

И, наконец, последняя функция doGet, которая позволяет создать простое REST API для доступа к данным Google Sheets и используется для обработки HTTP GET-запросов:

function doGet(req) {   const spreadsheet = SpreadsheetApp.openById(config.spreadsheetId);   const sheets = spreadsheet.getSheets();    const allData = sheets.reduce((acc, sheet) => {     const jsonData = processSheet(sheet, config);     return acc.concat(jsonData);   }, []);    return ContentService.createTextOutput(JSON.stringify(allData))     .setMimeType(ContentService.MimeType.JSON); }

Шаг 7

Теперь остается сохранить изменения и развернуть проект. После сохранения нажимаем на «Начать развертывание» → «Новое развертывание». В открывшемся окне заполняем все поля, как на скрине (тип — веб‑приложение, запуск от моего имени, у кого есть доступ — все) и нажимаем на «Начать развертывание»:

Шаг 8

При нажатии на кнопку «Начать развертывание» у вас может открыться окно с требованием разрешить доступ к данным. В таком случае нужно нажать на Предоставить доступ, выбрать свой аккаунт в Google и… вы увидите такую страницу:

Чтобы все поправить, нужно нажать на «Advanced» в левом нижнем углу и в открывшемся блоке нажать на ссылку перехода к своему проекту. После этого развертывание завершится и вы получите url, к которому можно делать запросы. Можно нажать на него и увидеть, что получилось:

Если вам нужно что-то поменять в скрипте, например, поправить конфиг, то после сохранения внесенных изменений:

1. нажмите на «Начать развертывание» → «Управление развертываниями».

2. В открывшемся окне выберите свой проект, нажмите на иконку редактирования и в поле «Версия» выберите «Новая версия».

3. После этого нажмите на «Начать развертывание».

Шаг 9

Затем можно делать с полученным JSON все, что угодно. Например, мы написали на бэкенде небольшой сервис, который обращается по указанному эндпоинту и сохраняет полученные результаты в базе. После этого мы уже работаем с этими данными для публикации контента на сайте.

И что в итоге получилось?

Таким образом, алгоритм работы сотрудника отдела саппорта стал гораздо проще: он заполняет свою таблицу → заходит в наш сервис → нажимает кнопку «Импортировать» → проверяет загруженные данные → публикует на сайт.

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

А как же безопасность?

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

Заключение

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

Расскажите, используете ли вы Apps Script в своих проектах? Какие проблемы решали с помощью такого инструмента? А может быть вы столкнулись со сложностями, тоже интересно было бы об этом узнать.