This was originally published on my blog, where I often write about learning to code, remote work, and women in tech.

3 Approaches for Using the Google Sheets API in Node.js: A Tutorial

After learning to code for the past year, I’ve recently found myself in the realm of asynchronous Javascript.

Throughout the last few months, I’ve really struggled with this component of the language and have had to go over parts of the course I had taken many times over. Even after doing that, I still felt pretty lost in terms of how I could take the information and translate it to something actionable.

During my most recent review of the material, I realized the only way that it would truly stick would be to create something with it. So, I decided to test my knowledge by building out a tool called Progression Page. Upon creating this application, I did feel much more capable of utilizing tools in the asynchronous toolbox.

Progression Page is a tool that helps people track and visualize their daily progress in learning to code, so in the spirit of helping others learn, I decided to share this tutorial to inspire others to create mini-applications using Google Sheets as their database.

Using Google Sheets as a Database

I track everything that I do in Google sheets. When I say everything, I mean everything.

I track how often I exercise, how many books I get through, how often I learn to code, and much more. I do this to keep myself accountable because I believe in quantifying progress is such an important precursor to “success”﹣not just in my “work life”, but in all facets.

Since tracking my effort has been so essential in my personal progress, I’ve always been surprised when others don’t do the same, especially when the lack of tracking tends to end with results like this:

I wanted a place where people could easily log their progress towards coding publicly, so people could keep them accountable. That’s exactly what Progression Page does; it allows each member to log their daily progress in terms of what they’re learning, how long, and how often in a Google sheet, in addition to their skills, goals, and more. This information is pulled directly from the sheet and displayed on the webpage.

Progression Page is one of many examples of people using Google Sheets as a database. For example, Sheet2Site utilizes Sheets as the database for sites ranging from events to products to job boards. Although there are certainly pitfalls of using Sheets as a database., the beauty of it is that you (and others) can easily view, edit, and scale the information as needed. With that in mind, I can’t wait to see what people create with this technology at their fingertips.

Preface

Before jumping in, I want to preface that my code may not be "optimal" or refactored exactly as it should be. Remember, I’m still a n00b (aren’t we all?).

As someone still new to development, I’m focused on creating things that work, before worrying about how scalable or flawless the application may be.

I also should mention that this article is created specifically for those using Node.js, although I’m sure it can be tailored for other backends. I won’t go through the steps to set up a Node.js/Express.js application from the very ground up, so it’s really for those familiar with that infrastructure.

Things that this article will go over:

• How to authenticate OAuth2 in Node.js
• How to connect with the Google Sheets API with Node to create a Google Spreadsheet database through 3 approaches
• How to integrate data from the API into an Express.js application

Things that this article will not go over:

• How to style or deploy a web page
• How to use Node.js or Express.js from the ground up
• How to use the API across other back-end languages

Getting Started

To start, you should have a basic Node application set up with npm initialized.

From here, you’ll need to install the packages that you’ll be using for this application:

• express (for rendering dynamic pages)
• request (for making HTTP requests)
• fs (for the Google API)
• readline (for the Google API)

• googleapis (for the Google API)
  

  npm install express ejs request gs readline googleapis@39 --save
  

Ensure that you’ve properly required each of these dependencies at the top of your index file, as shown below.

var express     = require("express"),
fs              = require('fs'),
readline        = require('readline'),
{google}        = require('googleapis'),
request         = require('request'),
app             = express();

From here, you should also set your view engine to ejs so that the program recognizes EJS files that are being rendered without needing to define the file extension each time (ie: you can easily call res.render('test') instead of res.render('test.ejs')).

app.set("view engine", "ejs");

Setting up a Route in Node.js

Since this particular application will send the pulled data to a webpage, we first need to set up the route that will be serving this application. For example, this can be a standard route that we’ll direct to the test subdirectory, rendering the test.ejs file. I’ll be editing this endpoint for each part of the tutorial.

app.get("/test", function(req, res){
  res.render('test')
});

Setting up the Rendered Page

You’ll need to create a views directory which hosts your EJS files. I also set up a few other things that I utilize in my node applications which aren’t essential to get started, but are needed for other steps past this tutorial, like adding styling.

• I added header and footer partials, although those are not necessary for every application. If you do not know what those are, just ensure that your entire express files have all of the required information as a normal HTML file would have, like the DOCTYPE and <head> section.
• I added a public directory to house styling and other files supporting the site. In order for the application to recognize this directory, the following line is required prior to any routing.

