To test if the sheets api works fine, open a new google sheet under the same account that you used to create the token and copy the spreadsheetId from the URL. So if the URL is of the following form, everything after /d/ before next slash is the spreadsheet identifier.

https://docs.google.com/spreadsheets/d/<this is the id>/edit#gid=0

Refer and replace the spreadsheetUrl and spreadsheetId in config.json file in the codebase. Note, that the first sheet name in the workbook is assumed as Sheet1

Now, run node googleapi.js and you will be asked to a copy a URL and open it on the browser. Do it and you will download a file called token.json at the end of OAuth confirmation and if you check the spreadsheet you will see the following contents on the file:

Connecting to Database

Checkout the contents of pgClient.js file. You can either pass the database connection params as environment variables (which is needed when deploying to AWS lambda) and fallback to literals for testing in local database.

pgClient.js

The script assumes that you have a Postgres server running on port 5432 on your local machine and has a database called customers

Let’s check the configuration in config.json

As discussed before, we have already copied the Spreadsheet URL to the config, Notice, the tables array. You can specify the tables that you would like to import and the corresponding sheet name.

Testing the lambda function

We can invoke the lambda function locally to make sure everything is working fine. Run the following command

$ SLS_DEBUG=* serverless invoke local --function lambda_sheets

The SLS_DEBUG param will print additional debugging information to the console in case something goes wrong. If all is good, you see something like this below where the script prints the number of rows imported.

Running the lambda function locally

Now, go check the spreadsheet and you should see all the data from the customer_details table in Sheet1 So, this means every time the lambda function is invoked, it will clear the contents of the sheet and replace it with the data from the table in the Postgres database.

Deploying the Lambda function

The Serverless config shared with you assumes that you are deploying this function to AWS Lambda. Let’s walk-through the steps to deploy the function to production.

Before we deploy, the function let’s discuss additional parameters in serverless.yml file that are relevant. Other parts of the yml file have been skipped for brevity.

Lamda function config in serverless.yml

The timeout param (in milliseconds) is the maximum execution time and the scheduler will ensure this function is called every 1 hour.

To deploy the function to AWS, make sure aws_access_key_id , aws_secret_access_key and region are passed as environment vars to deploy command or configured in ~/.aws/config

$ serverless deploy

The above command will create a new AWS Lambda function and invoke it every 1 hour. Make sure the database config is populated as environment variables as needed from lambda console.

The complete source code is available here for your reference. Thanks for reading, and please feel free to comment and ask me anything! Cheers 👍