Using Google Sheets with NodeJS

on

This guide will walk you through how to integrate the Google Sheets API in your applications, this guide focuses on using the Google-Spreadsheet library for NodeJS and includes a small example application to test with.

If you are using a different language and are integrating with the Google Sheets API then the setup process is exactly the same, just your code will be different. 

Our example at the end shows how to integrate using the Google-Spreadsheet npm module.

How to access Google Spreadsheets with a service account

There are a few steps to complete to setup your application to use Google sheets, the following guide with walk you through creating service accounts, creating keys, and setting permissions to make use if the Google Sheets API.

Enable the Google Sheets API

  1. Navigate to cloud.google.com and sign into the console with your Google account.
  2. From the Left menu, select APIs & Services, then click on Library
  3. From the search page type in sheets to find the Google Sheets API
  4. Enable the Google Sheets API on your Google Cloud Project by clicking on the "Enable" button.

Create the Private Key

You will then be taken to a page that shows information about the API usage of the Google Sheets API. 
  1. From the left menu, click on "Credentials", we will need to create some credentials to authorise our NodeJS scripts.
  2. From the credentials page we will want to create a new service account, there may already be one listed but we will want to create a new one as we need to set what permissions the service account has.
  3. Next you will want to enter a name for the service account, a unique ID which will form the service accounts email identifier, and lastly a description for the service account, you can add some notes here about what it will be used for.
  4. Then click on "Create", this will progress you to step 2 where we can grant particular permissions to the service account.
  5. For the permissions, set the service account to Project -> Editor, which will give the service account access to all the resources we require.
  6. Click on "Continue", then click on "Done" this will finish setting up the new service account.
  7. You should now see the credentials page once again with the new service account listed. Click on the pencil icon next to the service account you just created.
  8. From the bottom of the Service account properties page you will see a section for keys, we will need to create a key here that authorises our application to use the Google Sheets API. Click on "Add Key" then click on "Create new key"
  9. Ensure that JSON is selected as the format and then click "Create" a JSON file will then download containing your private key for authorising the API.
  10. A JSON file will be downloaded to your computer, this contains your private key which will be required in your code.


Share your spreadsheet with the service account

Sharing your spreadsheet with the service account we just created will give access to that account to view and modify our new spreadsheet.

  1. Create a new Google Spreadsheet at sheets.google.com and give the new spreadsheet a title.
  2. To enable the service account to access and modify your Google Spreadsheet, you will need to share the spreadsheet with the service account email address.
  3. In your Google Sheet, click on the "File" menu then click on "Share"
  4. In the sharing settings, paste in the email address of your service account, then ensure the permissions allow editing if you would like the account to be able to modify or add data. Click Send.


Create your application

We now have all the details we need to start creating an application that integrates with Google Sheets.

Depending on your development environment of choice you may need to install the Google-Spreadsheet npm module using the command:

npm i google-spreadsheet --save

The following is some sample code you may use to test this in your environment, you may also find this sample code in a sample project I have created below at codesandbox.io



Troubleshooting

If you run the code above and the code outputs one of the following errors, this indicates an issue with the way the service account has been setup in Google Cloud, or the private key, the following is a summary of these errors and what they mean.

Error: Google API error - [403] The caller does not have permission

This error will occur if you have not shared the Google Sheet with the service account email address.

Error: Google API error - [404] Requested entity was not found.

This error means that the document does not exist, check that you are using the correct Google Sheets ID

Error: error:09091064:PEM routines:PEM_read_bio_ex:bad base64 decode

This error indicates a bad request, ensure that the service account email address is correct and that the private key is correct, also ensure you are using the correct service account with the correct permissions.



Learn how to integrate a build your own scripts that leverage the Google Sheets API in our up and coming new course Webscraping with NodeJS.










Was this helpful?

Yes No


Comments

Post a Comment