Transform your Google Sheets data into powerful analytics with Amazon QuickSight

What's New / Blog Blog
1

Amazon QuickSight is a cloud-based, serverless business intelligence (BI) service that you can use to build visualizations, perform analysis, and gain insights through machine learning (ML) capabilities such as anomaly detection, forecasting, and natural language querying. QuickSight uses its robust Super-fast, Parallel, In-memory Calculation Engine (SPICE) to rapidly perform advanced calculations and deliver visuals.

Google Sheets is a cloud-based spreadsheet application that you can use to create, edit, and collaborate on data in real time. It supports a wide range of functions and formulas that makes it well-suited as a data source for BI and analytics tasks. With Google Sheets, you can organize, analyze, and share insights quickly and efficiently. Its accessibility and collaboration features make it a versatile platform for teams to work together on data-driven decision-making.

QuickSight now integrates with Google Sheets as a new data source. This post guides administrators through the setup process and explains how to configure Google Sheets as a data source for BI workflows in QuickSight.

Solution overview

Admins must complete a one-time set up when they enable their Google Sheets data source. In this post, we demonstrate how to enable AWS Secrets Manager to store user credentials needed to connect with Google Sheets.

Prerequisites

Make sure you have the following:

Enable AWS SecretsManager Write permissions

As part of the new Google Sheets connector launch, we are introducing a new security and permission called AWS SecretsManager Write to store credentials. Upon creating a new Google Sheet connection, all OAuth tokens are securely stored in Secrets Manager within your AWS account. This setup creates a new secret for each data source, so credentials are customer-owned and never stored in the QuickSight service. To select the new AWS SecretsManager Write permission, you can choose from two scenarios based on the other permission: AWS SecretsManager.

Scenario 1: Your QuickSight account doesn’t have AWS SecretsManager permissions previously selected

If you don’t have AWS SecretsManager permissions previously selected, you simply need to select the new AWS SecretsManager Write permission for Google Sheets. QuickSight administrators can follow these steps:

  1. On the QuickSight console, choose your user profile, then choose Manage QuickSight.
  2. Choose Security & Permissions in the navigation pane.
  3. Choose Manage under Security & Permissions for access to AWS services.
  4. Select AWS SecretsManager Write and choose Save.

652×839 121 KB

Your admin configuration is now complete.

Scenario 2: Your QuickSight account has AWS SecretsManager permissions previously selected

If your QuickSight account already has Secrets Manager enabled and you want to enable the AWS SecretsManager Write permission, an IAM trust relationship update is required before you can enable AWS SecretsManager Write. The trust relationship update needs to be applied to the QuickSight Secrets Manager service role (aws-quicksight-secretsmanager-role-v0). This is necessary to allow the new connector to access and manage secrets securely under the updated security and permissions model.

AWS account administrators must use the following IAM trust relationship to enable proper access for QuickSight to use AWS SecretsManager Write. Make sure to replace with your account ID.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "quicksight.amazonaws.com"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "ArnEquals": {
                    "aws:SourceArn": "arn:aws:quicksight:*::account"
                }
            }
        },
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "quicksight.amazonaws.com"
            },
            "Action": [
                "sts:AssumeRole",
                "sts:TagSession"
            ],
            "Condition": {
                "ArnLike": {
                    "aws:SourceArn": "arn:*:quicksight:*:*:datasource/*"
                }
            }
        }
    ]
}

After you update the trust relationship, return to the QuickSight Security & Permissions page to select AWS SecretsManager Write permission as described in the previous section.

If you’re using bring-your-own-role (BYOR), attach the preceding trust policy to the role and attach the AWSQuickSightSecretsManagerWriteAccess managed policy to the role.

Create a Google Sheets data source for QuickSight

After you enable the AWS SecretsManager Write permission, complete the following steps:

  1. On the QuickSight console, choose Datasets in the navigation pane.
  2. Choose New dataset.
  3. For the data source, choose Google Sheets.

1429×637 108 KB

  1. Enter the data source name and choose Connect.

790×268 18.5 KB

You will be redirected to the Google sign-in page, which authorizes AWS to connect to your Google account.

  1. Enter your Google account details and choose Next.

790×680 75.7 KB

  1. Review and apply the permissions and choose Continue.

The result displays all the folders, subfolders, and sheets from your Google account in each level.

  1. To display the tables, choose a sheet.

883×784 64.5 KB

  1. From the list of tables, choose your table.

880×713 38.6 KB

  1. Choose Edit/Preview to edit the data.
  2. Choose Add data to add additional tables.
  3. After you have prepared your data, choose Publish & Visualize.

