An error occurred while processing the data in the semantic model.

[rakozzz]

Issue with Data Refresh from Google Sheets: "Too Many Requests" Error

Hi everyone,

I'm encountering an error while trying to refresh data in Power BI. The refresh fails with the following message:

```
An error occurred while processing the data in the semantic model.
Refresh failed:

Project #4 Поисковик has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

<ccon>DataSource.Error: Web.Contents failed to get contents from 'https://sheets.googleapis.com/v4/spreadsheets/10z-Zo1H1M2QbKs1ZsUGY1_VdYpLKTod18QaGoco6AG8?ranges=Ex...' (429): Too Many Requests. DataSourceKind = GoogleSheets. DataSourcePath = https://docs.google.com/spreadsheets/d/10z-Zo1H1M2QbKs1ZsUGY1_VdYpLKTod18QaGoco6AG8/edit?usp=sharing. Url = https://sheets.googleapis.com/v4/spreadsheets/10z-Zo1H1M2QbKs1ZsUGY1_VdYpLKTod18QaGoco6AG8?ranges=Ex.... </ccon>. The exception was raised by the IDbCommand interface. Table: GS Inventory DP.

Next refresh for Project #4 Поисковик is scheduled for July 12, 2024 15:00 UTC.
```

It appears that the error is due to a "429: Too Many Requests" response from the Google Sheets API. Here are the details of the data source and the specific query:

- **DataSourceKind**: GoogleSheets
- **DataSourcePath**: [Google Sheets Link](https://docs.google.com/spreadsheets/d/10z-Zo1H1M2QbKs1ZsUGY1_VdYpLKTod18QaGoco6AG8/edit?usp=sharing)
- **URL**: https://sheets.googleapis.com/v4/spreadsheets/10z-Zo1H1M2QbKs1ZsUGY1_VdYpLKTod18QaGoco6AG8?ranges=Ex...

Has anyone encountered this issue before? Any suggestions on how to resolve this error? I understand that the 429 error indicates too many requests, but I'm not sure how to manage or mitigate this within Power BI. Any help or guidance would be greatly appreciated!

Thank you in advance!

[ReportMaster]

Hi @rakozzz were you able to find a solution? As a workaround, you can try to connect your Google Sheets data using some external connector, which would manage on a better way the API limits. I've tried some tools out there like Supermetrics, etcetera, among others but currently, I use windsor.ai due to their good pricing

[rakozzz]

@rajendraongole1 Thanks for your response
Could you please elaborate on the first option?

[rajendraongole1]

Hi @rakozzz -Google Sheets API typically indicates that your application (in this case, Power BI) is making requests to Google Sheets at a rate that exceeds the allowed quota for your API key or service account. 

some solutions:

• Review Google Sheets API Usage: Check the usage limits and quotas for the Google Sheets API associated with your API key or service account. You can monitor these through the Google Cloud Console under API & Services > Quotas.

• Adjust Data Refresh Frequency: Reduce the frequency of data refreshes in Power BI to stay within the API quota limits. For example, if you have hourly refreshes, consider spreading them out or reducing the frequency to every few hours.

• Optimize Query Ranges: Limit the ranges queried from Google Sheets to only what is necessary. If your query retrieves large datasets unnecessarily, it can contribute to hitting the rate limits faster.

• Implement Backoff Strategy: Configure Power BI to implement exponential backoff in case of receiving 429 errors. This means if a request fails with a 429 error, wait for an increasing amount of time before retrying the request. This can help mitigate the impact of rate limiting.

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!