Google Sheets API JSON

Repository example for using Google Sheets as an API (JSON)

Problem / Motivation

English

You can use Google Spreadsheets to do without a backend for your application if you need to store certain data and then use it for your API. All data can be conveniently changed in the Google table and they will be dynamically available in real-time.

Ukrainian

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

How to use Google Spreadsheets

1. Create a Google Spreadsheet. The spreadsheet itself does not have to be public when the web app is deployed to execute as its owner.
2. Add a sheet named API-example and create the six columns shown in the example spreadsheet.
3. Open Extensions → Apps Script.
4. Copy the code from api-example.gs.
5. Copy the ID from your spreadsheet URL and put it in SPREADSHEET_ID.
6. If your sheet has a different tab name, update SHEET_NAME.
7. Select testApi in the Apps Script function menu and click Run. Do not run prepareGsData with an argument; the current version needs no arguments.
8. Approve the requested Google Sheets permission. A successful test logs a JSON object.
9. Select Deploy → New deployment → Web app. Execute the app as yourself and choose who may access the generated /exec URL.

Example deployment: https://script.google.com/macros/s/AKfycby0yU7sjcUrlIKFdsKIcuyOE3wABb6ysAWwB7XDyOWGQxEMOcCxg2fUFg2xrqjvO72f/exec

Anyone who can access the web app URL can read the JSON it returns. Do not put private or personal data in a sheet exposed by a public deployment.

Local tests

The tests use built-in Node.js modules and do not require npm install:

node --check --input-type=commonjs < api-example.gs
node tests/api-example.test.mjs

Links

Google Apps scripts documentation - https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

Google Sheets - https://docs.google.com/spreadsheets/d/e/2PACX-1vRuyjAsFpk4matTzw64SRQxsZb2FKx_dl-R4_OibJdkpgVyfaCH5aCPuGGAul1xRNUbvXx3sdhqd2ml/pubhtml

Google Apps Script: A Beginner’s Guide - https://www.benlcollins.com/apps-script/google-apps-script-beginner-guide/

Author

Volodymyr Melnychuk

Proposals

If you have suggestions for improvement, you can create an issue.