Using Google Sheets as Data Source in Power Query (Power BI)

Posted on by Daniel Zrust

Google Sheets have replaced Excel in a lot of companies so how do Google Sheets get along with Power Query (Power BI)? Actually pretty well.

Sample scenario:

  • 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 (Power BI).
  • You want to be using the reference sheet stored in Google Sheets since it is always the latest and the only correct version.

There are two routes you can take:

  1. Secure option – you’ll be able to keep the Google Sheet private but the process is a bit cumbersome
  2. Insecure option – you’ll have to make your Google Sheet public so anyone with the link can access it but  the process is relatively easy

Let’s look at both options in more detail.

Secure Option

If you absolutely need to keep your Google Sheet private, then this is the process:

  1. Build your Google Sheet
  2. Setup Google Cloud Project
  3. Make yourself BigQuery admin in the Google Cloud Project
  4. Install BigQuery Uploader for Google Sheets for your Google Account
  5. Create a dataset in BigQuery
  6. Configure the BigQuery Uploader to upload your Google Sheet to BigQuery to the dataset which you created in step #5
  7. Use ODBC connector for Excel or native BigQuery connector for Power BI to connect to your tables in BigQuery

Technically, you will upload your sheets to BigQuery (all locked under your accounts) and then you will connect to BigQuery from Power Query (Power BI) to get your data. The downside of this approach is that the data in BigQuery is not real time. You can schedule the BigQuery Uploader to upload the data every hour at best or you can trigger to upload from Google Sheets to BigQuery manually in the worst case.

Insecure Option

Insecure but real time.  This method will always give you 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 with no middlemen?

It is rather simple:

  1. Create a shared Google Sheet workbook
  2. Share it as “Edit – anyone with the link”. Yes, this sounds a bit insecure but if anyone wants to steal your data, they will probably find different way of doing it anyway.
  3. In the Google Sheet document, go to File>Download as>Microsoft Excel OR Comma-seperated values OR Tab-Separated values
  4. 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). Update 2020-09-28: It seems like Google has made an update to the structure of generated URLs. See more below.
  5. Use the link as URL for “From Web” data source in Power Query

A sample PAID Excel file with a linked Google Sheet doc
can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

Export URLs after September 2020

Let’s use this file as the sample document. Currently, when I export the file as XLSX, the download URLs looks like this:

https://doc-08-04-sheets.googleusercontent.com/export/4amfcd45o54u9g4fj911090k30/q9asr6c1oprn3mtelv658nh4is/1601311045000/106833805149863350930/106833805149863350930/1JyFG6wl6ljee4bhKt2qLJ8NVfslRzKHzm6rcwqLXdVs?format=xlsx&id=1JyFG6wl6ljee4bhKt2qLJ8NVfslRzKHzm6rcwqLXdVs&dat=AMvwolZMCCoOnLRQTscDGJdz0wCT7PIIldtuaV6YS_Pik7KZl3d_Ugnj5U49__-Wp6Rs3y6aCkPsMwJueEzXkB0gQqbOiWWXv5oBakJMxMTikp3zTD2Uz84pGbXOZjIh7Nq9JdsllG1zq0GtGB6MmdLSxlZf-qHvRKoVRkYw2LGDro3WBapgVLrHCjuZhw9bzCo86MT0CiJY056F7U17wUHXUWWprresETR_Gd0cvoJeUl-VP5ia_WNt6pJY5qK6ZvYFtUMwx2wlwBlN3-sXipf6IuA9ussaEnIrnXDsE0KmAIsRAgMeQmLNRycq4LafLlSnX4AVskpb_RK2rFRf3u_Ruup4NqTE4_gf9EZCE-72Ci8YR468Dy3CCx99G8__aH1t-fThD4dOkdFt0veP7tePUrAepyvxLVV7QR691W_XY0Etjl9HZ6PTBDJtNG-9PaQWOCzH-eZdFlWwalQQmLQK3bmfNTzQfTCyPo6K-29uuvzUz4ULoyf5WQj177eEHt4AmaThF_YqoretORUyOQRCQARvbRXOBh-oJ5SrEnBr89VxoSfp9SA5T1EHPEGUrYLTcUtPHX7FxxH00IcM8r5r4MhPeSNXpv1oi5l-ZIwQKvrkKsZwZz11BilcRRYe7NX3Q2EiKLD4DbiyW7Kc3lXoYbHcs-eoi06FCBpD5pEpEQ76PHtzWXTMefeA0C-tNvAKyoAVoNkhxWENsJVscDVRhEj6cyc

Crazy, right? When you try to use this URL as source in Power Query, it will throw an error.

