Integrating Google Sheets as REST API using googleapis

Integrating Google Sheets as REST API using googleapis

Did you know that you can use spreadsheets as a database? Actually Yes!! You can turn Google Sheets into a REST-API powered database and use it with your application.
Purva Shah

March 17, 2022 | 5 minutes read

Problem Statement

For simple applications, we don’t need complex query mechanisms. In such cases, it’s often enough to use simple and free tools like CSVs, TSVs, TXTs, or Google Sheets to store data. This comes with an additional advantage that even non-developers understand and can work with these tools, thus maintaining our app.

Except for Google Sheets, CSVs or TXTs are pretty simple and provide a local solution that is not shareable by default.

Before working with Google Sheets you might need answers to a few questions:
  • How can we use a private Google spreadsheet to store, read, update and delete data while making it securely accessible from within our Node app?
  • What about Authorization?? There won’t be any user interaction to log in. If you have ever implemented a client-side authorization using Google’s OAuth2, then you probably know how hard &painful it is to get it working 🙁

Solution

A naive approach would be to use the google developer console and create access keys for your Google account and use it to access the sheets, programmatically. But, this possess a big security/exposure risk to your account.

To save you from this here comes “Google Service Account” to your rescue. You can use service accounts for granting authorization to access google sheets. These accounts can also be shared across teams as their access can be controlled as required.

Service accounts allow your application to talk directly to Google APIs, and they become very useful when you have a backend talking directly to Google APIs.

Approach

  1. Login to your Google Account, open Google Drive, and create/populate a new Google Sheet.
  2. Enable Google Sheets API (shown below).
  3. Create Service Account (shown below)
  4. Share the spreadsheet created in step1 with the service account created in step3 (shown below)
  5. Develop a Node.js service, using googleapis, to perform CRUD operations on the google-sheet.

Let’s get started !! 🚀

  • Create a new Project on your Google Developer Console.
  • After creating the project, go to API & Services -> Search For Google Sheets API and Enable it.
google-sheet-api
  • The next step is to create a service account.
google-sheet-api-key

Go to the Credentials tab -> Create Credentials -> Select Service Account and fill in the required information to create the service account.
(You can skip role assignment)

google-sheet-api-settings
  • Go to IAM & Admin -> Service Accounts-> Click on the newly created Service Account -> Keys tab and create a new key as shown in the above image. Select the JSON option and save the file on your local machine. Let’s name it ‘keys.json’.
  • Now that we have a service account, we need to share our spreadsheet with it. It’s just like sharing a spreadsheet with any normal user account. Open the spreadsheet in your browser and click on the Share button in the top right corner. That will open a modal where you need to enter the email address of the service account. 
    (Uncheck the checkbox of Notify people since this will send an email and since the service account does not have any mailbox, it will give you a mail delivery failure notification.)
group-links

Performing CRUD Operations

Google provides API docs for sheets that you can use to identify the raw API endpoints to perform the CRUD operations.

(https://developers.google.com/sheets/api/samples/reading)

However, we can use open-sourced libraries to accelerate our development process. Let’s use “googleapis”, Google’s officially supported Node.js client library, for accessing Google APIs. The advantages of using googleapis module, along with the Sheets v4 API are as follows:-

  • Faster responses
  • More features
  • Uses JSON instead of XML
  • async/await support.

Application set up

Install googleapis library as mentioned in the official documentation (https://www.npmjs.com/package/googleapis) using the following command.

npm install googleapis

Let’s Start Coding !!

Some points to note before we start:-

  • Spreadsheet Id: When you have the sheet open in your browser, the URL looks something like this: https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXX/edit#gid=0. Here, XXXXXXXXXXXXXXXXXXXXXX is your spreadsheet Id which will be different for each spreadsheet file.
  • Scopes: The scopes can be found in the official documentation here https://developers.google.com/identity/protocols/oauth2/scopes. As we want to read and edit the sheet we have used scopes as “https://www.googleapis.com/auth/spreadsheets“.
  • Spreadsheet Name: Every spreadsheet file has multiple sheets and each sheet is identified by the sheet name. In our example, we are using the sheet name as “Data”.

Reading data from google sheets

“Data!A1:E5″ means- from the sheet ‘Data’, target rows from 1-5 and columns from A-E &“Data!A3” means all columns of row 3. This can be used in either read or write operations.

The following code will help to access all records from the sheets

Writing/updating data into Google Sheets

The following code will make an entry to row 6 of the sheet

Output can be seen as follows:-

excelsheet
Deleting data from google sheets

The following code will delete row 6 from the sheet

Conclusion

  1. Google sheets are not a complete solution for the database management of your business. But, it works wonders for small projects and POCs to carry out operations effectively without holding on to any technical knowledge, like SQL.
  2. Connectivity: Google Sheets are available over the internet and allow you to access them anytime anywhere. For UI access you need to be logged in to your Google account.
  3. Permission or Access Control: Using Google Sheets you can control access to your data by adding or revoking user access with just a few clicks.
  4. Pricing: Google sheets are free of charge, as of now :p

Happy Learning!!

Tech Prescient

Become a part of our big family to inspire and get
inspired by professional experts.

Join Tech Prescient Team