Using Google Sheets as Data Source in Power Query

Google Sheets have replaced Excel in a lot of companies so how do Google Sheets get along with Power Query? 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.
  • You want to be using the reference sheet stored in Google Sheets since it is always the latest and the only correct version.

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


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:

  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 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).
  5. 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:

  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.

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


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: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:  


And… that’s all. Happy Power-Querying!


***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project here.***

19 thoughts on “Using Google Sheets as Data Source in Power Query

  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!

  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!

  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!

    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.


  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!

    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.


      1. Hi DZ,

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


        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.


  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!

    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.


  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?

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


  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?

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

Leave a Reply

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