How to make Google Sheets as QuickSight Data Source

rweebs — Sun, 17 Jul 2022 17:56:29 +0000

There are cases where you want to connect AWS QuickSight to pull data from Google Sheets to suit your business. In fact many business people love to see data in a spreadsheet instead of having to query database. But, as of today AWS QuickSight doesn't have a Google Sheet connector out of the box. To be able to use Google Sheet as data source we need to make a script to export the data to csv and put it in the S3.

In this post, I will demonstrate to you how to use Google Sheets as Data Source for QuickSight.

Program overview

Prerequisites:

Setup AWS AccessKey and SecretKey that enable access to read and write to specific S3 Bucket.
The source code is available here : https://github.com/rweebs/quicksights-gsheet-demo-/tree/master/gsheet_to_quicksights

Steps:

Navigate to Apps Script on the Google Spreadsheet.
Copy all the code that was given in this repo, you can see the final configuration below.
Navigate to the project settings.
Add the AccessKeyId, Bucket Name, and Secret AccessKey to the script properties. This will protect your secrets for security reason.
Navigate to Triggers
This is the trigger implementation you can make the program to sync on the given time, such as sync every minutes or with other configuration.
It will export every worksheet on the workbook into a different csv file on the bucket.
In Quicksight we can make our S3 Bucket as our data source.
This is how our manifest.json file looks like and needs to be uploaded.
We can also preview the data here.
We also schedule a job to refresh our dataset hourly.

How to export the data from QuickSight to Google Sheet?

So as of today the only data that can be exported out of QuickSight is only for Visualization data.

So when we already download the csv file. We can upload the file to s3 and make a lambda function to make a new worksheet with the title of the csv file to the Google Sheet.

Prerequisites:

Setup Service Account in GCP.
The source code is available here: https://github.com/rweebs/quicksights-gsheet-demo-/tree/master/s3_to_gsheet

Steps:

Share your spreadsheet with the service account email.

Copy the spreadsheetId the Id is the one that I select for example

Add the service account private key to key.json file on lambda_function directory
Zip the lambda_function directory
Upload the zip to lambda and Add the spreadsheetId that you copy to the environment variable
Make sure the directory looks like this with lambda_function.py in the root directory
Add Trigger to the Lambda from specific s3 bucket and don't forget to set the object format .csv
The flow of the program looks like this:
When there is a object create event, it will invoke the lambda function to make a new worksheet with the title of the filename of the csv file in this example address, if there is address workbook available it will clear all the cell within the sheet and replace it with the new one as seen at the last image

PS. Just make sure in the configuration runtime at minimum 30 seconds instead of 3 seconds. And make sure the lambda role has access to List and Get Object at the bucket

Conclusion

We can use Google Sheet as the data source for QuickSight with some effort to make it automated. We can also use for example lambda and Cloudwatch to make a cronjob for exporting csv file. Also use Athena to query all the csv file on S3. Until the feature is available to make Google Sheet as data source out of the box. This is one of the option we can do.

Otherwise, hopefully this did work for you and you can now happly using Google Sheet as data source in Quicksight!

DEV Community: rweebs

How to make Google Sheets as QuickSight Data Source

Program overview

Prerequisites:

Steps:

How to export the data from QuickSight to Google Sheet?

Prerequisites:

Steps:

PS. Just make sure in the configuration runtime at minimum 30 seconds instead of 3 seconds. And make sure the lambda role has access to List and Get Object at the bucket

Conclusion