I can't see the whole exported rows from BigQuery to the Google Sheets, I have almost 12k rows ?

[abdallahsaeed]

I can't see the whole exported rows from BigQuery to the Google Sheets, I have almost 12k rows .
I only see 31 rows so i can't deal or process the data.
any help ?

[ms4446 · Accepted solution]

In BigQuery the "Explore in Sheets" feature limits exports to 10,000 rows, which is tailored more for quick insights rather than larger datasets.

Given that your dataset consists of approximately 12,000 rows, I recommend the following methods to manage and analyze your data effectively:

GCS as an Intermediary:

• Export to GCS: First, export your data from BigQuery as CSV, JSON, Avro, or another supported format into a GCS bucket. Please ensure appropriate permissions are configured on both BigQuery and GCS.
• Import to Sheets: Then, use the "File > Import" option in Google Sheets to bring the data into your spreadsheet. This method supports up to 5 million cells, which should comfortably accommodate your dataset.

Connected Sheets for In-Depth Analysis:

• If your analysis needs are more complex and you prefer working within Google Sheets, Connected Sheets might be the right choice. This feature enables live queries of your BigQuery data directly in Sheets, circumventing traditional export limitations. Be aware that performance can vary based on the Google Workspace tier you're subscribed to.

Selecting the Right Approach:

• For straightforward access to all 12,000 rows without needing frequent live updates, using GCS to bridge BigQuery and Google Sheets is highly efficient.
• For ongoing, complex analyses directly in Google Sheets, Connected Sheets offers robust capabilities and flexibility.