none
Power Query for Google Sheets RRS feed

  • Question

  • Does anyone know of any plans to create an add-on for Power Query in Google Sheets?   I have played with the IMPORTHTML function but it doesn't provide the same data manipulation tools the PQ does.   I haven't had any luck finding anything in Google searches.

    Thanks


    Thursday, February 26, 2015 2:31 PM

Answers

  • Here's a way you can do this without a built-in connector:

    1. Make your Google Docs workbook visible to anyone that has the link (or public).
    2. Go File > Download as > Microsoft Excel.
    3. Get the link that was used in the browser's download manager.
    4. Go in PQ > From Web.
    5. Paste the link.
    6. Click "OK"

    The workbook will be download as an Excel workbook and opened in PQ.

    Thursday, February 26, 2015 9:48 PM
    Moderator

All replies

  • Here's a way you can do this without a built-in connector:

    1. Make your Google Docs workbook visible to anyone that has the link (or public).
    2. Go File > Download as > Microsoft Excel.
    3. Get the link that was used in the browser's download manager.
    4. Go in PQ > From Web.
    5. Paste the link.
    6. Click "OK"

    The workbook will be download as an Excel workbook and opened in PQ.

    Thursday, February 26, 2015 9:48 PM
    Moderator
  • Actually what I need to do is pull table data from an external web source from within a Google sheet and be able to do the data manipulations that PQ can do (and IMPORTHTML can't).   I'd like to have the PQ functionality available within the Google Sheet (i assume as an add-on).
    Friday, February 27, 2015 12:55 PM
  • This doesn't seem to work. PQ just retrieves an HTML table

    Kind Name Children Text
    Element HTML [Table]


    • Edited by greenguy13 Friday, June 10, 2016 3:30 AM
    Friday, June 10, 2016 3:30 AM
  • If I was going to hook up PowerQuery to google sheets I would use gspread.

    Basically by the deadset simple way do you transformation in PQ and save the excel file the open the excel file in sheets in automated fashion with https://github.com/burnash/gspread . You do need Ouath2 key but then you can do.

    # You can open a spreadsheet by its title as it appears in Google Docs
    sh = gc.open("My poor gym results") # <-- Look ma, no keys!
    
    # If you want to be specific, use a key (which can be extracted from
    # the spreadsheet's url)
    sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
    
    # Or, if you feel really lazy to extract that key, paste the entire url
    sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
    Of course if you want it fully automated then you would need XLRD too, haven't gone there unless thats what you really need.
     

    Taking my C# further

    Friday, June 10, 2016 12:28 PM
  • hi, the steps works for me. Thank you very much.


    Saturday, February 10, 2018 5:19 AM
  • Has anyone found a solution to this ridiculous problem, which is seems due to Team Drive level permissions preventing Power Query or PowerBI from accessing Google Sheets? This connection is not a problem when working with one's "Personal" or otherwise non-corporate Google Sheets and account.
    Monday, October 14, 2019 3:59 PM
  • This doesn't seem to work. PQ just retrieves an HTML table

    Kind Name Children Text
    Element HTML [Table]


    I get this same issue... any help anyone?
    Tuesday, August 18, 2020 2:40 PM
  • This does not work anymore.  Apparently things have changed as of September 2020 which requires a manual URL construction. 
    Monday, October 12, 2020 4:57 PM