Read and append Google Sheets values from Node

The original server-side example used a Google Cloud service account and googleapis@105 to read and append spreadsheet values. Authentication and access to the target document are separate: the service account must be authorized, and the spreadsheet must be shared with its identity.

Prepare the project and document access

The old sequence created a Cloud project, enabled the Sheets API, created a service account, and shared the target sheet with its email address. It downloaded a JSON key for local server authentication; that key is a credential, not content to place in a repository or frontend.

yarn add dotenv googleapis@105

# 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

Only an example key-file path and document-ID field appear here. No credential file was read or imported.

The historical client

// 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,
};

The source passes range as an array even though the API expects an A1-notation string. Its connection helper catches an authentication error without propagating it, so a later call can encounter an uninitialized client. These limitations are explicit rather than silently repaired inside preserved code.

An append response describes an update; it should not be assumed to return the same values structure as a read. USER_ENTERED also lets Sheets interpret input according to spreadsheet rules, which differs from storing an exact raw string.

Keep reads and writes distinct

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

A successful authentication does not establish document permission or a successful append. Check the intended range and update result before treating a write as complete. No spreadsheet, Cloud project, key, or account was accessed or changed here.

Sources: Sheets values API and retrieved notebook revision.

← Back to the garden