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

[Syndicate_Admin]

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

[Syndicate_Admin]

Hi Gao,

The steps you have mentioned are for Power BI.

But I have an issue with Excel. 

[Syndicate_Admin]

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]

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]

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]

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