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.
Preparation
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.
1. Create or select a Google Cloud project
A Google Cloud project is required to use this service. Open Google Cloud console, then create a new project or select existing project
2. Enable billing for the project
Like other cloud platforms, Google requires you to enable billing for your project. If you haven't set up billing, open billing page.
3. Enable Google Sheets API
To use a Google Cloud API, you must enable it first. Open this page to enable Google Sheets API.
Getting Google Client Secret
To get authenticated by Google, we're going to use OAuth 2. The following steps show you how to get a client secret.
- Open Google Cloud API Credentials page
- Click on Click Credentials button, then select OAuth client ID
- Enter the name of OAuth client. In Authorized Redirect URIs, enter the URL where the user (or you) will be redirect after having authenticated with Google. If you've never added the domain to authorized domain list, you'll be asked to add your site on the OAuth consent screen. After that, click on the Create button and a client secret file should be dowloaded to your computer.
Code
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
Read a Spreadsheet
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'));
};Append Content to a Spreadsheet
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 },
});
}Update Cells on a Spreadsheet
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 },
});
}Create a New Spreadsheet
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 }
}
});
}Usage Example
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');
};