Connecting Google Sheets to Amazon QuickSight

Overview

In today’s data-driven business environment, many organizations rely on tools like Google Sheets to manage and store key data. However, turning that data into actionable insights often requires more advanced visualization tools. While Amazon QuickSight is a powerful data analysis and visualization platform, it doesn’t offer a direct, native connection to Google Sheets. Fortunately, there are several effective methods to overcome this limitation. This blog will explore practical solutions to integrate your Google Sheets data with Amazon QuickSight, enabling richer insights and more informed decision-making.

Introduction

By leveraging Google Cloud’s API, Amazon S3, and AWS Lambda, you can automate the process of fetching data and storing it in Amazon S3, ready for visualization in Amazon QuickSight.

Architecture Diagram

Method to connect Google Sheets to Amazon QuickSight

Step 1: Create a Google Cloud Project and Enable Google Sheets API.

• Go to the “https://console.cloud.google.com/apis/dashboard”
• Create a new project or select an existing one.
• Navigate to the “Library” and search for “Google Drive” or “Google Sheets” APIs.
• Enable the API for your project.
• Click on “Create Credentials” and fill in the necessary details.
• A Client ID will be generated; you can either copy it or download it for later use.

Step 2: Set Up an Amazon S3 Bucket and Deploy AWS Lambda Function

• Create an Amazon S3 bucket in the specified AWS region.
• Write and deploy an AWS Lambda function that fetches the Google Sheets data (in CSV format) and stores it in the Amazon S3 bucket you’ve created.

Step 3: Create a Manifest JSON File

• Create a manifest.json file that includes all necessary details about the file, such as its location in Amazon S3, delimiter type, file type, etc.

Eg: manifest.json

Step 4: Upload the Manifest File

• Upload the manifest.json file to the same Amazon S3 bucket where the CSV file is stored.

Step 5: Connect the Amazon S3 Bucket to Amazon QuickSight

• In Amazon QuickSight, connect to the Amazon S3 bucket containing the CSV and manifest files.
• Once the connection is established, you can build visualizations based on the data.

Visualization in Amazon QuickSight

Once your data from Google Sheets is successfully loaded into Amazon QuickSight via the Amazon S3 bucket, it’s time to turn that data into meaningful visualizations and dashboards. Amazon QuickSight provides various chart types, filtering options, and customization features to help you analyze and present your data effectively. Below are some key features to enhance your dashboards:

• URL Actions: You can add URL actions to navigate to external dashboards or websites directly from your Amazon QuickSight dashboard. This is useful when linking to external reports, documentation, or related resources.
• Navigation Actions: Amazon QuickSight allows seamless navigation between different sheets within the same dashboard. You can set up actions that help users easily switch views or explore different aspects of the data.
• Cross-Sheet Filters: Amazon QuickSight enables the application of filters across multiple sheets, allowing users to synchronize data across different views. This feature ensures that when a filter is applied, it affects all relevant visualizations and sheets.
• Custom Sheet Selection for Filters: While applying filters, you can use the custom sheet selection feature to include or exclude specific sheets based on your needs. This allows for better control over the filtered data in each view.
• Filter Application: Filters can be applied at different levels within your dashboard:
• Single Visual: Apply a filter to only one specific visualization.
• Single Sheet: Filter data at the sheet level to affect all visuals within that sheet.
• Cross-Sheet: Apply a filter across multiple sheets to ensure consistency in data representation across different views.

Email Automation in Amazon QuickSight Dashboards

One of the most powerful features of Amazon QuickSight is the ability to automate email reports directly from your dashboards. This feature lets you regularly send data insights and visualizations to stakeholders without manual intervention, keeping everyone updated with the latest data. Here’s how you can set up and manage email automation for your Amazon QuickSight dashboard:

Enabling Email Reports

Step 1: Once your dashboard is ready, navigate to the “Dashboard” tab in QuickSight.

Step 2: Click on “Scheduling”.

Step 3: Configure the subscription by selecting the recipients (individual users or groups) and the frequency of the email reports (daily, weekly, or monthly).

Step 4: Customize the message, subject, and delivery time if needed.

Note: Ensure that both the recipient and the sender have active subscriptions to the dashboard. The automated email will not be sent if either party does not have a subscription.

Email Reporting Limitations:

• Attachment Format: Only single sheet PDF is supported for email automation.
• Frequency of Reports: Reports can be scheduled daily, weekly, or monthly, but not hourly.
• Subscriptions: Recipients must have an Amazon QuickSight subscription.
• Member Limit: Scheduled emails cannot exceed 5,000 recipients.

Fig: Dashboard with Scheduling the Email

Limitations of Amazon QuickSight

Limited Native Connectors: Amazon QuickSight has fewer native data source connectors than some BI tools, requiring workarounds for certain integrations.

SPICE Limitations:

• Standard Edition: Up to 25 million rows or 25 GB per dataset.
• Enterprise Edition: Up to 1 billion rows or 1 TB per dataset

Lack of Real-Time Streaming: QuickSight does not support real-time data streaming, relying instead on scheduled data refreshes.

Limited Customization: Visualization customization options are more restricted than those of other BI platforms like Tableau or Power BI.

Number of Sheets: Max of 20 sheets per dashboard. It cannot be increased.

Number of Visuals per Dashboard: Max of 50 visuals per sheet. It cannot be increased.

Conclusion

While Amazon QuickSight lacks a native connection to Google Sheets, using AWS services like AWS Lambda and Amazon S3 offers a practical workaround to visualize your data. Once connected, Amazon QuickSight’s robust visualization tools, email automation, and row-level security help transform your data into actionable insights. Amazon QuickSight remains a scalable, cost-effective data analysis and decision-making solution despite some limitations, such as limited connectors and customization.

Drop a query if you have any questions regarding Amazon QuickSight and we will get back to you quickly.

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, Microsoft Gold Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner,  AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon ECS Service Delivery Partner, AWS Glue Service Delivery Partner, Amazon Redshift Service Delivery Partner, AWS Control Tower Service Delivery Partner, AWS WAF Service Delivery Partner, Amazon CloudFront and many more.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.