Как использовать 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, к которому можно делать запросы. Можно нажать на него и увидеть, что получилось:
Если вам нужно что-то поменять в скрипте, например, поправить конфиг, то после сохранения внесенных изменений:
нажмите на «Начать развертывание» → «Управление развертываниями».
В открывшемся окне выберите свой проект, нажмите на иконку редактирования и в поле «Версия» выберите «Новая версия».
После этого нажмите на «Начать развертывание».
Шаг 9.
Затем можно делать с полученным JSON все, что угодно. Например, мы написали на бэкенде небольшой сервис, который обращается по указанному эндпоинту и сохраняет полученные результаты в базе. После этого мы уже работаем с этими данными для публикации контента на сайте.
И что в итоге получилось?
Таким образом, алгоритм работы сотрудника отдела саппорта стал гораздо проще: он заполняет свою таблицу → заходит в наш сервис → нажимает кнопку «Импортировать» → проверяет загруженные данные → публикует на сайт.
Получается, что мы сократили примерно в 2 раза работу по загрузке вопросов на сайт, так как теперь нет необходимости дублировать контент, сделали этот процесс более удобным, интуитивно понятным и гибким. При этом мы потратили совсем немного времени на реализацию такой идеи.
А как же безопасность?
Да, конечно, этот способ не заменит полноценный бэкенд и не подойдет для хранения конфиденциальной информации. И, вообще, может произойти все, что угодно, так что к этому нужно относиться осторожно.
Заключение
В этой статье я поделилась пошаговой инструкцией использования Google Sheets в качестве подобия базы данных. Код можно скопировать полностью, настроить конфиг под себя и использовать. Надеюсь, что вам это поможет легко и быстро внедрить такую схему в свой проект.
Расскажите, используете ли вы Apps Script в своих проектах? Какие проблемы решали с помощью такого инструмента? А может быть вы столкнулись со сложностями, тоже интересно было бы об этом узнать.
