Are you trying to import your data from Excel to Google Data Studio for visualization? If yes, then you have landed in the right place. Data Studio is a business intelligence tool offered by Google as a completely managed cloud-based service. It is offered free of cost with the Google Cloud Platform. Data Studio enables users to create custom dashboards and reports based on several data sources. Its deep integration with Google software stack and support for most of the data sources natively or through partners make it very popular among businesses.

Google Data Studio inherits the intuitive access control and sharing model that is typical to other Google suite products and makes it easy for professionals to share and collaborate based on the created artifacts. Since Google Data Studio is built on the Google app ecosystem, its de facto choice for spreadsheet processing is the Google Sheets application. But, Microsoft Excel is the original business intelligence tool, and most organizations still prefer it for their day-to-day work. It is a common need to import data from Excel to Google Data Studio for visualization. In this post, you will learn different ways to import Excel to Google Data Studio.

Table of Contents

Prerequisites

  • A Google account with access to Google Data Studio.
  • A Python environment.
  • Basic understanding of Databases and Business Intelligence tools.

What is Microsoft Excel?

excel to google data studio- Microsoft Excel Logo
Image Source

Microsoft Excel is a spreadsheet tool for organising and recording data in a tabular style that was created by Microsoft. Microsoft Excel, which was first released in 1985, is a spreadsheet programme that allows you to organise and modify data using formulas in columns and rows. MS Excel has a lot of features that allow you to do things like computations, pivot tables, graph tools, and so on.

MS Excel is part of the Microsoft Office Suite and is compatible with Microsoft Windows, macOS, Android, and iOS. Firms usually use it to keep track of, organise, and analyse financial data. VBA programming is another feature of MS Excel that can help you automate monotonous operations.

Key Features of Microsoft Excel

  • Conditional Formatting: MS Excel allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula.
  • Built-in Formulae: MS Excel provides you with basic and advanced built-in formulae for sum, average, minimum, etc. You can use formula auditing to graph or trace the relationship between cells and formulas with blue arrows. You can track precedents (cells that provide data for a particular cell) or the cells that depend on the value of a particular cell. 
  • Data Protection: Excel spreadsheets can be password protected on your laptop or PC. MS Excel also provides a robust system to recover data.
  • Data Sorting & Filtering: You can easily perform the basic data sorting and filtering operation on your data in excel. MS Excel also provides Advanced filtering options for complex criteria.

What is Google Data Studio?

Excel to Google Data Studio - Google Data Studio Logo
Image Source

GDS (Google Data Studio) is a free data visualisation tool that lets you create stunning interactive dashboards and custom reports. Business teams can easily transform raw data into relevant visualisations that help them make strategic decisions using our Business Intelligence tool. You can simply obtain data from up to 12 different sources, including Google Analytics, and combine it into one customizable and interactive report as part of the Google Ecosystem.

Key Features of Google Data Studio

Since its initial release in 2016, Google Data Studio has become one of the popular Business Intelligence platforms due to the following features:

  • Report Sharing: To streamline the communication between various business teams, Google Data Studio allows you to access and share your reports via Link sharing, Email permissions & scheduled email deliveries.
  • Real-Time Data: Instead of manually exporting & importing data, you can automate your reports to display graphs & charts based on real-time data.
  • Easy Integrations: Seamlessly connect to Multiple data sources such as Google Ads, Google Analytics, Google Search Console, Sheets, etc all within a single dashboard.
  • Data Customization: Google Data Studio’s filter tools allow you to organize and refine your data by applying filters such as date range, account, campaign, location, etc. You can also apply these filters at the report level, page level, or chart level. Similar to an excel spreadsheet, you can create calculated fields based on formulas and conditions.

Replicate Data in Minutes Using Hevo’s No-Code Data Pipeline

Hevo Data, a Fully-managed Data Pipeline platform, can help you automate, simplify & enrich your data replication process in a few clicks. With Hevo’s wide variety of connectors and blazing-fast Data Pipelines, you can extract & load data from 100+ Data Sources straight into your Data Warehouse or any Databases. To further streamline and prepare your data for analysis, you can process and enrich raw granular data using Hevo’s robust & built-in Transformation Layer without writing a single line of code!

GET STARTED WITH HEVO FOR FREE

Hevo is the fastest, easiest, and most reliable data replication platform that will save your engineering bandwidth and time multifold. Try our 14-day full access free trial today to experience an entirely automated hassle-free Data Replication!

Importing Excel to Google Data Studio Using Google Sheets

Creating a Google Sheet from Excel is an easy task as you have to upload the Excel to Google drive and then open it as a Google Sheet. But, this method may not be a good fit in an ETL process where everything is automated. In usual cases, Excel will be the last step of data processing, you would then need it directly available in Google Data Studio for your management to make decisions. In such cases, we need a programmatic method of accomplishing it. Here, you will use a python utility called py-drive, to automatically upload your Excel sheet as Google Sheet in Excel to Google Data Studio Integration.

  • Step 1: The first step in Excel to Google Data Studio is to go to Google Developer Console and generate a client secret to be used for building the application. Head to the credentials section and click “Create Credential”.
