Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

pavel_levchuk

Power BI Google Sheet connector

Submitted by on ‎11-06-2023 06:34 AM

Could somebody explain to me how to adjust Power Query so that it does not make tons of requests to Google Sheets?
I regularly receive this error: 

pavel_levchuk_0-1699281151449.png

Microsoft, if you release something please eat your dog food to realize that it just does not work as it should work.

Status: Investigating
See more ideas labeled with:
5 Comments (5 New)
Comments
Anonymous
Not applicable
‎11-06-2023 10:48 PM

Hi  @pavel_levchuk 

I have some information I need to confirm.

1. What is your PBI Desktop version ?

2. Did you connect to Google Sheets before ? Did this error happen recently or has it been there before?

If your Desktop version is not the latest one , you can update Desktop to 2.122.1066.0 64-bit (October 2023) and then try to connect to Google Sheet connector again .

 

Best Regards,
Community Support Team _ Ailsa Tao

pavel_levchuk
Regular Visitor
‎11-07-2023 03:47 AM

1/ I use the latest available version of PBI Desktop.

2/ This error happens from time to time. It's not new.

 

The key issue is that I need to run report refresh several times to complete it. Moreover, when Google sees that the PBI Google Sheet connector generates too many requests it increases the time I need to wait for another try.

Anonymous
Not applicable
‎11-08-2023 11:11 PM

Hi  @pavel_levchuk 

Can you currently restore this reported error? Does this error occur when you are querying larger amounts of data, or is there no pattern to it?

 

Best Regards,
Community Support Team _ Ailsa Tao

pavel_levchuk
Regular Visitor
‎11-13-2023 07:52 AM

@Anonymous 

1/ This error occurs on every Power BI Desktop build. So, I still experience it.

2/ In my work I use from 3 to 10 Google sheets that I join to my model.
I think the main issue is that PQ does a lot of unnecessary evaluations in parallel and that's the problem.

 

Below is a quotation from the Google Sheet API usage limits article:

 

Sheets API has per-minute quotas, and they're refilled every minute. For example, there's a read request limit of 300 per minute per project. If your app sends 350 requests in one minute, the additional 50 requests exceed the quota and generates a 429: Too many requests HTTP status code response. If this happens, you should use an exponential backoff algorithm. After 1 minute, you can execute requests again. Users can submit multiple requests at the same time, as long as they're within the quota limit.

 

Anonymous
Not applicable
‎11-15-2023 10:42 PM

Hi  @pavel_levchuk 

Based on the above information, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

The link of Power BI Support: https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

 

Best Regards,
Community Support Team _ Ailsa Tao

Idea Statuses