app.use(express.static(__dirname+"/public"));

Once you’ve taken these steps, your directory should look something like this!

At this point, you should have the core dependencies in place for our Node application, so we can start working with the API!

Linking Node.js to Google Sheets

A bulk of this tutorial will be going through how to connect with the Google API. There are multiple ways to connect with the API and I’ll be sharing three:

• Google Sheets HTTP Requests Via V3
• Google Sheets API v4 with OAuth2
• Google Developer Console and google-spreadsheet Package with v4

You are welcome to utilize any of the methods below and skip to the approach that is most helpful to you. Before hopping in, I should mention that I would encourage most applications to use v4 for the following reasons:

Reasons to use v3 HTTP method:

• It’s easier to set up

Reasons to use v4:

• v3 will get deprecated eventually
• Using the v3 method requires the spreadsheet to be published
• Using the v3 method only allows reading and not writing
• You are dependent on the order of tabs with the v3 method
• You have less flexibility in your requests with the v3 method
• The output is cleaner in v4

Method 1: HTTP Requests Via V3

As of today, arguably the easiest way of accessing the API is through v3.

This version offers an endpoint that returns all of the information in a single JSON simply by accessing a designated URL. To utilize this method, it’s as simple as the following steps:

1. Go to your spreadsheet and click File → Publish to the Web

1. Select the parts of the document that you want to extract data from. These must be published in order to pull data via this method.
2. Utilize this URL structure to access the data, replacing SPREADSHEET_ID and TAB_NUMBER with the correct information from your sheet:
   • https://spreadsheets.google.com/feeds/cells/SPREADSHEET_ID/TAB_NUMBER/public/values?alt=json
   • For example, the following URL will return the information from the first image with a JSON depicted in the second: https://spreadsheets.google.com/feeds/cells/1UIV4RkOx8KJK2zQYig0klH5_f8FCOdwIWV8YF2VyF8I/2/public/values?alt=json
   • If you remove ?alt=json from the end, you’ll receive the data back as an XML.

Should we want to make this request via Node, instead of the browser, we can utilize the request package that was installed at the beginning of this tutorial. If you’ve never used the request package, you can find the documentation here. Below is an example of the code that would be run in node at the URL: domain.xyz/v3, rendering the page and passing in the response object that can be used in the express ‘test’ file.

app.get("/v3", function(req, res){

  // Defining the request URL
  var options = {
      url: 'https://spreadsheets.google.com/feeds/cells/1UIV4RkOx8KJK2zQYig0klH5_f8FCOdwIWV8YF2VyF8I/2/public/values?alt=json'
  }

  // Using the request package to pull the information using the options object defined above
  request(options, callback)

  // Callback function logging the request body in the console if it was successful
  function callback(error, response, body){
    if (!error && response.statusCode == 200) {
      console.log(body);

      // Rendering test express file while passing in the response object to be used.
      res.render('test', {response: response})
    } else {
      console.log(error)
    }
  }

})

Returning to the limitations outlined earlier, I should mention that although the v3 API is quite easy to use, I would recommend continuing on to learn about how to utilize v4 of the API.

Outside of v3 requiring your data to be public, not allowing any writing to the spreadsheet, and less flexibility with your requests, perhaps the most compelling issue is that v3 will be deprecated at some point in the future by Google and so it’s worth moving onto a more reliable solution now.

Method 2: Node.js OAuth2 with V4

Let’s get started with v4! This section will teach you to utilize v4 through the methodology that Google provides (without any third party packages).

Google already provides pretty good documentation for how to begin integrating with v4 of the Sheets API. They offer a Quickstart guide for most back-end languages including PHP, Python, Ruby, .NET, Go, Java, Go, and of course, Node.js.

You’ll see in the quickstart section that there are four steps to follow. You can follow the instructions directly from this page provided by Google, but I’ve left a couple guiding notes below.

Step 1: Turn on the Google Sheets API

• Once you’ve enabled the API, you’ll get a client ID and client secret that can later be managed in the API console. For the purposes of this type of authentication, you will only need the credentials.json file which should be saved in your working directory (ie: wherever you have app.js or index.js running).

Step 2: Install the client library

• If you followed the earlier steps of this tutorial, you will have already installed the dependencies.

Step 3: Set up the sample

• If you followed the earlier steps of this tutorial, you will have already required the dependencies at the top of your app.js or index.js file. You can now copy the code over from the quickstart page to your working Node file. A few clarifying notes on the code:

