Updated on Mar 13, 2019
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.
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.
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.
Make sure you don’t delete this as it is the only available copy!
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
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.
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) } });
The row-based API also assumes there are no empty rows in your sheet. If any row is completely empty, you will not be able to access any rows after the empty row using the row-based API.
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.