Saving Data to Google Sheets From BigQuery

It’s been a while since I’ve started working on datasets with BigQuery. Most of the time, I need to generate reports for several teams that work with/love spreadsheets. There are multiple ways to extract and present data (Connecting to Data Studio, Google Sheets via OWOX, etc.), but today I’m just going to show you one way.

Extracting and Saving to Google Sheets

Have your query prepared, and for those of you starting out, here’s the general syntax.

SELECT * FROM `GCP_Project_Name.Dataset.Table`
WHERE [CONDITIONS]

There are 2 options you can choose from when extracting:

Option 1

If your query is fairly simple and completes relatively quickly, I’d suggest to run the query from Google sheets directly using the native data connector (You may need to have a GSuite subscription for this feature).

Click on “Connect to BigQuery”, select the project, paste the query, then click “Insert results”.

Option 2

If your query is complex, and long-running, you may want to let BigQuery handle the heavy lifting at the back, and only query from the results table from Google sheets. To do that, go to your GCP BigQuery, type out your query, and schedule it. The “Schedule query” option can be found in the query editor page on the mid-right side.

Query editor — obviously yours would have your query pasted here, and not blank like mine :))

You can schedule the query to append the result table or overwrite it. Since this result set is much smaller, you can paste this table with your conditions in Google sheets.

Google sheet’s new data connector lets you preview your data first before extracting. From this preview, you can play with your data first — create functions, calculated columns, etc.

Preview sheet. Just named my connector “Auto Report”

Once you’re ready to extract, click on “Extract”. The maximum limit for Google sheet extraction is 25k rows. That’s quite big. (You can expand this number with some macros, but frankly if you need more rows, it may be best to schedule this query, connect to a data preparation tool like Dataprep, have it extracted to CSV, and saved in GCP storage).

When you click “Extract”, it will open a new sheet in your Google workbook which is also connected to the same source. You can click on refresh options to schedule the refresh. The thing about this refresh is that it refreshes all the connected sheets. You can’t just refresh particular sheets. If you’re okay with that, you can stop right here. If you’re like me and several other people that work with datasets, and need the last few runs saved, please continue.

Using Google Macros and Triggers

On your data connector sheet, go to Tools > Macros > Record Macro. After clicking on this, it will record your on-screen actions. Click anywhere then stop recording. Give the macro a title and save. This will bring up a notification at the bottom left “EDIT SCRIPT”. Clicking on this will open up the script editor. You should see some content inside a function which you’ve just named. You can delete the insides of this function.

Here’s the snippet for refreshing the data

//Refresh Data  
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Extract'), true);
  SpreadsheetApp.enableAllDataSourcesExecution();
  spreadsheet.getCurrentCell().getDataSourceTables()[0].refreshData().waitForCompletion(120);

Google uses JavaScript for this. The above snippet sets the active spreadsheet to “Extract” (You can put the name of your sheet there), and then executes a refresh data. I added “waitForCompletion” to give it time to complete the refresh before moving to the next part of the script.

After refreshing your data, you need to save it somewhere. Here’s the snippet I used:

/Save latest refreshed data in new tab
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var now = new Date();
  var yesterday = new Date(now.getTime() - 1 * MILLIS_PER_DAY);
  var formatted = Utilities.formatDate(yesterday, 'Hong Kong', 'MMMM dd, yyyy');
  spreadsheet.insertSheet(1);
  spreadsheet.getRange('\'Extract\'!A1:V25000').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getActiveSheet().setName(formatted);

You can write a script to give each tab your own name, but for this example, I named each new tab yesterday’s date, and copy-pasted the data via getRange and PASTE_VALUES CopyPasteType option. You can try using getRangeList and other CopyPasteType options as well. I just needed the values and not the underlying formula hence just the PASTE_VALUES option.

Since the above snippet would forever keep creating new tabs, you may want to add in some code to only keep the last few runs. Something like this:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (i = 0; i < sheets.length; i++) {  
     if (sheets.length > 2)
     {
       switch(sheets[i].getSheetName()) {
         case "Auto Report":
         case "Extract": 
           break;
         default:
           ss.deleteSheet(sheets[i]);}
     } 
  }

I just used a for loop with a switch case. Just add in more cases for the tabs you’d like to keep.

After you’ve completed your macro, it’s time to create a trigger. A trigger will let Google run the macro for you at a scheduled time. Click on the small clock at the top left of the page. It will open a new webpage to all your triggers for this sheet. Click on “Add Trigger”. Select the function you’d like to run, and change the event source to “Time-Driven”. You can have this trigger run at your preferred time. If you used option 2 in the above extract option — saving the result set to a new table, you need to schedule this trigger after that result set table is populated or else you won’t be getting the latest data.

That’s it! Hope this helps.