How to get around this? You have to construct your URL manually. What’s important in the building process is the:

  1. Spreadsheet ID
  2. Sheet ID aka “gid” parameter in the URL

Your goal is to construct a URL looking like this:

So how to actually build the URL? It’s not the difficult:

  1. Copy the red part from the standard spreadsheet URL which you see in your address bar. The spreadsheet ID is the long “1wvu6zq3r_8l7KNHVnY9VH34mE-P8UJAbOfUeym2ev8M” part so you don’t really have to worry about it too much since it can be copied really easily.
  2. Decide if you want export as XLSX or CSV
  3. For XLSX exports, simply add “/export?format=xlsx” to the end of the URL
  4. For CSV exports, simply add “/export?format=csv” and “&gid={GID NUMBER OF THE SHEET}”. For exports of specific sheet, you need to include the gid parameter which stands for sheet ID within the given spreadsheet. If you don’t use the gid parameter, you will always get the first sheet created within the spreadsheet.
  5. Use either of the constructed URLs as source URL in “From Web” in Power Query

Updated URLs in Google Sheets

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:

  1. 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.
    Cache Cleaning in PQ
  2. 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.
Encoding

A sample PAID Excel file with a linked Google Sheet doc
can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

Ok, good but how do I find the download link of the Google Sheet document in my browser?

This information may no longer be valid for some users after September 2020.

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:Download History in Chrome

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:
Google Sheets Setup v2

Ok, so this is cool but how do I use this for my work?

There are few possible use cases:

  1. Maintaining all the reference sheets in one shared place, in a always updated version of the document which used as input in Power Query.
    Look Up Sample
  2. 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.
  3. 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 PAID Excel file with a linked Google Sheet doc
can be downloaded here:  
DOWNLOAD NOW FOR WINDOWS

Conclusion

The key pain point here is to decide whether you want to do more clumsy secured approach or go easier, real time but insecure route. 🙂

The secure route is more complex to setup and probably not for everybody – especially if you have never seen Google Cloud Console.

The insecure route can be managed by anybody but the problem is… its insecurity.

So make your choice and happy power-querying!

