Google Sheets API on Node.js

_README.md

Use Google Sheets as a "database" for simple or small size applications

References

Thanks to https://www.alec.fyi/set-up-google-sheets-apis-and-treat-sheets-like-a-database.html

Setup GCP

Reuse or create a project

1. Go to https://console.developers.google.com/ and use an existing project
2. Or you can create a new project by going to https://console.developers.google.com/projectcreate

Enable the needed APIs

1. Search for the "Google Sheets API" by using the search box at the top of the page
2. Enable the API
3. Search for the "Google Drive API" by using the search box at the top of the page
4. Enable the API

Create a service account to enable server 2 server communication

1. Click the hamburger menu -> "APIs and Services" -> "Credentials"
2. Click "+CREATE CREDENTIALS" and select "Service account"
3. Enter a service account, click "CREATE" and "DONE"
4. You'll end up on the credentials screen, click the newly created service account at the bottom of the page
5. Click "ADD KEY" -> "Create new key". Make sure to save the json on your workstation

Share the sheet to your service account

In Google Sheets, open the sheet you want to use as a "database".

1. Click "Share", in the share dialog, you can simply put the service account email address and click done.

Setup Node.js

On your workstation, run the following:

yarn init;
yarn add dotenv google-spreadsheet;

Setup the necessary environment variables

touch .env

In this .env file, you will need to put the following variables:

GOOGLE_SHEET_ID="your google sheet id, this is the long id in the sheets URL when you have it open in your browser"
GOOGLE_SERVICE_ACCOUNT_EMAIL="client_email value from your key json"
GOOGLE_PRIVATE_KEY="private_key value from your key json"

Copy the code below into an index.js file, afterwards simply run node index.js. It should output the title of your doc.

Go forth and call them APIs

You can build this into an application or a server; Google Sheets is now your API oyster, have fun!

index.js

const { GoogleSpreadsheet } = require('google-spreadsheet');
require('dotenv').config()

// spreadsheet key is the long id in the sheets URL
const doc = new GoogleSpreadsheet(process.env.GOOGLE_SHEET_ID);

(async () => {
    // use service account creds
    await doc.useServiceAccountAuth({
        client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
        private_key: process.env.GOOGLE_PRIVATE_KEY,
    });

    await doc.loadInfo(); // loads document properties and worksheets
    console.log(doc.title);
})().catch(e => {
    console.log(e);
});