Google Sheets API Client

Common errors

• TypeError: GoogleSpreadsheet is not a constructor

  cause Attempting to use CommonJS `require()` syntax (`const { GoogleSpreadsheet } = require('google-spreadsheet');`) in an environment that expects ES modules, or with an incompatible module configuration.
  fix Ensure you are using ESM `import` syntax: `import { GoogleSpreadsheet } from 'google-spreadsheet';`. If using CommonJS, verify your build system or Node.js version supports this package's module format correctly.

• Error: Missing credentials for 'JWT' (or similar 'credential.private_key should be a string' error)

  cause The `GOOGLE_PRIVATE_KEY` environment variable or provided string is not formatted correctly, often due to incorrect handling of newline characters or a malformed key.
  fix Ensure the `key` property in your `JWT` constructor correctly represents the private key. If using `process.env`, replace escaped newlines: `key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n') ?? ''`. Always double-check the private key format from your service account JSON.

• Google API error: The caller does not have permission

  cause The service account or API key used lacks the necessary permissions to access the specific Google Sheet or the Google Sheets API itself.
  fix Verify that the service account email has 'Editor' or 'Viewer' access to the target Google Sheet. Also, ensure the Google Sheets API is enabled for your project in the Google Cloud Console.

• (node:...) UnhandledPromiseRejectionWarning: TypeError: Cannot read properties of undefined (reading 'loadInfo')

  cause This typically occurs when `await` is used at the top level of a script without being inside an `async` function, and the Node.js version in use does not support top-level `await`.
  fix Wrap your top-level `await` calls within an immediately invoked async function expression (IIFE): `(async function() { /* your code here */ })();` or ensure your Node.js version supports top-level await.

Warnings

• breaking Version 5.0.0 introduced significant modernization and dependency updates, particularly affecting `google-auth-library`. This may require adjustments if you were using older versions of `google-auth-library` (e.g., v8 or lower) or relied on internal behaviors prior to v5.
  Fix: Ensure your `google-auth-library` peer dependency is `>=8.8.0`. Review your authentication setup, as older patterns might be deprecated or incompatible. Refer to the official authentication guide.
• deprecated Direct authentication methods like `doc.useServiceAccountAuth()` have been deprecated and removed. Users should now instantiate authentication clients (e.g., `JWT`, `GoogleAuth`) from `google-auth-library` and pass them directly to the `GoogleSpreadsheet` constructor.
  Fix: Replace calls to `doc.useServiceAccountAuth()` with creating an authenticated client from `google-auth-library` and passing it to `new GoogleSpreadsheet(id, authClient)`.
• gotcha The example code often utilizes top-level `await`. This feature requires Node.js versions that support it (Node.js 14.8+ with ES modules, or Node.js 16+ without explicit module configuration). In older environments, top-level `await` will throw a syntax error.
  Fix: If your environment doesn't support top-level `await`, wrap your async code in an immediately invoked async function expression (IIFE): `(async () => { /* your code */ })();`.
• breaking The peer dependency for `google-auth-library` was explicitly updated to `>=8.8.0`. Using older versions of `google-auth-library` with `google-spreadsheet` v5.x may lead to runtime errors or type conflicts.
  Fix: Upgrade your `google-auth-library` package to version `^8.8.0` or higher to ensure compatibility. For example: `npm install google-auth-library@latest`.
• gotcha When providing the `GOOGLE_PRIVATE_KEY` via an environment variable, newline characters (`\n`) within the key must be correctly escaped as `\\n` when setting the variable, and then unescaped when reading it back in JavaScript. Failure to do so will result in an invalid private key format.
  Fix: If setting `GOOGLE_PRIVATE_KEY` as an environment variable, ensure newlines are `\\n`. In your JavaScript code, you might need `process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n')` to correctly parse it.
• gotcha Prior to v5.2.0, retrieving an empty cell might have yielded `undefined`. Since v5.2.0, empty cells are guaranteed to return an empty string (`''`), which could subtly affect existing logic that explicitly checks for `undefined`.
  Fix: Update your code to anticipate `''` for empty cell values when on or above v5.2.0, or explicitly handle both `undefined` and `''` if supporting a range of versions.

Install

• npm install google-spreadsheet npm
• yarn add google-spreadsheet yarn
• pnpm add google-spreadsheet pnpm

Imports

• GoogleSpreadsheet
  wrong:

  const GoogleSpreadsheet = require('google-spreadsheet');

  correct:

  import { GoogleSpreadsheet } from 'google-spreadsheet';

  Since v5.0.0, the library is primarily designed for ESM `import` statements and ships with TypeScript types. While CommonJS `require` might function in some setups, it's not the recommended or idiomatic approach.
• JWT
  wrong:

  import { JWT } from 'google-spreadsheet';

  correct:

  import { JWT } from 'google-auth-library';

  Authentication classes like `JWT` are not directly exported by `google-spreadsheet`; they must be imported from the `google-auth-library` peer dependency.
• GoogleSpreadsheetWorksheet (type)

  import type { GoogleSpreadsheetWorksheet } from 'google-spreadsheet';

  For type-only imports in TypeScript, use `import type` to ensure they are correctly removed during compilation, avoiding potential runtime issues or unnecessary bundle size.

Quickstart

Demonstrates initializing the GoogleSpreadsheet client with service account credentials, loading document properties, and performing basic operations like reading/updating document/sheet titles and managing sheets.

import { GoogleSpreadsheet } from 'google-spreadsheet';
import { JWT } from 'google-auth-library';

// Initialize auth - use environment variables for security
// process.env.GOOGLE_PRIVATE_KEY should have '\n' replaced with '\\n' if set as env var
const serviceAccountAuth = new JWT({
  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL ?? '',
  key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, '\n') ?? '', // Handle escaped newlines
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

async function main() {
  const doc = new GoogleSpreadsheet(process.env.GOOGLE_SHEET_ID ?? '', serviceAccountAuth);

  await doc.loadInfo(); // loads document properties and worksheets
  console.log('Document title:', doc.title);
  await doc.updateProperties({ title: 'Renamed Doc via API' });
  console.log('Updated document title to:', doc.title);

  const sheet = doc.sheetsByIndex[0]; // Access the first sheet
  console.log('First sheet title:', sheet.title);
  console.log('First sheet row count:', sheet.rowCount);

  // Adding and removing a new sheet
  const newSheet = await doc.addSheet({ title: 'Another Sheet ' + Date.now() });
  console.log('Added new sheet:', newSheet.title);
  await newSheet.delete();
  console.log('Deleted the new sheet.');
}

// Run the main function (or wrap in an IIFE if top-level await is not supported)
main().catch(console.error);