Google Data Studio is a powerful visual reporting tool that allows you to transform a sea of raw data into engaging and interactive dashboards and charts. The user-friendly pre-built data connectors mean you don’t need any programming knowledge to pull data from multiple different sources. And combined with Google’s sharing functions, teams can easily combine, filter and present their data together in a really professional format, with great graphics.
As you’d expect from a piece of Google software, Google Data Studio is designed to integrate seamlessly with data sources such as Google Ads, Google Analytics, BigQuery. On top of that, there are over 150 third-party connectors to fetch data from sources like Facebook, Ebay, LinkedIn and Mailchimp. If you want to use spreadsheets as your data source, you need to use Google Sheets.
Got source data in Excel? No worries. Read on to find out how to create an automated system to import your data seamlessly from Excel to Google Sheets to Google Data Studio.
Step 1: Use Sheetgo to connect your files
Sheetgo is a no-code automation tool for spreadsheets and other office apps. When you create a Sheetgo connection, you watch your data move from one spreadsheet to another automatically. Here we’ll show you how to use Sheetgo to create a workflow, bringing your data from Excel files to Google Sheets and then on to Google Data Studio.
Once inside the web app, click on Connect to start creating the Excel to Google Sheets connection.
Step 2: Select your source data
The first step of the connection is to select your Excel file as your data source. If you store your Excel files in an online storage platform, you can access them straight from the Sheetgo web app and connect them to Google Sheets.
If you do not store your Excel files online yet, it is necessary to transfer them to your Cloud storage.
You could do that individually, one file at a time, but there are a few things to consider.
An effective dashboard or accurate report needs to be fed recent, up-to-date (or live!) data. Manually saving Excel files to Google Sheets is not efficient or reliable. Your data will quickly become out-of-date, plus it’s incredibly time-consuming to have to keep repeating the process. If you want to use source data from Excel files to feed your Google Data Studio reports, the best solution is to set up an automated system.
As an example, we have listed the steps below to sync your offline Excel files to your Google Drive automatically (note that you can do this automatically for any cloud storage solution).
How to save Excel to Google Drive automatically?
- Start your own automated data flow by installing Google’s Backup and Sync software. Backup and Sync is designed as a way to safely back up all your files, but you can use it as way to sync Excel with Google Sheets.
- Select the Excel files you want to use as a your data source and save them in the same folder. Select this folder to be backed up to your Google Drive with Backup and Sync.
Step 3: Send to Google Sheets
In the next step, you need to select your data destination, the Google Sheets file you want to export your Excel data to.
Let Sheetgo create a new Google Sheets file automatically, or choose one that’s already in your Drive.
Click on Finish and save to create the connection between your Excel and Google Sheets file.
Step 4: Automate the workflow
Finally, at the workflow level, click on Automate to schedule the frequency with which you want the automatic updates between Excel and Google Sheets to run. This can happen on an hourly, daily, weekly, or monthly basis.
Step 5: Connect your Excel files to Google Data Studio
Ok, so you’ve set up an automated system to pull data from your Excel file(s) to Google Sheets. Now you’re ready to connect to Google Data Studio.
- Open Google Data Studio.
- Click on Select Data Source, in the top right hand corner of the screen.
- Select Google Sheets.
- Select the spreadsheet, then the worksheet (tab).
- Click on the blue Connect button in the top right-hand corner
- You will now see an overview of all columns, fields and items. Click Add to report.
- A popup appears, to verify that you have selected the correct data. Check it and click Add to report.
Your connection is complete!
You have created a link from the Excel file on your hard drive to Google Data Studio, using Backup and Sync and Sheetgo.
Any changes to the data in the original Excel file(s) on your computer, or the Google Sheets file in your Drive, will be reflected in your Google Data Studio reports.
Need help turning your Excel data into a dashboard?
Looking for advice on how to convert all that data into an eye-catching and effective report or dashboard? Check out our post on how to create dashboards in Google Sheets and Google Data Studio.