Error (429) Too Many Requests - Google Sheets connection

[LucasCamargo]

Hi everyone,

I tried to get my answer from other questions about the same problem but I couldn't find anything useful to me.

Basically I have a Dashboard with a Google Sheet connection and, for a long time, the refresh always worked well, but, since yesterday, the Error 429 Too Many Requests keep showing up and I'm not being able to update my data anymore (not on Power BI Online, neither on Power BI Desktop)

Can someone objectively help me with this matter? I am not finding solution in links or redirected responses.

Thank you all in advance!

[OleksiiV]

Hi maybe my suggestion could help you.
The issue there is that power query send GET request for all sources at the same time and google basicaly can't handle big amout of request in one moment.

I have figureit out by create separate appended google sheet trough google sheets functions QEUERY and IMPORTRANGE which allows you to basicaly merge tables into one inside google infrastructure and use resulted table within powerbi

[Anonymous]

Hi @LucasCamargo 

The "Error 429 Too Many Requests" you're encountering in Power BI when trying to refresh your data connected to a Google Sheet is a common issue related to hitting the rate limits imposed by the Google Sheets API. This error typically occurs when there are too many requests made to the API in a short period of time. Here are some steps you can take to resolve this issue:

1. Reduce Refresh Frequency: If your dashboard is set to refresh very frequently, consider reducing the frequency to avoid hitting the rate limit.

2. Stagger Refresh Times: If you have multiple datasets refreshing around the same time, try staggering them to distribute the load more evenly over time.

3. Optimize Queries: Ensure that your queries are as efficient as possible. Redundant or inefficient queries can contribute to hitting the rate limit.

4. Check for Multiple Connections: If multiple users or processes are accessing the same Google Sheet, each access counts towards the rate limit. Coordinate with your team to minimize simultaneous access.

5. Use Power BI Gateway: If you're using Power BI Online, ensure you have a Power BI Gateway installed and properly configured. This can help manage data connections more efficiently.

6. Error Handling in Queries: Implement error handling in your queries to manage and retry after encountering a 429 error, respecting a reasonable back-off time.

7. Increase API Quota: For Google Workspace users, you can request an increase in your Google Sheets API quota. This is more of a long-term solution and might not be applicable to all users.

8. Cache Data: If real-time data is not critical, consider caching data at intervals to reduce the number of API calls.

9. Review Google Sheets API Usage: Check the Google Sheets API usage in the Google Cloud Console to identify patterns or spikes in requests that might be causing the issue.

10. Contact Support: If the issue persists and is severely impacting your operations, consider reaching out to Microsoft Power BI support or Google Cloud support for further assistance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.