Oh my spreadsheets

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

Важное отступление

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

Авторизация и создание сервисного аккаунта

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

Этот процесс создания аккаунта будет описан ниже, но его не избежать :)

Полный список пунктов, которые нужно выполнить

Для того, чтобы писать данные в таблице вам понадобятся:

  • Google аккаунт (ну это очевидно), а все последующие действия (ну почти) нужно выполнять в Google cloud console
  • Установленная NodeJS (Если будете использовать тот же стек)
  • Гугл таблица (приватная) - то есть доступ есть только у вас и у сервисного аккаунта, который вы создадите дальше
  • Создать новый или выбрать существующий проект в консоли
  • Включить Google Sheets API для вашего проекта
  • Завести сервисный аккаунт в консоли для этого проекта, у них есть страничка в доке с подробным описанием (но я ей не пользовался). От имени этого аккаунта вам потребуется выполнять запросы в API Google Sheets. Поэтому нужно создать ключи в формате JSON и сохранить у себя на машине. Как создать и сохранить ключи от сервисного аккаунта Google в JSON

У созданного пользователя будет обычный email, которому и нужно дать доступ к таблице (кнопка "Настройки доступа", если хотите чтобы этот аккаунт и писал и читал данные, дайте ему права "редактор")

Кстати о таблице, мы будем работать с таблицей users c тремя столбцами (A,B,C) и какими-то данными:

uuid (A) | username (B) | email (C) 123 | testuser | testemail@gmail.com ...

Ближе к делу

Oh my spreasheets small banner

☝️ Тут кстати по ссылке можно посмотреть код библиотеки oh-my-spreadsheets, возможно она будет тебе полезна и ты поставишь звезду ⭐ или форкнешь проект.

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

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

https://docs.google.com/spreadsheets/d/<table-id>/edit#gid=0

Нам понадобится часть, которую я обозначил как <table-id>

Весь приведенный в примерах код я оформил в виде npm пакета, так что те, кому интересно покопаться и разобраться в деталях реализации - welcome to github

Из зависимостей нам потребуется только лишь официальный nodejs клиент от гугл googleapis

Предположим у нас есть такая файловая структура:

  • credentials.json - файл в котором лежат данные для аутентификации в Google Sheets API от имени сервисного аккаунта
  • main.js - файл, в котором мы прочитаем и обновим файлы
  • package.json

Установим зависимости npm install googleapis

Далее создадим клиент, используя ранее сохраненные данные:

// main.js
import { createRequire } from 'node:module';
import path from 'node:path';
import { google } from 'googleapis';

const require = createRequire(import.meta.url);

const creds = require(path.resolve(process.cwd(), 'credentials.json'));
const tableID = '<table-id>';
const client = new google.auth.JWT(
	creds.client_email,
	null,
	creds.private_key,
	['https://www.googleapis.com/auth/spreadsheets']
);

Как видно из кода, я использую ES модули, а не старый-добрый require и для того, чтобы импортировать json модуль credentials.json мне потребуется создать require используя функцию createRequire из nodejs модуля module

Tip: Также не забудьте заменить tableID на реальный идентификатор таблицы

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

// main.js
// ...previous code 

/**
* @returns {Promise<import('googleapis').google.auth.JWT>}
*/
export const authorize = async () => {
	return new Promise((resolve, reject) => {
		client.authorize(function (err) {
			if (err) {
				return reject(err);
			}
			resolve(client);
		})
	})
}

/**
 * Вернет кол-во непустых строк в таблице
 * @param {import('googleapis').google.auth.JWT} client 
 * @returns {Promise<number>}
 */
async function getRowsCount(client, tableID) {
    const gsapi = google.sheets({ version:'v4', auth: client });

    const opt = {
        spreadsheetId: tableID,
        range: 'A1:A'
    };

    let data = await gsapi.spreadsheets.values.get(opt);
    const nonEmptyRows = (data.data.values || []).filter(row => row[0] !== '').length;

    return nonEmptyRows;
}

async function readUsers() {
    const count = await getRowsCount(client, tableID);

    if (count === 0) {
        return []; // Если таблица пуста - отдаем пустой массив
    }

    const gsapi = google.sheets({ version:'v4', auth: client });
    const opt = {
        spreadsheetId: tableID,
        range: `A1:D${count}` // Запрашиваем все непустые ряды
    };

    let data = await gsapi.spreadsheets.values.get(opt);
      
    return data.data.values;
}

async function main() {
	await authorize(); // Авторизуемся в google sheets api
	const users = await readUsers(); // [ [ '123', 'testuser', 'testuser@gmai.com', ... ] ]
}

main();

Как можно увидеть я создал парочку вспомогательных функций: getRowsCount и authorize

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

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

Обновление данных

И вот мы плавно подошли к обновлению данных в таблице, например мы хотим поменять поле email e пользователя во втором ряду. Поле email находится в столбце C. Значит нам нужно обновить ячейку C2 - погнали:

const updateCell = async (cell, value, sheetID) => {
	const gsapi = google.sheets({ version:'v4', auth: client });

	await gsapi.spreadsheets.values.update({
		spreadsheetId: sheetID,
		range: cell,
		valueInputOption: 'USER_ENTERED', // Такое значение позволит записывать строки как обычный текст, а не как формулы и т.д.
		requestBody: {
			values: [[value]], // Новое значение передаем именно в таком формате
		},
	})
}

// Далее по коду, где хотим обновить ячейку
await updateCell('C2', 'newEmail@gmai.com', sheetID);

Функция updateCell отвечает за обновление любой ячейки в таблице

Вуаля, ты поменял значение ячейки, Дружище! Теперь мы можем использовать google таблицу почти как базу данных 🤡

Вместо заключения

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

Поэтому я прикрепил ссылку на репозиторий и npm пакет к этому посту, так что можешь заиспользовать мое поделье или просто форкнуть ( и поставить звезду ) - я буду рад. За деталями заходи на github или поставь себе пакет с помощью npm install oh-my-spreadsheets