/** * Runs a BigQuery query and logs the results in a spreadsheet. */functionrunQuery(){// Replace this value with the project ID listed in the Google// Cloud Platform project.constprojectId="XXXXXXXX";constrequest={// TODO (developer) - Replace query with yoursquery:"SELECT refresh_date AS Day, term AS Top_Term, rank "+"FROM `bigquery-public-data.google_trends.top_terms` "+"WHERE rank = 1 "+"AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK) "+"GROUP BY Day, Top_Term, rank "+"ORDER BY Day DESC;",useLegacySql:false,};letqueryResults=BigQuery.Jobs.query(request,projectId);constjobId=queryResults.jobReference.jobId;// Check on status of the Query Job.letsleepTimeMs=500;while(!queryResults.jobComplete){Utilities.sleep(sleepTimeMs);sleepTimeMs*=2;queryResults=BigQuery.Jobs.getQueryResults(projectId,jobId);}// Get all the rows of results.letrows=queryResults.rows;while(queryResults.pageToken){queryResults=BigQuery.Jobs.getQueryResults(projectId,jobId,{pageToken:queryResults.pageToken,});rows=rows.concat(queryResults.rows);}if(!rows){console.log("No rows returned.");return;}constspreadsheet=SpreadsheetApp.create("BigQuery Results");constsheet=spreadsheet.getActiveSheet();// Append the headers.constheaders=queryResults.schema.fields.map((field)=>field.name);sheet.appendRow(headers);// Append the results.constdata=newArray(rows.length);for(leti=0;i <rows.length;i++){constcols=rows[i].f;data[i]=newArray(cols.length);for(letj=0;j <cols.length;j++){data[i][j]=cols[j].v;}}sheet.getRange(2,1,rows.length,headers.length).setValues(data);console.log("Results spreadsheet created: %s",spreadsheet.getUrl());}

/** * Loads a CSV into BigQuery */functionloadCsv(){// Replace this value with the project ID listed in the Google// Cloud Platform project.constprojectId="XXXXXXXX";// Create a dataset in the BigQuery UI (https://bigquery.cloud.google.com)// and enter its ID below.constdatasetId="YYYYYYYY";// Sample CSV file of Google Trends data conforming to the schema below.// https://docs.google.com/file/d/0BwzA1Orbvy5WMXFLaTR1Z1p2UDg/editconstcsvFileId="0BwzA1Orbvy5WMXFLaTR1Z1p2UDg";// Create the table.consttableId=`pets_${newDate().getTime()}`;lettable={tableReference:{projectId:projectId,datasetId:datasetId,tableId:tableId,},schema:{fields:[{name:"week",type:"STRING"},{name:"cat",type:"INTEGER"},{name:"dog",type:"INTEGER"},{name:"bird",type:"INTEGER"},],},};try{table=BigQuery.Tables.insert(table,projectId,datasetId);console.log("Table created: %s",table.id);}catch(err){console.log("unable to create table");}// Load CSV data from Drive and convert to the correct format for upload.constfile=DriveApp.getFileById(csvFileId);constdata=file.getBlob().setContentType("application/octet-stream");// Create the data upload job.constjob={configuration:{load:{destinationTable:{projectId:projectId,datasetId:datasetId,tableId:tableId,},skipLeadingRows:1,},},};try{constjobResult=BigQuery.Jobs.insert(job,projectId,data);console.log(`Load job started. Status:${jobResult.status.state}`);}catch(err){console.log("unable to insert job");}}