Except for Google Sheets, CSVs or TXTs are pretty simple and provide a local solution that is not shareable by default.
- 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 🙁
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.
- Login to your Google Account, open Google Drive, and create/populate a new Google Sheet.
- Enable Google Sheets API (shown below).
- Create Service Account (shown below)
- Share the spreadsheet created in step1 with the service account created in step3 (shown below)
- 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.
- The next step is to create a service account.
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)
- 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.)
Performing CRUD Operations
Google provides API docs for sheets that you can use to identify the raw API endpoints to perform the CRUD operations.
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:-
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:-
Deleting data from google sheets
The following code will delete row 6 from the sheet
- 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.
- 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.
- 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.
- Pricing: Google sheets are free of charge, as of now :p