Google Spreadsheets and JavaScript/Node.js

Time to read: 2 minutes

March 23, 2017
Written by
Sam Agnew
Twilion

FNrxZXmSUyaRhrplT_ksE3ANbiSkAruC7tX4VMtVv8oMsZlTqE6b0JVEkTioE4_jhdJEtCaAz6WZQxGOnbj1YXDJKi2sgSlrrDj_A6lPL-n_hbIe0aPduDB78LJY1tjrSO2vJZs

Sometimes a database might be more than what you need. Using a Google Spreadsheet can be quicker and easier if you’re building a prototype or a simple internal application.

Let’s use Theo Ephraim‘s google-spreadsheet node module to read, write and delete data from a Google Spreadsheet with just a few lines of code. You’ll need to have Node.js and npm installed.

Google Drive API and Service Accounts

You’re going to need a spreadsheet if you want to follow along with this post. Make a copy of this spreadsheet with contact information for United States legislators. Ian Webster also uses this data and Twilio to make it easy for citizens to call congress.

To programmatically access your spreadsheet, you’ll need to create a service account and OAuth2 credentials from the Google API Console. If you’ve been burned by OAuth2 before, don’t worry; service accounts are way easier to use.

Follow along with the steps and GIF below. You’ll be in and out of the console in 60 seconds.

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Drive API.
  4. Create credentials for a Web Server to access Application Data.
  5. Name the service account and grant it a Project Role of Editor.
  6. Download the JSON file.
  7. Copy the JSON file to your code directory and rename it to client_secret.json

 

There is one last required step to authorize your app, and it’s easy to miss!

Find the client_email inside client_secret.json. Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.

We’re done with the boring part! Now onto the code.

Read Data from a Spreadsheet with Node.js

Now from your code directory, you’ll need to install the Google Spreadsheet package with the following terminal command:

npm install google-spreadsheet@2.0.3

Then copy and paste the following code into a file called spreadsheet.js:

var GoogleSpreadsheet = require('google-spreadsheet');
var creds = require('./client_secret.json');

// Create a document object using the ID of the spreadsheet - obtained from its URL.
var doc = new GoogleSpreadsheet('*spreadsheet ID*');

// Authenticate with the Google Spreadsheets API.
doc.useServiceAccountAuth(creds, function (err) {

  // Get all of the rows from the spreadsheet.
  doc.getRows(1, function (err, rows) {
    console.log(rows);
  });
});

Don’t forget to replace the spreadsheet ID in that code with your own spreadsheet’s ID. The spreadsheet ID is the long key in the URL of the spreadsheet when you view it. For example, mine was 1tO3BDTA0Ix1dIj7JayA0nrX3wmh4PN7l8139Il11aK8.

Run the code with the following command to see a bunch of data from your spreadsheet printed to the console:

node spreadsheet.js

Insert, Update, and Delete from a Spreadsheet with Node

We’ve only scratched the surface of this library’s documented and comprehensive functionality.

For instance, given a reference to the rows array you had in the innermost function of the current code, you can delete a row from the spreadsheet:

rows[0].del() // this is asynchronous

And find out the total number of rows:

console.log(rows.length);

You can also insert a new row in the spreadsheet, if you want to pretend that I am a United States legislator:

doc.addRow(1, { last_name: 'Agnew', first_name: 'Samuel' }, function(err) {
  if(err) {
    console.log(err);
  }
});

Check the API reference for the full details on these functions along with a few dozen others.
Using Google Spreadsheets with Node.js opens possibilities like building an Express app with a spreadsheet as the persistence layer. But you’ve probably got even better ideas than that.

I can’t wait to see what kind of awesome projects you build. Feel free to reach out for any questions:

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.