Query a BigQuery dataset and write the results to a Sheet spreadsheet using connectors

  main 
 : 
  
 steps 
 : 
  
 - 
  
 init 
 : 
  
 assign 
 : 
  
 # Replace with your sheetId and make sure the service account 
  
 # for the workflow has write permissions to the sheet 
  
 - 
  
 sheetId 
 : 
  
 "1D8n7uoU8kGwQvR4rcLkF10CdAfnUKE2o0yl6P-Z7nfM" 
  
 - 
  
 limit 
 : 
  
 100 
  
 - 
  
 runQuery 
 : 
  
 call 
 : 
  
 googleapis.bigquery.v2.jobs.query 
  
 args 
 : 
  
 projectId 
 : 
  
 ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")} 
  
 body 
 : 
  
 useLegacySql 
 : 
  
 false 
  
 # Query name and gender of most popular names 
  
 query 
 : 
  
 ${"SELECT name, gender, SUM(number) AS total 
  
 FROM `bigquery-public-data.usa_names.usa_1910_2013` 
  
 GROUP BY name, gender 
  
 ORDER BY total DESC 
  
 LIMIT " + limit} 
  
 result 
 : 
  
 queryResult 
  
 - 
  
 init_header_row 
 : 
  
 assign 
 : 
  
 - 
  
 rows 
 : 
  
 - 
  
 [ 
 "Name" 
 , 
  
 "Gender" 
 , 
  
 "Total" 
 ] 
  
 - 
  
 process_query_result 
 : 
  
 for 
 : 
  
 value 
 : 
  
 row 
  
 in 
 : 
  
 ${queryResult.rows} 
  
 steps 
 : 
  
 - 
  
 process_each_row 
 : 
  
 assign 
 : 
  
 - 
  
 name 
 : 
  
 ${row.f[0].v} 
  
 - 
  
 gender 
 : 
  
 ${row.f[1].v} 
  
 - 
  
 total 
 : 
  
 ${row.f[2].v} 
  
 - 
  
 row 
 : 
  
 [ 
 "${name}" 
 , 
  
 "${gender}" 
 , 
  
 "${total}" 
 ] 
  
 - 
  
 rows 
 : 
  
 ${list.concat(rows, row)} 
  
 - 
  
 clear_existing_values 
 : 
  
 call 
 : 
  
 googleapis.sheets.v4.spreadsheets.values.clear 
  
 args 
 : 
  
 range 
 : 
  
 "Sheet1" 
  
 spreadsheetId 
 : 
  
 ${sheetId} 
  
 result 
 : 
  
 clearResult 
  
 - 
  
 update_sheet 
 : 
  
 call 
 : 
  
 googleapis.sheets.v4.spreadsheets.values.update 
  
 args 
 : 
  
 range 
 : 
  
 ${"Sheet1!A1:C" + (limit + 1)} 
  
 spreadsheetId 
 : 
  
 ${sheetId} 
  
 valueInputOption 
 : 
  
 RAW 
  
 body 
 : 
  
 majorDimension 
 : 
  
 "ROWS" 
  
 values 
 : 
  
 ${rows} 
  
 result 
 : 
  
 updateResult 
  
 - 
  
 returnResult 
 : 
  
 return 
 : 
  
 ${updateResult}