Authentication

The function fs.readFile() actually initiates the process of authentication via calling the authorize() function, using the information in your credentials.json file.

The test code is set up to call listMajors() as the callback function upon authentication, which we’ll change later on.

fs.readFile('credentials.json', (err, content) => {
  if (err) return console.log('Error loading client secret file:', err);
  // Authorize a client with credentials, then call the Google Sheets API.
  authorize(JSON.parse(content), listMajors);
});

In order to perform the authentication, there is a set of functions that actually runs the process. The authorize() and getNewToken() functions are predefined and can be moved to the bottom of your Node file or even exported in from another file. There is no need to mess around with this code and it can actually be placed outside of your routing, in order to clean up your code.

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
function authorize(credentials, callback) {
  const {client_secret, client_id, redirect_uris} = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
      client_id, client_secret, redirect_uris[0]);

  // Check if we have previously stored a token.
  fs.readFile(TOKEN_PATH, (err, token) => {
    if (err) return getNewToken(oAuth2Client, callback);
    oAuth2Client.setCredentials(JSON.parse(token));
    callback(oAuth2Client);
  });
}

/**
 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
 * @param {getEventsCallback} callback The callback for the authorized client.
 */
function getNewToken(oAuth2Client, callback) {
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline',
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });
  rl.question('Enter the code from that page here: ', (code) => {
    rl.close();
    oAuth2Client.getToken(code, (err, token) => {
      if (err) return console.error('Error while trying to retrieve access token', err);
      oAuth2Client.setCredentials(token);
      // Store the token to disk for later program executions
      fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
        if (err) return console.error(err);
        console.log('Token stored to', TOKEN_PATH);
      });
      callback(oAuth2Client);
    });
  });
}

Testing the API Call in Node

The final function which Google provides as listMajors() is the function that will actually be used to define what information is being manipulated using the API.

ListMajors currently pulls from a predefined spreadsheet (id = ‘1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms’) that Google has set up. In this case, the code is set to pull from range A2:E and you’ll notice that the data that is returned in the response.data.values object.

The listMajors function (or whatever you change it to be named) is the callback to the original authentication function and this function will be where you house your res.render() and send data to your webpage. More on this later.

/**
 * Prints the names and majors of students in a sample spreadsheet:
 * @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
 * @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
 */
function listMajors(auth) {
  const sheets = google.sheets({version: 'v4', auth});
  sheets.spreadsheets.values.get({
    spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    range: 'Class Data!A2:E',
  }, (err, res) => {
    if (err) return console.log('The API returned an error: ' + err);
    const rows = res.data.values;
    if (rows.length) {
      console.log('Name, Major:');
      // Print columns A and E, which correspond to indices 0 and 4.
      rows.map((row) => {
        console.log(`${row[0]}, ${row[4]}`);
      });
    } else {
      console.log('No data found.');
    }
  });
}

Step 4: Run the sample

• In the command line of your node application, run node ., which will prompt you to authorize access. As you grant access, you may notice that a token.json file is created in your directory. Don’t delete this file as it is used to remember that this particular application has been authenticated.

Now, you should be able to test out whether everything is set up correctly, by running your app.js file in the terminal. When this happens, you should see the data from listMajors() print to the console as below.

Customizing the API Call

Now that you’ve set up the authentication layer in your application, it’s time to actually use the API! As mentioned, this means that you’ll be customizing the callback function which is currently listMajors().

In order to pull data, you’ll continue using the sheets.spreadsheets.values.get(), or if you are looking to pull multiple sections the function you can use sheets.spreadsheets.values.batchGet(). The documentation for both of these methods can be found here:

• spreadsheets.get
• spreadsheets.values.batchGet

Let’s start with the former.

Getting Data

For this case, you’ll only be editing a few things from the listMajors function since it’s already set up with the get() method. I’ve renamed it datapull() for clarity and made the following adjustments:

1. Changed the spreadsheet ID to my spreadsheet’s ID
2. Edited the range to pull only the cells that I want (‘tab2!A1:A10’ in this case)
3. Set up the response object which I’ve named ‘rows’
4. Passed the data from the rows object into the express file via the res.render call

