Google Sheets have replaced Excel in a lot of companies so how do Google Sheets get along with Power Query? Actually pretty well.
- You store reference sheets via Google Sheets files where multiple people can work at the same time.
- You would like to use this reference sheet as data source in Power Query.
- You want to be using the reference sheet stored in Google Sheets since it is always the latest and the only correct version.
In combination with Power Query, reference sheets stored in Google Sheets sound like a good idea since Power Query always downloads the latest version of the Google Sheet file before every query refresh. So the question is: How do you do that? How do you connect Google Sheets to Power Query?
It is rather simple:
- Create a shared Google Sheet workbook
- Share it as “Edit – anyone with the link”. Yes, this sounds a bit insecure but if anyone wants to steal your data, they will find different way of doing it anyway.
- In the Google Sheet document, go to File>Download as>Microsoft Excel OR Comma-seperated values OR Tab-Separated values
- Locate the download link of the file you just downloaded. You are not interested in the downloaded file, you only care about the download URL (see the detailed animation below).
- Use the link as URL for “From Web” data source in Power Query
Let’s use this file as the sample document.
Which “download” file format to choose?
(Recommendations given based on personal experience)
Microsoft Excel XLSX
You want to use Microsoft Excel XLSX format when you e. g. want to work with multiple sheets from that file. Those sheets will be visible in PQ as if you were working with a standard Excel file stored on your hard drive. However, there are few problems with using Microsoft Excel format:
- Sometimes, Power Query reads the file inconsistently. It will e. g. tell you that column XYZ is missing despite the fact it is visible in the Google Sheets doc. The solution is to clean cache in Power Query. If your Google Sheets file is complex, you may need to clean your cache before every Query Refresh.
- You may run into a weird error “Relationship tag contains incorrect attribute. Line 2, position 86”. I have never figured out what’s causing the error. I think it is the fact the Google server incorrectly renders the XLSX version of the Google Sheet file. There is no good solution for this error. It has happened to me only twice in the last 2 years. You need to download all the sheets separately as CSVs or TSVs.
Comma or tab-separated values
The safest solution as long as you have only few sheets to download and then use them as source in Power Query. I have not encounter any major errors. When downloading Google Sheet file as CSV or TSV, you need to download all the necessary sheets one by one and use these URLs as data source in Power Query.
The one tip I have for you is to change the encoding parameter in the Power Query. For me, Power Query best-guesses the character encoding as 1252 and you may want to change it to 65001. Especially folks using e.g. Czech characters in the Google Sheet file such as č, š, ř ,ž. Otherwise, the special characters will replaced with some weird signs.
Ok, good but how do I find the download link of the Google Sheet document in my browser?
After downloading the Google Sheet file as XLSX or CSV or TSV, you need to navigate to the download section of your web browser. For example, in Chrome, hit CTRL+J and you will see the history of downloaded files:
First, right click the URL, then select “Copy link address”. Go to Power Query>From Web>Paste the URL here.
Yes, it’s a bit weird. You are not interested in the downloaded file. You are only interested in the source URL!!!
This is how the entire process should look like:
Ok, so this is cool but how do I use this for my work?
There are few possible use cases:
- Maintaining all the reference sheets in one shared place, in a always updated version of the document which used as input in Power Query.
- Building semi-automated complex advertising reports. Imagine you download GA data, Adwords data, Bing Data, Facebook data via SuperMetrics to Google Sheets and you want to create a refreshable report consolidating ALL the data. With the help of Power Query, you can run complex transformations which cannot be done in Data Studio. By having performance data pulled into Google Sheets, you don’t have to download anything from anywhere. The main benefit is that you are able to get even Cost from all the systems into your report.
- Getting always updated data for PPC Search tools. Imagine you export e.g. all Search Terms via AdWords scripts to Google Sheets and then you use this data as input into your Power Query tool which e. g. classifies new search terms suitable for additions into your account.
A sample file with a linked Google Sheet doc can be downloaded here.
And… that’s all. Happy Power-Querying!