Node.js - Accessing a spreadsheet using the Google Sheets API

using-sheets-api.md

Using the Sheets API

To access the Sheets API, you will need to enable the Sheets API, obtain credentials, and grant access to your Sheet.

Based on these instructions

1. Enabling the Sheets API

1. Open the Google Developers Console
2. Select your project or create a new one (and then select it)
3. Enable the Sheets API for your project
   • In the sidebar on the left, select APIs & Services > Library
   • Search for "sheets", Click on Google Sheets API
   • Click Enable

2. Obtaining credentials

1. In the sidebar on the left, select APIs & Services > Credentials
2. Click on Create Credentials > Service Account
3. Enter name, description, click Create
4. Skip permissions and roles
5. Click Create Key > JSON > Create
6. JSON key will be downloaded to your computer
7. The JSON file contains your GOOGLE_SERVICE_ACCOUNT_EMAIL and PRIVATE_KEY

3. Granting access to your Sheet

1. Open your Google Sheet in your browser
2. Copy the Spreadsheet ID from the url for later use: https://docs.google.com/spreadsheets/d/<spreadsheetId>/edit
3. Click Share
4. Paste in the GOOGLE_SERVICE_ACCOUNT_EMAIL from the json file. Click Share

You're all set!

The service account email address, cannot actually receive emails, so you may receive a "Delivery Status Notification - Failure" email when the invite message hard bounces off the account. That's normal, ignore it.

4. Using the Api (JavaScript)

While you can use the Google Sheets SDK package, the (google-spreadsheet) is much easier to use (and has better docs!), so we'll use that:

npm i google-spreadsheet

Expose your GOOGLE_SERVICE_ACCOUNT_EMAIL andGOOGLE_SERVICE_ACCOUNT_PRIVATE_KEY as environment variables in your code.

When using environment variables, the private key newlines will have to be unescaped using this regex: `GOOGLE_PRIVATE_KEY.replace(/\n/g, "\n")

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

// Or embed it directly

async function main() {
	const doc = new GoogleSpreadsheet(process.env.GOOGLE_SHEETS_DOCUMENT_ID); 
	await doc.useServiceAccountAuth({
  client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, "\n")
await doc.loadInfo();
console.log(doc.title)
});
}

main();

Learn more

• google-spreadsheet auth guide
• Sheets API - About spreadsheet IDs
• Must grant access to the service account - Github issue
• google-spreadsheet package docs

this code is outdated

but it is very helpful, and Bing AI can update the outdated parts.