29 thoughts on “Using Google Sheets as Data Source in Power Query (Power BI)”

  1. Dan, thanks a lot for super instructions on how to link the sheet with PQ. In the past, I did not understand why table from google sheet does not want to load into PQ when I used the direct URL for sharing. I did not think I needed another url 🙂 Thanks a lot!

    Reply

  2. Thank you so much! I was looking for more information about exactly this–using Power Query in conjunction with data imported from Google Sheets–and like Michal, got snagged on why the direct link would not import. This will be very helpful for my immediate task at hand and I’m sure many more in the future!

    Reply

  3. This was really useful info, and easy to follow; thank you!

    But one question: I ultimately want to use this to link from Excel to a Google Sheet that contains a GoogleFinance formula (which of course does not exist in Excel).
    I can access the source Google sheet successfully, but it just says “the table is empty” and N/A, which is also what appears in any exported file.

    Do you know of any way to maybe make this kind of thing exportable and therefore accessible to Excel? I also tried making an intermediate sheet referencing the GoogleFinance formulas, but that didn’t work either.

    I can’t use a Paste Values type thing, because I want it to always be updated.

    Thank you very much!

    Reply

    1. Hi Kelly,
      in that case, you can have a script which copies Google Finance sheet as values onto another sheet and link that one to Power Query? The script could run every 4 hours or something like that.

      DZ

      Reply

  4. These are great instructions! Our company has locked down our google sheet security to allow sharing by link within the enterprise only. I was hoping that being logged into Office 365 and Google with my enterprise login would work, but it did not. Is there an elegant work-around that would still allow me to access data in google sheets from power query?

    Thank you!

    Reply

    1. Hi Lzombiet,
      unfrortunately, Power Query in Excel is quite stupid – it cannot login into online services and I am not really aware of an elegant work-around though I have been using one but it’s not elegant.

      Basically, you have a script which uploads your Google Sheets to Big Query to which you can connect under login. For Excel, there is “Simba” ODBC driver (you have to download it) to connect to BQ, for Power BI, there is native connector within the Query Editor. It’s a bit complicated to setup but then it works OK.

      DZ

      Reply

      1. Hi DZ,

        What’s the native connector in the Query Editor and how do I set this up?

        Thanks,
        Richmond

        Reply

        1. Hi Richmond,
          it’s just the very basic “From Web” connector.

          Your Google Sheet needs to “be public”. Then you just grab the download link and use it in the “From Web” connector.

          DZ

          Reply

  5. I run weekly PowerQuery reports in Excel but my teammates would like to use the Googlesheet to continually revise and update data. I understand how to get the data down but will I seem to have lost my queries (which I close & load as connection only) when I uploaded the document for use again in Google…
    Is there a solution for this or will I need to recreate the wheel every week when I download the link in PQ? Thanks!

    Reply

    1. Hi Julie,
      maybe stupid question – but why don’t your mattes keep editing always the very same Google Sheet file?

      Any time you upload a new doc to Google Sheets then it get’s a new ID = new link for Power Query, so yes, you’d have to update the link every week. A gapps script could do the trick for you (unpack Excel=>take its content=>save to a Google Sheet), but it’s more on the advanced side.

      DZ

      Reply

  6. I have a master Excel file with multiple tabs. Each tab contains a game schedule for a soccer league. I wish to create a Google sheet file for each Excel tab and share it with the teams in that tab to enter scores into the schedule. I would like to then to import the scores from each Google sheet file into the appropriate tab of the master Excel file. This process would happen each week.

    Is your instruction the way to accomplish what I want to do?

    Reply

    1. Hi Alan,
      yes, this would be doable.

      You would use those Google Sheets as data sources in order to feed this data into Excel. Just couple things to keep in mind: When you refresh the query in Excel, all the data is overwritten. So if the older entries in your Google Sheets are being removed, you’d be missing them in the Excel after the refresh in Excel. Basically, the refresh drops all the data in Excel and gets the new data (=all entries) from your data sources (in this case, Google Sheets).

      Daniel

      Reply

  7. Amazing! Thank you very much!!!!
    This is exactly what I was looking for! And it works like a charm!

    Do you know if the download-URL is permanent? It feels a little bit hacky to use the link of the .csv file so I’m a bit worried that after a while you have to get a new one. Any experiences?

    Reply
  8. Pingback: How (And Why!) To Connect Excel To Google Sheets – How Not To Go Bananas

  10. Hi DANIEL, any way to do the reverse process? send excel data to google sheets? or two way read/write connection? Than you.

    Reply

  11. I’ve tried this several times on multiple machines but I get an error. Did something in Sheets or Excel change? I have Office 2016.

    Reply

    1. This is the error message:

      Unable to connect

      We encountered an error while trying to connect.

      Details: “Web.Contents failed to get contents from ‘[URL here]’ (400): Bad Request”

      Reply

      1. If you want, you can share your file privately with me on mail@danzrust.cz and I can have a look. Looks like you are doing something wrong. I use this trick every day and it works fine.

        Reply

          1. Hm… weird. Is your file “publicly accessible”? Also, are there any pivot tables if you are importing as XLSX? They tend to break the export.

            DZ

          2. Can’t reply directly to Daniel’s comment, so will reply here.

            Didn’t have any Pivot Tables in the file at the time. Have not figured out what caused it so far.

  12. “Relationship tag contains incorrect attribute. Line 2, position 86”

    Figured this one out! It occurred when I added Pivot Tables to the Google Sheets file. Even if you’re not accessing them – if they exist in the file they’ll cause issues.

    Reply

  13. Hi,

    it seams as if this whole process only works for google sheets and not for native csv files stored in google drive.

    I am trying to access a csv file that is regularily uploaded to google drive.

    First thing I note is that the download link starts with

    https://DRIVE.google.com/FILE/

    and not

    https://DOCS.google.com/SPREADSHEETS/

    If I use this in PowerQuery I only get the meta date of a html site as output.

    Another interesting input:
    If I open a csv in google.sheets it will automatically create a google.sheets file and the download link created here (within google.sheets) will pin to this sheet file and not the original csv.
    This of course is useless as only the csv gets constantly updated.

    Any ideas?

    Reply

    1. Hi,
      yea, the article is about Google Sheets, not CSV files stored in Google Drive.

      I do have a clumsy workaround for your CSV problem but I don’t think you will not like it.

      Here are the steps:
      1) Use your CSV file as a “table” in Google BigQuery. You’ll need to setup a GCP project first. This could be scary if you have not done it before.
      2) If you get past #1, then you need to run SELECT * FROM {yourtable} every hour and via CREATE OR REPLACE TABLE command. The thing is that you can query stuff in BigQuery via PowerQuery but only the files which technically sit in BigQuery – that’s not the case for your CSV which sits on Drive. So you have to select all rows from your CSV and create a regular BigQuery table.
      3) Install SIMBA ODBC connector on your Windows machine to be able to query BigQuery data from your PowerQuery in Excel
      4) In PowerQuery, use ODBC connector with your newly created connection to query the BigQuery data

      And “that’s it”. As I said – it’s clumsy and scary but that’s the best workaround I can come up with.

      Daniel

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *