Forum Discussion

NoorAlfar's avatar
NoorAlfar
Copper Contributor
Nov 28, 2021
Solved

Connect google sheet to excel

I'm trying to import data from google sheet:

https://docs.google.com/spreadsheets/d/1OBoS9Gpi0YMCZT-RZKggEHju3ulDP3CA5me1mmzHRCk/export?format=xlsx&id=1OBoS9Gpi0YMCZT-RZKggEHju3ulDP3CA5me1mmzHRCk

 

but power query keeps giving me this:

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 tried creating a blank query then using the advanced editor :

let
Source =Excel.Workbook(Web.Contents("link"), null, true)
in
Source

 

I also tried to get the data from the web option but for a reason, the file will be downloaded automatically:

 

 

 

 

Any help with this?

 

BI & Data Analysis
excel
Excel for web
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 28, 2021

    NoorAlfar 

    To my knowledge Google Sheets connector so far doesn't exist for all versions of Power Query. Workaround to connect converted to Excel file is to use ?output=xlsx in UR

     

    let
    Source = Excel.Workbook(
        Web.Contents(
            "https://docs.google.com/spreadsheets/d/1OBoS9Gpi0YMCZT-RZKggEHju3ulDP3CA5me1mmzHRCk/export?output=xlsx&id=1OBoS9Gpi0YMCZT-RZKggEHju3ulDP3CA5me1mmzHRCk"
        ), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type with Locale" = Table.TransformColumnTypes(
        Sheet1_Sheet, {{"Column3", type date}}, "en-US")
in
    #"Changed Type with Locale"

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 28, 2021

    NoorAlfar 

    To my knowledge Google Sheets connector so far doesn't exist for all versions of Power Query. Workaround to connect converted to Excel file is to use ?output=xlsx in UR

     

    let
    Source = Excel.Workbook(
        Web.Contents(
            "https://docs.google.com/spreadsheets/d/1OBoS9Gpi0YMCZT-RZKggEHju3ulDP3CA5me1mmzHRCk/export?output=xlsx&id=1OBoS9Gpi0YMCZT-RZKggEHju3ulDP3CA5me1mmzHRCk"
        ), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type with Locale" = Table.TransformColumnTypes(
        Sheet1_Sheet, {{"Column3", type date}}, "en-US")
in
    #"Changed Type with Locale"
    • NoorAlfar's avatar
      NoorAlfar
      Copper Contributor
      Nov 30, 2021

      Great, thank you for the help. I'm trying to use both power query and power pivot in excel but it seems the options I want do not exist in the current version I'm using ( excel pro plus 2016), do you recommend buying Microsoft 365 or downloading excel 2019 will be enough?

       

      For example, I can't find the options inside the red boxes in the power query I'm using

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 30, 2021

        NoorAlfar 

        Depends on goals. If to make the choice that's between 2021 (next after 2019 version) and 365. With 2021 you will have available for today functionality, and only this one, for at least another couple of years, till version 2023 or so appears. All updates will be related to bug fixing, performance and like, no new functionality within the version. 

         

        365 is updating with new functionality on regular basis, how fast you receive it depends only on distribution channel you select for your subscription (channel could be changed at any time free of charge). Yes, there is difference between subscriptions - Enterprise is the richest one.

         

        You may compare Pro/Business here Office applications service description - Service Descriptions | Microsoft Docs. As I remember among consumer subscriptions Home one has close to Business  (not Enterprise) functionality, the only difference in license terms. In brief, you are not allowed to earn money on using Home subscription.

Resources