Google Sheets API

nhancv

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 ([email protected]) 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()"