Error connecting to Google Sheets

[PM_Insights]

Hi All, 

I am trying to connect my Power BI to Google Sheets that is provided by my client. This is supposed to be a live data-sheet that will get refreshed on a daily basis. While trying to connect the data-sheet, I received the following error. What does this mean? Please help!

DataSource.Error: Unable to parse range: Connected Sheet 1!A1:GI500
Details:
errorstatus=INVALID_ARGUMENT
errormessage=Unable to parse range: Connected Sheet 1!A1:GI500
errorcode=400

Thanks,

PM

[v-priyankata]

Hi @PM_Insights 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-priyankata]

Hi @PM_Insights 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the relevant solution would be awesome for others who might run into the same thing.

[Poojara_D12]

Hi @PM_Insights 

The error you're encountering—"DataSource.Error: Unable to parse range: Connected Sheet 1!A1:GI500" with error code 400 (INVALID_ARGUMENT)—typically means that Power BI is unable to interpret the range reference you're using to connect to the Google Sheet. This can happen for a few reasons, most commonly due to issues in the sheet name or the range syntax. In your case, the sheet name is "Connected Sheet 1" and the range is A1:GI500, but since the sheet name contains spaces, it must be enclosed in single quotes when used in a range definition—for example: 'Connected Sheet 1'!A1:GI500. Omitting the single quotes causes the parser to fail because it misinterprets the name.

Another possible cause could be that the sheet name is incorrect or doesn’t exactly match what exists in the Google Sheet, including any trailing spaces or hidden characters. Also, the range A1:GI500 assumes that the sheet contains data up to column GI and row 500, which is quite wide and may not exist in your actual sheet, leading to an invalid reference.

To resolve this, double-check that:

The sheet name exactly matches, and is wrapped in single quotes if it has spaces.

The range being referenced exists in the Google Sheet and doesn’t exceed its dimensions.

If you're using a custom URL in Power BI (like a published CSV link), the error could also result from improper formatting or missing parameters.

A safer alternative is to use Google Sheets' published-to-web feature to generate a shareable link in CSV or Excel format, which Power BI can reliably connect to without range issues. Just be sure to confirm that the published version reflects the latest data.

In summary, the error is caused by a malformed or incorrect range reference in your Google Sheets connection, and correcting the syntax and verifying the actual sheet and range should resolve it.

[v-priyankata]

Hi @PM_Insights 

Thank you for reaching out to the Microsoft Fabric Forum Community.

If the sheet name has spaces, so Power BI gets confused unless you wrap it in single quotes. Instead of (Connected Sheet 1!A1 : GI500), use ('Connected Sheet 1'!A1 : GI500).

The range might be too big or not valid, maybe the sheet doesn't actually go all the way to column GI or row 500 yet. Try a smaller range like A1 : Z100, or even just use the sheet name without any range.

The easiest way is to let Power BI detect the sheets for you. After signing in, it’ll list all the available tabs,  just pick the right one instead of typing the range manually. Less chance of errors that way. 

If this information is helpful, please “Accept as solution”  to assist other community members in resolving similar issues more efficiently.
Thank you
 

[v-priyankata]

Hi @PM_Insights 

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

[PM_Insights]

Thanks for responding @Greg_Deckler . This Google Sheet contains nested dataset. Is that the reason this might be happening? Would you know? How do I un-nest this nested dataset, using Power BI? 

[Greg_Deckler]

@PM_Insights Any chance you can share an example of the nested data as a Google sheet? Then I could do some troubleshooting.

[Greg_Deckler]

@PM_Insights Maybe this helps? Troubleshoot API errors  |  Google Sheets  |  Google for Developers