Google Sheets Loading Error

[JonathanSh]

Hi,

I have a Google Sheet who is connected to BigQuery and it's usually updating on a daily basis. 

It's currently stuck in updating mode. 

How can I stop the loading and fix this issue?

Few notes - The BQ table itself is running fine.

When I copied the GS it worked.

[jeerahalli · Accepted solution]

My first recommendation would be to do all of these troubleshooting steps:

- Clear cache and cookies of the Chrome Browser with these instructions https://support.google.com/accounts/answer/32050.

- Try on an incognito window, you can use this article as a guide https://support.google.com/chrome/answer/95464.

- Try to disable Chrome extensions as they could be messing the loading process.

- If you use a proxy or firewall add these ports and hosts to your configuration https://support.google.com/a/answer/2589954.

- Try on different browsers like Firefox, Edge, or Safari.

- If you use a Chromebook, try to verify if on another computer (Windows or Mac) the issue reproduces.

- Try making a copy of the Google Sheet, verify if the copy has the same behavior.

- If you are loading large sheets with complex formulas, mostly if they retrieve data from external spreadsheets, the delay when it retrieves the data could cause formulas to fail so is recommended to use smaller spreadsheets or divide the data in other spreadsheets to avoid loading issues.

[tomlag]

it happens the same to me. Most probably is an issue with the refresh of data. Not a local problem with the data.

[Snoshone07]

Hi @JonathanSh iIt seems like the issue might be with the connection between your Google Sheet and BigQuery, rather than with BigQuery itself, since you mentioned the table is running fine. Here are a few steps you can try to fix the problem:

1. Stop the Current Update Process

• Check if there’s an active query running in the BigQuery console that’s linked to your Google Sheet. If there is, you can cancel it from the Query History section.
• Alternatively, try refreshing your Google Sheet or simply close and reopen it to stop the stuck update process.

2. Check the Query Configuration

• If the query pulling data into the Google Sheet is too complex, it might be taking too long or even timing out. Consider simplifying the query or temporarily reducing the dataset size.

3. Verify App Script or Connector Settings

• If you’re using a Google Apps Script or a third-party connector, check if it’s running correctly. Sometimes re-authenticating or adjusting permissions can solve the problem.

4. Try a Fresh Connection

• Since copying the Google Sheet seemed to work, the issue might be tied to the current file’s configuration or cache. You can try re-establishing the connection in the original file:
  • Go to Extensions > Data Connector for BigQuery and set up the connection again.

5. Explore Alternative Tools

• If the problem persists and you need a more reliable way to integrate BigQuery with Google Sheets, you could consider using an external ETL/ELT tool. For example, platforms like Windsor.ai offer seamless integrations, allowing you to connect BigQuery to Google Sheets or other tools with minimal risk of errors.

[tomlag]

I solve it by watching the logging logs. Some users where refreshing the data without the proper permissions.