Google Cloud Workflow: Export BigQuery results to Google Sheets?

Question

I am setting up a Google Cloud Workflow to write the result of a BigQuery query to a Google Sheet on a daily schedule.

I get the following error

"message": "HTTP response exceeded the limit of 2097152 bytes",

My result size is over 1mm rows so not even close to making this threshold.

I've also tried using AppsScript to paste a range from my Google Sheet connected to BigQuery, but the connection only shows a preview of 5k rows so this approach doesn't work either.

Is there a way using Workflows, Sheets macros or GCP bucket to write BigQuery data of ~1mm rows to Sheets on a schedule?

Unfortunately, the downstream tool only takes Google Sheets data.

Tried AppsScript:

function Copyall() {
  const source = SpreadsheetApp.openById("my_sheet_1");
  const source_range = source.getRange("Export!A:W");
  const source_data = source_range.getValues();

  const paste_to = SpreadsheetApp.openById("my_sheet_2");
  const paste_range_start = paste_to.getRange("Sheet1!A1");
  const paste_sheet = paste_to.getSheetByName(paste_range_start.getSheet().getName());

  paste_sheet.clear();

  const paste_range = paste_sheet.getRange(
    paste_range_start.getRow(),
    paste_range_start.getColumn(),
    source_data.length,
    source_data[0].length
  );

  paste_range.setValues(source_data);

Tried Workflows:

https://cloud.google.com/workflows/docs/write-to-google-sheets#deploy-workflow

Answer 1

Your approach with connected sheets, However, it seems like there’s a limit of 5k rows, so this might not work for your use case.

https://support.google.com/docs/answer/9702507?hl=en

One workaround could be to split your data into smaller chunks that are under the limit, write each chunk to a separate sheet in your Google Sheets document, and then use Apps Script to combine them into one sheet. However, you might encounter an error if your result size is over the HTTP response limit. Another option could be to write the results of your BigQuery query to a Google Cloud Storage bucket and then import the data from the bucket into Google Sheets. However, BigQuery does not support writing its query results directly to GCS. You will have to write the results to a table, and then export the table to GCS after it’s been materialized. check this : How to write query result to Google Cloud Storage bucket directly?

There are external tools and services available that can sync BigQuery data to Google Sheets, such as Supermetrics, Panoply, and Fivetran. These tools are designed to handle large amounts of data and can be scheduled to run at regular intervals. However, they may come with a cost. https://supermetrics.com/blog/bigquery-query-google-sheets

Answer 2

You got into a few google limitations at the same time: 50,000 rows limit on connected sheets, 10M cells limitations on google sheets itself.

You can try OWOX BI BigQuery Reports which doesn't have additional limitations to what google sheets have.