Help Needed: Looker is Failing to Connect to Large Google Spreadsheet

Looker Q&A Looker Studio
,
1

Hi everyone,

I’m experiencing a frustrating issue with Looker Studio and a Google Spreadsheet used as a data source for a report. Here’s the situation:

1. The Problem

  • The Google Spreadsheet is very large (it previously hit the 10 million-cells limit).
  • The client deleted some data to reduce the spreadsheet size but claims the specific sheet connected to Looker Studio wasn’t changed. They only made slight changes to formulas, which theoretically shouldn’t cause issues.

However:

  • Looker Studio fails to connect to the spreadsheet. I get stuck on a loading screen when trying to reconnect the data source.
  • On one occasion, I managed to reconnect, but many columns (mostly numeric fields like revenue and profit) were missing from the report.

2. What I’ve Tried

  • Verified that the missing data still exists in the Google Spreadsheet and hasn’t been renamed or moved.
  • Checked the spreadsheet for broken formulas or formatting issues.
  • Refreshed the data source and attempted to re-map fields (but numeric fields are still missing).
  • Reduced the spreadsheet size further by cleaning up unnecessary rows and columns.

3. Challenges with Current Setup

  • Due to the size of the file, copying data to another spreadsheet would be very problematic. The client has already managed to copy the file itself, but even that was difficult and resulted in error.
  • Splitting the file into multiple smaller spreadsheets could be an option, but this would complicate the report setup. I also have multiple copies of the main report, configured for personal preferences, and restructuring everything would likely take a month of work—so it’s not a optimal at all.

4. Questions & Help Needed

  • Could the issue be related to the spreadsheet’s size or complexity? Are there known limitations for large datasets in Looker Studio?
  • How can I debug or troubleshoot this issue? Are there logs or tools I can use to identify what’s causing the connection to fail or why certain fields are missing?
  • Should I consider moving the data to a more scalable platform like BigQuery? If so is it possible to transition to BigQuery without causing major disruptions to the existing Looker Studio report and its copies?

5. Important Notes

Since the data is private, I’m unable to share links to the spreadsheet or the Looker Studio report itself. However, I’d appreciate any general advice, tools, or best practices you can recommend for handling these issues.

Thanks in advance for your help—I’m stuck and could really use some guidance!

2

Hi,

You should migrate to bigquery, in bigquery there is the possibility of connecting a google sheet and use it for looker studio, but for what you say this google sheet is already overloaded with information and formulas, what I have applied in my company is to see how to download the information in csv and this csv upload it to bigquery and have the data as a fixed table (if you connect the google sheet directly to bigquery will give you an error of overload). Once the information is uploaded, we find a way to pass the formulas from the google sheet to sql and all the calculation is done by bigquery, in a better way:

  • Pass the base to bigquery without the formulas.
  • Pass the logic of the formulas to SQL
  • Create a view from the formulas
  • Connect this view to the dashboard

Sorry for my bad english.

1 Like