app.get("/v4-get", function(req, res){

  // Authorization
  fs.readFile('credentials.json', (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    // Authorize a client with credentials, then call the Google Sheets API.
    authorize(JSON.parse(content), datapull);
  });

  // Callback function pulling data
  function datapull(auth) {

  const sheets = google.sheets({version: 'v4', auth});

  // Pulling the data from the specified spreadsheet and the specified range  
  var result = sheets.spreadsheets.values.get({
    // (1) Changed spreadsheet ID
    spreadsheetId: '1UIV4RkOx8KJK2zQYig0klH5_f8FCOdwIWV8YF2VyF8I',
    // (2) Changed the range of data being pulledd
    range: 'tab2!A1:A10',
  }, (err, response)=>{
    if (err) return console.log('The API returned an error: ' + err);

    // (3) Setting data for daily tracking
    const rows = response.data.values;

    // (4) Rendering the page and passing the rows data in
    res.render('test', {rows: rows})
  });
  }

Getting Batch Data

Should you need to pull multiple sections from a spreadsheet, you can keep most of the code the same while replacing .get() with .batchGet(), while making the following changes to the request:

1. Updated the function to be a batchGet() function
2. Updated “range” to plural “ranges”, while inputting the series of cells that you want to grab
3. Setting the output of each range to a constant
4. Organizing the output data into two arrays
5. Rendering the express file while passing through both arrays (in this example: “rows” and “data”)

app.get("/v4-batch-get", function(req, res){

  fs.readFile('credentials.json', (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    // Authorize a client with credentials, then call the Google Sheets API.
    authorize(JSON.parse(content), datapull);
  });

  function datapull(auth) {
  const sheets = google.sheets({version: 'v4', auth});

  // (1) Updating function to use batchGet()
  var result = sheets.spreadsheets.values.batchGet({
    spreadsheetId: '1UIV4RkOx8KJK2zQYig0klH5_f8FCOdwIWV8YF2VyF8I',

    // (2) Updating "range" to "ranges" and inputting the series of cells to grab within the array
    ranges: ['tab2!A2:A12', 'tab2!B2:B12', 
             'tab3!A2:A', 'tab3!B2:B']
  }, (err, resp)=>{
    if (err) return console.log('The API returned an error: ' + err);

    // (3) Pulling first two ranges into arrays - data format changes from.values to .valueRanges[range defined above].values
    const rows1 = resp.data.valueRanges[0].values;
    const rows2 = resp.data.valueRanges[1].values;

    // Pulling last two ranges into arrays- same data format as above
    const data1 = resp.data.valueRanges[2].values;
    const data2 = resp.data.valueRanges[3].values;

    // (4) Organizing the data output into two arrays: rows and data
    const rows = [rows1, rows2]
    const data = [data1, data2]

    // (5) Rendering the page and passing both the rows and data arrays through
    res.render('test', {rows: rows, data:data})
  });
  }
});

Hopefully, it’s clear that using the .get() and .batchGet() methods in v4 enable you to have much more control over the requests that you hope to make. There are many other methods that the Google API enables via v4, including the ability to write via the update() and batchUpdate() methods. With this in mind, let’s jump into the final approach for using the Google API which utilizes a third-party package.

Method 3: Node.js OAuth2 - Google Developer Console and npm google-spreadsheet Package

The final method utilizes the Google Developer Console and a third party npm package called google-spreadsheet which arguably is much cleaner than the functions outlined in the Google documentation. In order to utilize this approach, the first step is to install google-spreadsheet and to require the modules at the top of your file:

npm install google-spreadsheet

var express     = require("express"),
fs              = require('fs'),
readline        = require('readline'),
{google}        = require('googleapis'),
request         = require('request'),
GoogleSpreadsheet = require('google-spreadsheet'),
creds             = require('./client_secret.json'),
app               = express();

There are a few administrative steps in order to enable authentication through this approach.

1. Go to the Google Developers Console and navigate to the API section. You should see a dashboard.
2. Click on “Enable APIs” or “Library” which should take you to the library of services that you can connect to. Search and enable the Google Sheets API.
3. Go to Credentials and select “Create credentials”.
4. Select “Service Account” and proceed forward by creating this service account. It can be named whatever you want.
5. Under “Role”, select Project > Owner or Editor, depending on what level of access you want to grant.
6. Select JSON as the Key Type and click “Create”. This should automatically download a JSON file with your credentials.
7. Rename this credentials file as client_secret.json and copy it into your working directory.
8. The final administrative step is super important! Take the “client email” that is in your credentials file and grant access to that particular email in the sheet that you’re working in. You can do this by clicking “Share” in the top left of your spreadsheet and then pasting that email in the field, enabling with “Can edit”. If you do not do this, you will get an error when trying to pull the data.

In order to test whether the code works, we can start working with the API! Below is some example code which effectively authenticates and gets the data from the second tab of the identified spreadsheet. I've left notes identifying where the authentication is taking place, what is being requested, and where the response is being returned.

While you’re testing, make sure to take a look at the output in the console which will either log the error or the response object. If you are getting a 403 error, this means that something probably went wrong in setting up the authentication.

app.get("/google-spreadsheet", function(req, res){

  // Identifying which document we'll be accessing/reading from
  var doc = new GoogleSpreadsheet('1UIV4RkOx8KJK2zQYig0klH5_f8FCOdwIWV8YF2VyF8I');

  // Authentication
  doc.useServiceAccountAuth(creds, function (err) {

  // Getting cells back from tab #2 of the file
  doc.getCells(2, callback)

  // Callback function determining what to do with the information
  function callback(err, rows){

    // Logging the output or error, depending on how the request went
    console.log(rows)
    console.log(err)

    // Rending the test page while passing in the response data through "rows". Can access specific data points via: rows[i]._value
    res.render('test', {rows:rows})
  }
  });  
});

With this package, it becomes even easier to start writing to the spreadsheet as well. For example, perhaps you want to write to the spreadsheet daily with your progress. Here is an example of how you might do that:

app.get("/google-spreadsheet", function(req, res){

  // Identifying which document we'll be accessing/reading from
  var doc = new GoogleSpreadsheet('1UIV4RkOx8KJK2zQYig0klH5_f8FCOdwIWV8YF2VyF8I');

  // Authentication
  doc.useServiceAccountAuth(creds, function (err) {

  // Adding a row in tab #4 with the date and the number 1
  doc.addRow(4, { date: "=today()", progress: "1" }, callback)

  function callback(err) {
    if(err) {
      console.log(err);
    } else {
      console.log('You added your progress for the day.') 

      // Rendering test page
      res.render('test')
    }
  }

  });  
});

From here, you can utilize the functions within the docs of the google-spreadsheet package.

Connecting the Google Sheets Database to Express

Hopefully the last few sections were helpful in identifying different approaches that you can take to connect with, authenticate, and pull data from the Google API. The final step in utilizing the database to produce a website/application that uses this information in real time is to pass through the data to the rendered EJS file.

At this point, you should have a few routes set up which pull data that you need from the API and which pass this data through to the express file of your choice (via your res.render() call). Within the express file itself, you are able to utilize this data through EJS (embedded javascript) tags.

These tags allow you to render Javascript in-line or alongside your HTML. For example, if you were pulling from a batchGet() function which passed data through to the express file as “data” and “rows”, you can use the EJS tags to loop through your data and print it directly to the page.

Input (pulling from this file):

<% include partials/header %>

<div class="container">

    <h2>This is a tutorial for using the Google Sheets API!</h2>

    <div>This data is pulling from the second tab:</div>

    <!--Looping through the 'rows' array and printing the output within the EJS tags-->
    <% for(i=0; i<10; i++) { %>
    <div >
        Rows data: <%= rows[0][i] %>, <%= rows[1][i] %>
    </div>
    <% } %>    

    <br>
    <hr>
    <br>

    <div>This data is pulling from the third tab:</div>

    <!--Looping through the 'data' array and printing the output within the EJS tags-->
    <% for(i=0; i<10; i++) { %>
    <div >
        On <%= data[0][i] %>, I made the following progress: <%=data[1][i]%>
    </div>
    <% } %>    

</div>

<% include partials/footer %>

Output (rendering from the EJS file):

Once you set up the right database-link, you are free to style the page and add additional functionality, just like any other HTML page, since EJS is essentially HTML with Javascript embedded.

Over and Out

I hope that the information provided gives you enough of a backbone for understanding how to make requests and then actually embed requests within your Node/Express application. You’ll have to think through your individual approach for setting up the data structures you may need, but the method still remains the same.

As demonstrated, the Google API provides multiple methods for accessing data from Google Spreadsheets and I encourage you to use whichever method best suits your needs. I would also encourage you to dive into the documentation to learn more about the possible solutions available. I'm really excited to see what you create and as always, if you have any questions as you’re learning, feel free to shoot me a message!

PS: Come discuss on Twitter or subscribe to my blog.