Too many requests google sheet connector even small sheets

[Nevsksar]

Google sheets connector always worked great, never had any issues till now. However since some update in late 2023 yearly 2024 I have been getting this 429 error message stating too many requests were being made and the data simply will not load.

I Came to a point when testing this behavior that simply connection to a single sheet with 50 rows returns me this error. so i know the number of sheets connected or the number of rows has nothing to do with it.

I have to keep trying and without changing anything at some point it will simply accept and fetch the data normally. But that turns my refreshes unusable, since  they are not reliable and i have to keep refreshing them by hand in order to try multiple times.

I have seen many users reporting similar issues, but no real solution provided until now.

If you work with too many sheets some people say to join them all in a single sheet (but as stated that is not the issue here, since a since sheet with 50 rows will get the same error), googles documentation said to do a Exponential backoff  but that cannot be done in pwoerquery (you could do by programming an api but not through power query) and there is no way to increase the limit eitehr by sheets side or PBIs side.

The bulk of the data releis on sheets and this weird behaviour ends up making impossible to use powerbi as is

[Anonymous]

Hi @Nevsksar 

If you exceed a quota, you'll generally receive a 429: Too many requests HTTP status code response. If this happens, you should use an exponential backoff algorithm and try again later.

Usage limits  |  Google Docs  |  Google for Developers

Best Regards,
Community Support Team _ Ailsa Tao

[Nevsksar]

Yes, I´ve read the documentation however:
1 -  As far as I know there is no way of implementing an exponential backoff algorithm in powerquery; the directions on the doc reffer if you are coding something then you can use that technique in your code;

2 - It used to work fine I've had reports connected to multiple sheets with a large number of rows and it used to work fine. Now for some reason I cannot load consitently even a single small sheet without receiveing this error. Ehich lead me to believe it is somthing inherently wrong with the connector.

3 - Since it is related to the ammount of requests, I believe this is an issue on sheets connector in powerbi it is spamming requests and causing this issue. If it had by itself implemented the algorithm to slownly request instead of just erroring out, I believe it should work.

4 - The ammount of issues from otehr users claimming the same issue leads to believe that this is not an isolated issue, hence an issue with power query.

5 - If you keep on trying to refresh, eventually it will work. So if nothing has changed between refreshes  (google did not change the requests limit, the sheets are teh same the tarnsformations in PQ are the same, the users are the same, there is a single user requesting data from that sheet) the only explanation is something worng with how the connector is handling the requests.

[patrick_avia]

@Nevsksar - Did you ever see a resolution on this? I am having the same issue.