excel to google data studio- Credentials Section
Image Source

Once generated, download the keys and rename it to “client_secrets.json” and put it in the folder where you will create the python script. 

  • Step 2: The next step in Excel to Google Data Studio is to install a library called py-drive in your python environment.
pip install PyDrive

The library will help you to convert your Excel file to a Google Sheet and upload it to the defined Google account in a few lines of code.

  • Step 3: The next step in Excel to Google Data Studio is building the authentication layer – Google uses OAuth for establishing identity and authentication. It means the user will need to use a web browser to authenticate the use of API. After the first time, the tokens can be saved in a file and used for subsequent requests. Use the below snippet to accomplish it.
from pydrive.auth import GoogleAuth 
       from pydrive.drive import GoogleDrive
       gauth = GoogleAuth() 
       gauth.LoadCredentialsFile("goog_creds.txt")
       gauth.LoadCredentialsFile("goog_creds.txt") 
       if gauth.credentials is None:
                gauth.LocalWebserverAuth() 
       elif gauth.access_token_expired: 
                gauth.Refresh() 
       else: # Initialize the saved creds 
     gauth.Authorize()

       gauth.SaveCredentialsFile("goog_creds.txt")

The above snippet checks for credentials in the file “goog_creds.txt”. If it doesn’t find the file, it redirects to a web browser and saves the tokens in a file when the user authenticates using his google account. It is a one time process, as from the second time onwards, you will use the credentials saved in the file.

  • Step 4: The next step in Excel to Google Data Studio is to use the below snippet of code to add the Excel file to Google Sheet.
 src_file = drive.CreateFile({'title': source.xlsx'}) 
          src_file.SetContentFile('source.xlsx')
          src_file.Upload({'convert': True})

This will upload the Excel file and create a Google Sheet. Now, let’s upload it to Google Data Studio for further analysis in Excel to Google Data Studio Integration.

  • Step 5: The next step in Excel to Google Data Studio is go to Google Data Studio and click “Reports” as shown in the below screenshot.
excel to google data studio- Select Reports
Image Source
  • Step 6: The next step in Excel to Google Data Studio is to select “Google Sheets” as a data source and click on the sheet which you have created in the above steps.
excel to google data studio- Select Google Sheets as Data Source
Image Source

That concludes the steps to add Excel to Google Data Studio. This method has automated the process of importing to an extent. Your changes will get updated in the reports, as per the schedule that you have created in Google Data Studio.

Download the Guide on How to Set Up a Data Analytics Stack
Download the Guide on How to Set Up a Data Analytics Stack
Download the Guide on How to Set Up a Data Analytics Stack
Learn how to build a self-service data analytics stack for your use case.

What makes Hevo’s ETL Process Best-In-Class

Providing a high-quality ETL solution can be a difficult task if you have a large volume of data. Hevo’s automated, No-code platform empowers you with everything you need to have for a smooth data replication experience.

Check out what makes Hevo amazing:

  • Fully Managed: Hevo requires no management and maintenance as it is a fully automated platform.
  • Data Transformation: Hevo provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Faster Insight Generation: Hevo offers near real-time data replication so you have access to real-time insight generation and faster decision making. 
  • Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (with 40+ free sources) that can help you scale your data infrastructure as required.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-day free trial!

Limitations of Using Google Sheets

The above method of Excel to Google Data Studio Integration is not without its drawbacks. Let us spend some time on some of the challenges that may play spoilsport while implementing it.

  • The method only handles the first time load of Excel to Google Data Studio Integration. In reality, you will need additional logic to overwrite the file when your Excel updates.
  • This method requires you to develop custom code for Excel to Google Data Studio Integration, and taking this to production will need further expertise.
  • Usually, such extracts will need to be transformed into a usable form before creating reports in Excel to Google Data Studio Integration. As complex transformations are not a strong point for Data Studio, you will need additional logic to implement the transforms, before the load itself in Excel to Google Data Studio Integration.

Conclusion

In this blog, you have learned about two different methods for importing Excel to Google Data Studio. You have to import your data in Google Sheet to visualize it in Google Data Studio.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

mm
Former Director of Product Management, Hevo Data

Vivek Sinha has extensive experience in real-time analytics and cloud-native technologies. With a focus on Apache Pinot, he was a driving force in shaping innovation and defensible differentiators, including enhanced query processing, data mutability support, and cost-effective tiered storage solutions at Hevo. He also demonstrates a passion for exploring and implementing innovative trends within the dynamic data industry landscape.

No-code Data Pipeline for Your Data Warehouse

Get Started with Hevo