Resources exceeded during query execution: Google Sheets service overloaded for spreadsheet

[Hansenlim]

So I have a gsheet with 1.5k row and a few simple formulas that is not heavy at all to run. Im consuming this gsheet as an external table in bigquery to use in a query.  The ghseet is running fine and i can query to the external table just fine. But today i encountered this issue that said: Resources exceeded during query execution: Google Sheets service overloaded for spreadsheet

What is the root cause of this problem? Cause i did not make any drastic changes to the ghseet and suddenly it just wont run on BQ.  Lots of users use the gsheet daily but it ran fine before. What should i do to fix this problem?

[Hansenlim]

Bump, anyone found a fix for this?

[Snoshone07]

Hi @Hansenlim the error you’re seeing (“Resources exceeded during query execution: Google Sheets service overloaded for spreadsheet”) usually happens when the Google Sheets API is under heavy load. This can occur due to high simultaneous usage or complex interactions, even if your spreadsheet hasn’t changed much. Here are some potential causes and solutions:

Possible Causes

1. Concurrent Access: If multiple users are editing the sheet or running queries through BigQuery at the same time, it can overload the Sheets API.
2. Query Complexity: Even if the spreadsheet formulas are simple, running external queries via BigQuery can put additional strain on the API, especially with multiple simultaneous queries.
3. API Limits: Google Sheets has usage limits for API calls. Querying through BigQuery counts toward these limits, and a sudden spike in activity can trigger the error.

Recommendations to Fix the Issue

• Minimize Simultaneous Usage: Reduce concurrent edits or queries to the Google Sheet, especially during busy periods.
• Optimize BigQuery Queries: Ensure your queries are well-optimized, applying specific filters to limit the amount of data retrieved.
• Export Data to BigQuery: If the sheet’s data is relatively stable or updated periodically, consider exporting it into a native BigQuery table. This removes reliance on the Sheets API. You can automate this process with tools like Apps Script or Dataflow.
• Increase API Quotas: If you manage the GCP project, you could review and increase the Sheets API quota. However, this is more of a temporary fix than a long-term solution.

Alternative

If your team frequently works with Google Sheets and BigQuery, you might want to explore external tools for better data management. For example, Windsor.ai allows you to integrate and consolidate data from multiple sources into BigQuery seamlessly. This can help avoid the limitations of the Sheets API while improving efficiency for querying and reporting.

Greetings