Best way to export Google BiqQuery results into Google Sheets using C# API

Question

I wanted to export the BigQuery results into Google Sheets, consider the export data to be in high volume, What is the best way to do it?

I can run BigQuery(SQL Query) and loop it through to create CSV data and can upload it using GoogleSheet API, But I feel this is not the optimize way as there will be thousands of records involved, Is there any better way out.

I am expecting GoogleSheet URL in response.

Answer 1

It looks like there's no way of exporting from BigQuery directly to Drive with the API, even though it's supported in the BigQuery console. From the BigQuery "exporting table data" documentation

You cannot export table data to a local file, to Google Sheets, or to Google Drive.

Likewise in the "downloading and saving query results" documentation:

Saving query results to Google Drive is not supported by the command-line tool, the API, or the classic BigQuery web UI.

Having said that, you can export to Google Cloud Storage, to avoid downloading all the data. As far as I can tell there's no "import from GCS" option in the Drive or Sheets APIs, but you could write a Google Cloud Function to do that. There a blog post specifically covering that, leading to a code lab.

Obviously this isn't as simple as it might be, and it's entirely possible that in the future there'll be a way of doing this directly, but at the moment this should help you move forward.