Additional considerations for admins

In this section, we discuss some additional considerations for admins.

Customer managed key implications

If you have registered a customer managed key (CMK) with QuickSight, we use your CMK for the following use cases:

  • Encrypting data source secrets in the customer’s AWS account – Access depends on the type of role:
    • For a QuickSight service managed role, we manage the grant lifecycle. We create two account-level grants during UpdateKeyRegistration (one for the QuickSight service principal and one for the Secrets Manager service role in the customer’s account if the customer has enabled AWS SecretsManager Write permission). We retire both grants during QuickSight account unsubscription. If you manually delete the QuickSight Secrets Manager service role outside of QuickSight, you must manually clean up any associated grants. To do this, you must call ListGrants on your CMK and then filter through the grants to find the grant name with the prefix QuickSightSecretsManager. You can then explicitly call RevokeGrant on this grant.
    • For customer-provided roles (BYOR), customers must manage key access themselves by either making two-way policy changes (key and IAM role policy) or creating temporary grants to give BYOR access to the CMK.
  • Encrypting temporary staging data during Google Sheets using SPICE ingestion – We create ingestion job-level grants at the beginning of Google Sheets ingestion and immediately retire these grants after the ingestion is complete (completion includes successful, canceled, and failed ingestions). Although these grants will not be annotated with encryption context, the grant name will look like QuickSightIngestion-{INGESTION_ID}.

AWS CloudTrail callouts

Certain Google Sheets actions in QuickSight will trigger AWS CloudTrail events in customer accounts. For Secrets Manager or AWS Key Management Service (AWS KMS) events, the userIdentity in the CloudTrail event will display glue.amazonaws.com in the invokedBy field instead of quicksight.amazonaws.com. Similarly, sourceIpAddress and userAgent fields will also display glue.amazonaws.com. This is an expected behavior.

The following specific Secrets Manager or AWS KMS events will show Glue in the CloudTrail event:

  • Data source creation:
    • Secrets Manager secret creation event
    • Secrets Manager PutResourcePolicy event on the secret
    • AWS KMS GenerateDataKey event to encrypt the secret
  • Data ingestion:
    • AWS KMS GenerateDataKey events to encrypt staging data
    • AWS KMS Decrypt event on staging data

Conclusion

In this post, we walked you through the permissions required to import your Google Sheets data into QuickSight and build a simple dashboard.

If you haven’t already, your next step is to create a QuickSight account with Enterprise Edition. We only reviewed the basic QuickSight functionalities in this post. For additional discussion and help getting answers to advanced QuickSight topics, check out the QuickSight Community.

About the authors

100×135 19.7 KB
Ashok Dasineni is a Solutions Architect for Amazon QuickSight. Before joining AWS, Ashok worked with clients and organizations in Banking and financial domain, focusing on fraud research and prevention. He designed and implemented innovative solutions to improve business process, reduce cost and increase revenue, enabling companies around the world to achieve their highest potential through data.

100×129 3.88 KB
Bhavesh Shah is a Software Development Engineer II on the Amazon QuickSight team, working in Data Connectivity and Ingestion domain. His work spans designing and delivering large-scale ETL workflows, implementing customer-managed key encryption solutions, and enabling seamless integrations in OAuth space. Bhavesh was one of the lead engineers behind the QuickSight–Google Sheets integration, enabling customers to ingest their Sheets data into QuickSight SPICE for analysis. Outside of work, he enjoys long runs, learning new songs on his piano, and experimenting in the kitchen.

100×130 4.56 KB
Sriramkumar Thamizharasan is a Software Development Engineer II on the Amazon QuickSight Data Connectivity team with over three years of experience. He works on developing and improving data connectors, building secure and private connectivity, and enhancing the user experience. Sriram was one of the lead engineers behind the QuickSight Google Sheets integration, helping customers connect and explore their Sheets data in QuickSight. In his free time, he enjoys cooking, gaming, and solving puzzles.

100×135 20.5 KB
Vignessh Baskaran is a Sr. Technical Product Manager owning Data Connectivity and Data Preparation domains in Amazon QuickSight. He has 9+ years of experience in developing large scale data and analytics solutions. Prior to this role, he worked as a Sr. Analytics Lead in AWS building comprehensive BI solutions using QuickSight which were globally adopted across AWS Worldwide Specialist Sales teams. Outside of work, he enjoys watching Cricket, playing Racquetball and exploring different cuisines in Seattle.

This is a companion discussion topic for the original entry at https://aws.amazon.com/blogs/business-intelligence/transform-your-google-sheets-data-into-powerful-analytics-with-amazon-quicksight/