Do you need to read the content of a Google Sheets spreadsheet from your Node.js application? Or maybe you need to write to or delete Google Sheets file? In this tutorial, I’m going to show you how to use Google Sheets API v4 in Node.js including getting credentials and code examples.
Before starting to code, make sure you have a Google Cloud project with billing and Google Sheets API enabled. Please do the following if you haven’t done it yet.
A Google Cloud project is required to use this service. Open Google Cloud console, then create a new project or select existing project
Like other cloud platforms, Google requires you to enable billing for your project. If you haven’t set up billing, open billing page.
To use a Google Cloud API, you must enable it first. Open this page to enable Google Sheets API.
To get authenticated by Google, we’re going to use OAuth 2. The following steps show you how to get a client secret.
To make it easy to access Google Cloud APIs, we’re going to use googleapis library. It has google.sheets class that wraps the call to Sheets API. Add it to the dependencies section of your package.json.
"dependencies": {
...
"googleapis": "~32.0.0",
...
}
In order to use google.sheets, you need to pass an authentication client.google.auth.OAuth2 is a constructor which returns an OAuth2 client instance. It has setCredentials method for setting the credentials to be used which is the access token and refresh token. The question is how to get those tokens.
First you need to authorize your application by visiting a unique URL. To generate the URL, use the code below.
google-oauth-generate-url.js
const fs = require('fs');
const { google } = require('googleapis');
const credentials = JSON.parse(fs.readFileSync('google-client-secret.json', 'utf-8'));
const {
client_secret: clientSecret,
client_id: clientId,
redirect_uris: redirectUris,
} = credentials.installed;
const oAuth2Client = new google.auth.OAuth2(
clientId, clientSecret, redirectUris[0],
);
// Generate a url that asks permissions for Gmail scopes
const SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
];
const url = oAuth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES,
});
console.info(`authUrl: ${url}`);
If it runs successfully, you should get the URL on your console. Open the URL using a web browser. You may need to login or select account if you’ve multiple accounts. Then you’ll be redirected to a page asking permission to allow your application to manage Google Sheet spreadsheets.
If successful, you should get the code. Now it’s time to get the tokens. Use the script below and replace const code value with the code you’ve got.
google-oauth-get-tokens.js
const fs = require('fs');
const { google } = require('googleapis');
// Replace with the code you've got from the previous step
const code = '4/AABBCC-abcdEFGH1-aBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDeaBcDe';
const credentials = JSON.parse(fs.readFileSync('google-client-secret.json', 'utf-8'));
const {
client_secret: clientSecret,
client_id: clientId,
redirect_uris: redirectUris,
} = credentials.installed;
const oAuth2Client = new google.auth.OAuth2(
clientId, clientSecret, redirectUris[0],
);
const getToken = async () => {
const { tokens } = await oAuth2Client.getToken(code);
console.info(tokens);
fs.writeFileSync('google-oauth-token.json', JSON.stringify(tokens));
};
getToken();
Run the script above. If successful, a file named google-oauth-token.json containing access token and refresh token should be generated.
{
access_token: 'abcd.abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCDef12abCD',
token_type: 'Bearer',
refresh_token: '1/abc123abc123abc123abc123abc123abc123abc123a',
expiry_date: 1529136753542
}
After obtaining the tokens, now we can create an OAuth2 client. The library can automatically request a new access token if the current one is expired, so we don’t need to manually renew the token. Below is the code for authentication using OAuth 2.
helpers/google-sheet.js
const _ = require('lodash');
const fs = require('fs');
const { google } = require('googleapis');
const credentials = JSON.parse(fs.readFileSync('google-credentials.json', 'utf-8'));
const {
client_secret: clientSecret,
client_id: clientId,
redirect_uris: redirectUris,
} = credentials.installed;
const oAuth2Client = new google.auth.OAuth2(
clientId, clientSecret, redirectUris[0],
);
const token = fs.readFileSync('google-oauth-token.json', 'utf-8');
oAuth2Client.setCredentials(JSON.parse(token));
A spreadsheet is referenced by its ID. The ID is part of the URL when you open the spreadsheet. The bold part of the URL below is the ID. https://docs.google.com/spreadsheets/d/12ABc12ABc12ABc12ABc12ABc12ABc123ABc-1234abcd/edit#gid=1234512345.
Another term you need to understand is range. A range is specified by A1 notation. Below are some examples of using Google Sheets API in Node.js
helpers/google-sheet.js
/**
* Read a spreadsheet.
* @param {string} spreadsheetId
* @param {string} range
* @returns {Promise.<Array>}
*/
exports.read = async (spreadsheetId, range) => {
const sheets = google.sheets({ version: 'v4', auth: oAuth2Client });
return sheets.spreadsheets.values.get({
spreadsheetId,
range,
})
.then(_.property('data.values'));
};
helpers/google-sheet.js
/**
* Append content to the next line of a spreadsheet on specified range.
* @param {string} spreadsheetId
* @param {string} range
* @returns {Promise}
*/
exports.append = async (spreadsheetId, range, values) => {
const sheets = google.sheets({ version: 'v4', auth: this.oAuth2Client });
return sheets.spreadsheets.values.append({
spreadsheetId,
range,
valueInputOption: 'USER_ENTERED',
resource: { values },
});
}
helpers/google-sheet.js
/**
* Update cells on a spreadsheet.
* @param {string} spreadsheetId
* @param {string} range
* @returns {Promise}
*/
exports.update = async (spreadsheetId, range, values) => {
const sheets = google.sheets({ version: 'v4', auth: this.oAuth2Client });
return sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption: 'USER_ENTERED',
resource: { values },
});
}
helpers/google-sheet.js
/**
* Create a new spreadsheet.
* @param {string} spreadsheetId
* @param {string} range
* @returns {Promise}
*/
exports.create = async (title) => {
const sheets = google.sheets({ version: 'v4', auth: this.oAuth2Client });
return sheets.spreadsheets.create({
resource: {
properties:{ title }
}
});
}
example.js
const helpers = require('helpers/google-sheet');
const tryGoogleSheets = async () => {
await helpers.read('the-spreadsheet-id', 'Sheet1!A:Z');
await helpers.append('the-spreadsheet-id', 'Access!Y:Z', [ ["1", "2", "3"], ["4", "5", "6"] ]);
await helpers.update('the-spreadsheet-id', 'Access!Y7:AA', [ ["A", "B", "C"], ["D", "E", "F"] ]);
await helpers.create('Example spreadsheet');
};
☞ The Complete Node.js Developer Course (2nd Edition)
☞ GraphQL: Learning GraphQL with Node.Js
☞ Angular (Angular 2+) & NodeJS - The MEAN Stack Guide
☞ Beginner Full Stack Web Development: HTML, CSS, React & Node
☞ Node with React: Fullstack Web Development
☞ MERN Stack Front To Back: Full Stack React, Redux & Node.js
#node-js