Node.js - Using Google Sheets API with OAuth 2

Node.js - Using Google Sheets API with OAuth 2

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.

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

  1. 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.

  1. 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');
  };

Learn More

The Complete Node.js Developer Course (2nd Edition)

Learn and Understand NodeJS

Node JS: Advanced Concepts

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

Top 7 Most Popular Node.js Frameworks You Should Know

Top 7 Most Popular Node.js Frameworks You Should Know

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser. In this post, you'll see top 7 of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser.

One of the main advantages of Node is that it enables developers to use JavaScript on both the front-end and the back-end of an application. This not only makes the source code of any app cleaner and more consistent, but it significantly speeds up app development too, as developers only need to use one language.

Node is fast, scalable, and easy to get started with. Its default package manager is npm, which means it also sports the largest ecosystem of open-source libraries. Node is used by companies such as NASA, Uber, Netflix, and Walmart.

But Node doesn't come alone. It comes with a plethora of frameworks. A Node framework can be pictured as the external scaffolding that you can build your app in. These frameworks are built on top of Node and extend the technology's functionality, mostly by making apps easier to prototype and develop, while also making them faster and more scalable.

Below are 7of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).

Express

With over 43,000 GitHub stars, Express is the most popular Node framework. It brands itself as a fast, unopinionated, and minimalist framework. Express acts as middleware: it helps set up and configure routes to send and receive requests between the front-end and the database of an app.

Express provides lightweight, powerful tools for HTTP servers. It's a great framework for single-page apps, websites, hybrids, or public HTTP APIs. It supports over fourteen different template engines, so developers aren't forced into any specific ORM.

Meteor

Meteor is a full-stack JavaScript platform. It allows developers to build real-time web apps, i.e. apps where code changes are pushed to all browsers and devices in real-time. Additionally, servers send data over the wire, instead of HTML. The client renders the data.

The project has over 41,000 GitHub stars and is built to power large projects. Meteor is used by companies such as Mazda, Honeywell, Qualcomm, and IKEA. It has excellent documentation and a strong community behind it.

Koa

Koa is built by the same team that built Express. It uses ES6 methods that allow developers to work without callbacks. Developers also have more control over error-handling. Koa has no middleware within its core, which means that developers have more control over configuration, but which means that traditional Node middleware (e.g. req, res, next) won't work with Koa.

Koa already has over 26,000 GitHub stars. The Express developers built Koa because they wanted a lighter framework that was more expressive and more robust than Express. You can find out more about the differences between Koa and Express here.

Sails

Sails is a real-time, MVC framework for Node that's built on Express. It supports auto-generated REST APIs and comes with an easy WebSocket integration.

The project has over 20,000 stars on GitHub and is compatible with almost all databases (MySQL, MongoDB, PostgreSQL, Redis). It's also compatible with most front-end technologies (Angular, iOS, Android, React, and even Windows Phone).

Nest

Nest has over 15,000 GitHub stars. It uses progressive JavaScript and is built with TypeScript, which means it comes with strong typing. It combines elements of object-oriented programming, functional programming, and functional reactive programming.

Nest is packaged in such a way it serves as a complete development kit for writing enterprise-level apps. The framework uses Express, but is compatible with a wide range of other libraries.

LoopBack

LoopBack is a framework that allows developers to quickly create REST APIs. It has an easy-to-use CLI wizard and allows developers to create models either on their schema or dynamically. It also has a built-in API explorer.

LoopBack has over 12,000 GitHub stars and is used by companies such as GoDaddy, Symantec, and the Bank of America. It's compatible with many REST services and a wide variety of databases (MongoDB, Oracle, MySQL, PostgreSQL).

Hapi

Similar to Express, hapi serves data by intermediating between server-side and client-side. As such, it's can serve as a substitute for Express. Hapi allows developers to focus on writing reusable app logic in a modular and prescriptive fashion.

The project has over 11,000 GitHub stars. It has built-in support for input validation, caching, authentication, and more. Hapi was originally developed to handle all of Walmart's mobile traffic during Black Friday.

Difference between AngularJS, React, Ember, Backbone, and Node.js.

The most common thing between all of them is that they are Single Page Apps. The SPA is a single page where much of the information remains the same and only some piece of data gets modified when you click on other categories/option.

Node.js Tutorial for Beginners | Node.js Crash Course | Node.js Certification Training

This courseis designed for professionals who aspire to be application developers and gain expertise in building real-time, highly-scalable applications in Node.js. The following professionals can go for this course :

Why learn Node.js?

Node.js uses JavaScript - a language known to millions of developers worldwide - thus giving it a much lower learning curve even for complete beginners. Using Node.js you can build simple Command Line programs or complex enterprise level web applications with equal ease. Node.js is an event-driven, server-side, asynchronous development platform with lightning speed execution. Node.js helps you to code the most complex functionalities in just a few lines of code...

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter

Learn More

The Complete Node.js Developer Course (3rd Edition)

Angular & NodeJS - The MEAN Stack Guide

NodeJS - The Complete Guide (incl. MVC, REST APIs, GraphQL)

Docker for Node.js Projects From a Docker Captain

Intro To MySQL With Node.js - Learn To Use MySQL with Node!

Node.js Absolute Beginners Guide - Learn Node From Scratch

React Node FullStack - Social Network from Scratch to Deploy

Selenium WebDriver - JavaScript nodeJS webdriver IO & more!

Complete Next.js with React & Node - Beautiful Portfolio App

Build a Blockchain & Cryptocurrency | Full-Stack Edition