Google Sheets API:

gsheet.md

Google Sheets API

• API References: https://developers.google.com/sheets/api/guides/values
• More about credential types: https://github.com/googleapis/google-api-nodejs-client#service-account-credentials
• Quick start: https://developers.google.com/sheets/api/quickstart/nodejs

Main steps:

• Create Google Cloud Project: https://developers.google.com/workspace/guides/create-project
• Enable Google Sheets API: https://console.cloud.google.com/flows/enableapi?apiid=sheets.googleapis.com
• Authorize credentials:
  • In the Google Cloud console, go to Menu menu > APIs & Services > Credentials. https://console.cloud.google.com/apis/credentials
  • Click Create Credentials -> Create service account (Name: google-sheets, Desc: To access google sheets on server) -> Create & Continue -> Empty role & permission -> Done
  • In the service account created -> Select KEYS tab -> ADD KEY -> Create new JSON key -> The key will be downloaded automatically, it allows access to your cloud resources, so store it securely.
• IMPORTANT: Add the service account email created above (google-sheets@xxxxxxxx.iam.gserviceaccount.com) to the sheet you want to work on. Open the sheet -> Share -> Add server account email.
• Install the client library

yarn add dotenv googleapis@105

• Update .env file

# Google Docs key path
# https://console.cloud.google.com/projectselector2/iam-admin/serviceaccounts
GSHEET_APIKEY_PATH=secrets/gkey.json
# https://docs.google.com/spreadsheets/d/[GSHEET_DOC_ID]/edit#gid=0
GSHEET_DOC_ID=xxx-xx

• For example about reading and writing on the sheet

// node src/libs/gsheet.js
// Test connection: node -e "require('./src/libs/gsheet.js').connect()"

require('dotenv').config();
const { google } = require('googleapis');

const APIKEY_PATH = process.env.GSHEET_APIKEY_PATH;
const DOC_ID = process.env.GSHEET_DOC_ID;

let sheets;
const connect = async () => {
  if (!sheets) {
    try {
      const auth = new google.auth.GoogleAuth({
        keyFilename: APIKEY_PATH,
        scopes: ['https://www.googleapis.com/auth/spreadsheets'],
      });
      const authClient = await auth.getClient();
      sheets = google.sheets({ version: 'v4', auth: authClient });
    } catch (e) {
      console.log(`Google Cloud Connecting ERROR:`, e);
    }
  }
};

const readValue = async () => {
  try {
    await connect();
    const res = await sheets.spreadsheets.values.get({
      spreadsheetId: DOC_ID,
      range: ['Sheet1!A1:E'],
    });
    const rows = res.data.values;
    if (!rows || rows.length === 0) {
      console.log('No data found.');
      return;
    }
    console.log('Print columns A to E');
    rows.forEach((row) => {
      console.log(row);
    });
  } catch (e) {
    console.log(`Error readValue`, e);
  }
};

const writeValue = async () => {
  try {
    await connect();
    const res = await sheets.spreadsheets.values.append({
      spreadsheetId: DOC_ID,
      range: ['Sheet1!A1:E'],
      valueInputOption: 'USER_ENTERED',
      resource: {
        values: [[new Date()]],
      },
    });
    const rows = res.data.values;
    if (!rows || rows.length === 0) {
      console.log('No data found.');
      return;
    }
    console.log('Print columns A to E');
    rows.forEach((row) => {
      console.log(row);
    });
  } catch (e) {
    console.log(`Error writeValue`, e);
  }
};

module.exports = {
  connect,
  readValue,
  writeValue,
};

• Test

# Read
node -e "require('./src/libs/gsheet.js').readValue()"
# Write
node -e "require('./src/libs/gsheet.js').writeValue()"