Node.js - Using Google Sheets API with OAuth 2

2019-02-09 02:57 PM

104

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.

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.

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.

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.

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