Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Unable to link google sheets with excel using power query/get data

Hello,

 

I have followed all the steps mentioned for editing the URL link of the google sheets "export?format=xlsx".

However, when the navigator window opens, instead of the sheets being displayed, I get the document and when I click on that there is a weird 2-row table. I have attached the screenshot of the same.

 

I have checked the community answers as well but nowhere I could get an exact solution.

 

I have tried all possible solutions but the same issue persists.

 

Kindly help me with this.

 

Thank you,

Ashwini

 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Ashwini,

     

    The first record under the [Children] field is a table, consider adding a deepening step in the advanced editor,like.

    let
  Source       = GoogleSheets.Contents("https://docs.google.com/xxxxxxx"), 
  Sheet1_Table = Source{[Kind = "Element", Children = "Table"]}[Children]
in
  Sheet1_Table

     You can also click it directly in the PowerQuery Editor view.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Hi Gao,

    Thank you for the response!

    However, I could not quite follow your instructions as I am using this power query to connect Google Sheets to Excel for the first time. 

    Could you please explain in detail as I was not able to understand where to input the below step you mentioned

    Thank you,

    Ashwini

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago
    2 seconds ago

    Hi Gao,

    Thank you for the response!

    However, I could not quite follow your instructions as I am using this power query to connect Google Sheets to Excel for the first time. 

    Could you please explain in detail as I was not able to understand where to input the below step you mentioned

    Thank you,

    Ashwini

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi @Ashwini,

       

      1. In PowerBI Desktop --> Get data --> choose the google sheets:

      2. You'll be prompted for a Google Sheets URL. Copy and paste the URL from your browser address bar into the input prompt.

      3. The Google Sheets connector supports connecting through an organizational (Google) account. Select Sign In to continue.

      4. Check the workbook you want to import and click Convert Data to enter the PowerQuery Editor.

       

      5. Try clicking Table, when the mouse hovers over it, it has an underline and the font color is different, indicating that it is a container.

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Hi Gao,

    The steps you have mentioned are for Power BI.

    But I have an issue with Excel.