How to use Google Spreadsheets with Node.js

Google Spredsheets and Node.js might seem like a strange combination at first sight. However, Node.js can utilize the Google Drive API via a number of NPM packages including the more famous of them and the one which we will take a look of how to use today - Simple Google Spreadsheet Access. You will be able to use this package to create, retrieve, update  and delete (CRUD) information in cells and rows as well as manage worksheets.

Step 1 Create a Google project, Enable API and add Credentials

As a start, you will first have to create a project, enable the API on it and create credentials for the API via the Google API Console. To do just that, access the Google API Console with your google account and then click on the My Project button at the top of the page.

From here, you will be able to select an existing project or create a new one. Considering this may very well be your first time accessing the console, you will have to create a new project by clicking on the appropriate button at the top right of the newly opened window.

Now you have to name your project but then you can skip the Location field and go straight for the Create button.

Once you have your very own project, you will have to enable the Google Drive API. That can be done either by selecting it immediately from the Popular APIs and services list (if still present there at the time of reading this tutorial) or by clicking on the Enable APIs and Services button.

By going with the second option, search for the API and then click on the desired result.

Once you are in the API’s dedicated page, click the Enable button.

To use the Google Drive API you will have to Create credentials for it by clicking on the presented button.

On the newly opened page you will have to select the following options for the different steps of generating the correct credentials.

• Which API are you using?
  Google Drive API
• Where will you be calling the API from?
  Web server (e.g. node.js, Tomcat)
• What data will you be accessing?
  Application data
• Are you planning to use this API with App Engine or Compute Engine?
  No, I’m not using them

To Create a service account, you will need to write and select the following options.

Service account name – a descriptive name for your service account
Service account ID – generated in conjunction with the service account name
Role – Project → Editor so you can edit the information in the spreadsheet connected to this project
Key type – JSON (the recommended and most used format for generating privacy keys)

Once you click the Continue button, your Service account and that account’s private key in a JSON format will be created for you.

Save the private key on your local computer to complete the 3rd step.


 

The last thing you will need to do to authorize your app to access a spreadsheet is to upload the JSON file to your app’s directory on the server and rename it to client_secret.json.

Afterwards, access the JSON, find the client_email and copy it. You will need to go back to your spreadsheet and click on the Share button.

With this, you will get a field to paste the client_email so you can give your project edit rights. Alternatively, you can get to the same result by copying the entire Service account ID (edit-google-spreadsheets@stellar-aurora-219218 in our case) and adding .iam.gserviceaccount.com to the end of it.

Step 2 Create, Retrieve, Update and Delete (CRUD)

Now that you have the API enabled and authorized, you can proceed with the actual managing of the Spreadsheet with Node.js. If you need to create a package.json, you can see how to do so by visiting our NPM tutorial which includes this information. Considering you already have your app set up, enter your app’s directory on the server via a command line client like PuTTy. Alternatively, you can do so by using the integrated cPanel Terminal.

Then run this command:

npm i google-spreadsheet

Now, create a new file called spreadsheet.js and paste the following code in it:

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);
  });
});

Of course, you will have to replace the *spreadsheet ID* with your own spreadsheet’s ID. The ID of each spreadsheet is the string of symbols after the https://docs.google.com/spreadsheets/d/ segment of the URL.

Now you can read the spreadsheet directly from the console by using:

node spreadsheet.js

On more regarding the functionality of the package, you can read up in its official NPM or GitHub pages. Here are just a few examples on what you can do via fairly simple commands:

Figuring out the total number of rows:

console.log(rows.length);

Inserting a new row in your spreadsheet and populating it with data:

doc.addRow(1, { date: '28', month: 'October' }, function(err) {
  if(err) {
    console.log(err);
  }
});
  if(err) {
    console.log(err)
  }
});

You can use all of this to create custom reporting tools, simple data gatherers without the need of a database, building models based on REST endpoints by utilizing frameworks such as Express and many more useful apps.