none
Import from Excel file xlsx on Google Drive RRS feed

  • Question

  • Hello,

    I am trying to import from Excel file stored on Google Drive.

    I made this file shared and got the link (opened for everyone with the link, no sign-in required):

    https://drive.google.com/file/d/0BxwE8Gt6ONVtMXFYYmE2N2lQSG8/view?usp=sharing

    When I open it with Power Query

    = Excel.Workbook(Web.Contents("https://drive.google.com/file/d/0BxwE8Gt6ONVtMXFYYmE2N2lQSG8/view?usp=sharing"), null, true)

    I get this Error:

    DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.

    It seems that it doesn't recognize the xlsx format

    Please help

    Thanks

    Michael


    Michael

    Monday, December 26, 2016 11:42 AM

Answers

All replies

  • This works for me:

    let
        Source = Web.Page(Web.Contents("https://drive.google.com/file/d/0BxwE8Gt6ONVtMXFYYmE2N2lQSG8/view?usp=sharing")),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Source] = "Table")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
        #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1",
     "Column2", "Column3", "Column4", "Column5", "Column6"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data")
    in
        #"Promoted Headers"


    • Edited by MarcelBeug Monday, December 26, 2016 12:41 PM
    Monday, December 26, 2016 12:41 PM
  • Thanks MarcelBeug,

    But what do you see?

    It only shows one of the tables.

    I have 3 sheets in the file - I don't see the option to choose any sheet

    Thanks

    Michael


    Michael


    • Edited by M. Shparber Monday, December 26, 2016 12:57 PM
    Monday, December 26, 2016 12:51 PM
  • Ah, I see. I digged a bit further and found https://social.technet.microsoft.com/Forums/en-US/84bd90ff-964b-4f8d-a17f-0232f0357743/power-query-for-google-sheets?forum=powerquery

    Based on that info I pressed the download button, created a screen shot with the download URL and typed it in Power Query:

    = Excel.Workbook(Web.Contents("https://docs.google.com/uc?authuser=0&id=0BxwE8Gt6ONVtMXFYYmE2N2lQSG8&export=download"), null, true)

    This will give you the Excel navigation table from which you can proceed.

    • Marked as answer by M. Shparber Monday, December 26, 2016 10:31 PM
    Monday, December 26, 2016 1:42 PM
  • Thanks a lot Marcel,

    It is strange since sometimes it is working and sometimes it shows the same Error:

    DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server

    I can figure out why :(

    Please help

    Thanks

    Michael


    Michael

    Monday, December 26, 2016 9:48 PM
  • Dude, never mind, I understand why I am getting this error!!!!!

    I tried to go to Google Docs on the Web and download the file (different one) but then I got THIS:

    "Sorry, this file is infected with a virus" !!!!!!!!!!

    So, Power Query, instead of getting the file, gets this HTML message.!!!!

    AAAAA!!! Several hours spent..... :)

    At first it worked as you said probably because Google didn't realize yet that there is a virus.

    Don't worry - it is another file - not the one I mentioned in the post.

    Last thing - the file is .xlsm with macros, so maybe it thinks macro is a virus...

    THANKS A LOT, MAN!

    YOU REALLY HELPED!



    Michael


    • Edited by M. Shparber Wednesday, December 28, 2016 10:26 AM multiple rows
    Monday, December 26, 2016 10:30 PM