How to Connect Excel to Google Data Studio Easily?

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.

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?

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 program that allows you to organize 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, organize, and analyze 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?

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.

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

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.

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

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.

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.

Frequently Asked Questions