How to make Google Sheets as QuickSight Data Source
Tags: aws, quicksight, googlesheet
Published: 2022-07-17 17:56:29

There are cases where you want to connect [AWS QuickSight](https://aws.amazon.com/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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hekekv8s5pzvu627exq6.jpg)

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

## Steps:
1. Navigate to Apps Script on the Google Spreadsheet.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/spnjsjnp4nqjl5k8pmmu.png)
2. Copy all the code that was given in this repo, you can see the final configuration below.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8olz2mez13fdscbg2tc.png)
3. Navigate to the project settings.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/icp3jzv1teu9nth5g7tb.png)
4. Add the AccessKeyId, Bucket Name, and Secret AccessKey to the script properties. This will protect your secrets for security reason.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ldzoprn3adyph4j6wduo.png)
5. Navigate to Triggers
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ehlqv486l3014bikjd9t.png)
6. 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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r0e4q4jbkr37icodb1da.png)
7. It will export every worksheet on the workbook into a different csv file on the bucket.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1im9xw7edjuyr4byvqzw.png)
8. In Quicksight we can make our S3 Bucket as our data source.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fxmovrh7ajh9d6s82j2z.png)
9. This is how our manifest.json file looks like and needs to be uploaded.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o9w05abfsychgkazgd2w.png)
10. We can also preview the data here.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c9p06p7xhy193vce4ej1.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ocji31paqmxeh4cphooe.png)
11. We also schedule a job to refresh our dataset hourly.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/29cd9bvyew7b56nuf9su.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h00mh5r4hlf8tipjbqcg.png)


## 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s7dc52eb3lffwjo7olo3.png)

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:
1. Setup Service Account in GCP.
2. The source code is available here: https://github.com/rweebs/quicksights-gsheet-demo-/tree/master/s3_to_gsheet

## Steps:
1. Share your spreadsheet with the service account email.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hver9ond2c7n3275u2e5.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c9rzlwwkwcu3gxpw3baf.png)

3. Add the service account private key to key.json file on lambda_function directory
4. Zip the lambda_function directory
5. Upload the zip to lambda and Add the spreadsheetId that you copy to the environment variable
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tufyzja03fsv1nsz0uph.png)
6. Make sure the directory looks like this with lambda_function.py in the root directory
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mxh7bzuarownirtx9rls.png)
7. Add Trigger to the Lambda from specific s3 bucket and don't forget to set the object format .csv
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sqe6grs49mqadvxaxoka.png)

8. 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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qy53kqz8ra4unf6k6b83.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zuncukbb7uvqztjd7hgt.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yhsejc05d3n4qsmbsvu0.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/199glwydszqxpslicep1.png)

## 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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3884gnj6exq382zkv9a3.png)


## 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!