Stay organized with collections
Save and categorize content based on your preferences.
The BigQuery service allows you to use the
Google BigQuery API in Apps Script. This API
gives users the ability to manage their BigQuery projects, upload new data,
and execute queries.
Reference
For detailed information on this service, see the
reference documentation for the BigQuery API.
Like all advanced services in Apps Script, the BigQuery service uses the same
objects, methods, and parameters as the public API. For more information, see How method signatures are determined.
/** * 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(function(field){returnfield.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());}
Load CSV data
This sample creates a new table and loads a CSV file from Google Drive into it.
/** * 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');}}
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-18 UTC."],[[["\u003cp\u003eThe BigQuery service in Apps Script enables management of BigQuery projects, data uploads, and query execution using the Google BigQuery API.\u003c/p\u003e\n"],["\u003cp\u003eThis advanced service requires prior enabling before use and leverages the same structure as the public API.\u003c/p\u003e\n"],["\u003cp\u003eSample code is provided demonstrating how to run a query to retrieve Google Search terms and load CSV data into BigQuery.\u003c/p\u003e\n"],["\u003cp\u003eUsers can consult the Google Cloud support guide for troubleshooting and support related to the BigQuery service.\u003c/p\u003e\n"]]],[],null,["The BigQuery service allows you to use the\n[Google BigQuery API](/bigquery/docs/developers_guide) in Apps Script. This API\ngives users the ability to manage their BigQuery projects, upload new data,\nand execute queries.\n| **Note:** This is an advanced service that must be [enabled before use](/apps-script/guides/services/advanced).\n\nReference\n\nFor detailed information on this service, see the\n[reference documentation](/bigquery/docs/reference/v2) for the BigQuery API.\nLike all advanced services in Apps Script, the BigQuery service uses the same\nobjects, methods, and parameters as the public API. For more information, see [How method signatures are determined](/apps-script/guides/services/advanced#how_method_signatures_are_determined).\n\nTo report issues and find other support, see the\n[Google Cloud support guide](https://cloud.google.com/support/).\n\nSample code\n\nThe sample code below uses [version 2](/bigquery/docs/reference/v2) of the API.\n\nRun query\n\nThis sample queries a list of the daily top Google Search terms. \nadvanced/bigquery.gs \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs) \n\n```gosu\n/**\n * Runs a BigQuery query and logs the results in a spreadsheet.\n */\nfunction runQuery() {\n // Replace this value with the project ID listed in the Google\n // Cloud Platform project.\n const projectId = 'XXXXXXXX';\n\n const request = {\n // TODO (developer) - Replace query with yours\n query: 'SELECT refresh_date AS Day, term AS Top_Term, rank ' +\n 'FROM `bigquery-public-data.google_trends.top_terms` ' +\n 'WHERE rank = 1 ' +\n 'AND refresh_date \u003e= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK) ' +\n 'GROUP BY Day, Top_Term, rank ' +\n 'ORDER BY Day DESC;',\n useLegacySql: false\n };\n let queryResults = BigQuery.Jobs.query(request, projectId);\n const jobId = queryResults.jobReference.jobId;\n\n // Check on status of the Query Job.\n let sleepTimeMs = 500;\n while (!queryResults.jobComplete) {\n Utilities.sleep(sleepTimeMs);\n sleepTimeMs *= 2;\n queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);\n }\n\n // Get all the rows of results.\n let rows = queryResults.rows;\n while (queryResults.pageToken) {\n queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {\n pageToken: queryResults.pageToken\n });\n rows = rows.concat(queryResults.rows);\n }\n\n if (!rows) {\n console.log('No rows returned.');\n return;\n }\n const spreadsheet = SpreadsheetApp.create('BigQuery Results');\n const sheet = spreadsheet.getActiveSheet();\n\n // Append the headers.\n const headers = queryResults.schema.fields.map(function(field) {\n return field.name;\n });\n sheet.appendRow(headers);\n\n // Append the results.\n const data = new Array(rows.length);\n for (let i = 0; i \u003c rows.length; i++) {\n const cols = rows[i].f;\n data[i] = new Array(cols.length);\n for (let j = 0; j \u003c cols.length; j++) {\n data[i][j] = cols[j].v;\n }\n }\n sheet.getRange(2, 1, rows.length, headers.length).setValues(data);\n\n console.log('Results spreadsheet created: %s', spreadsheet.getUrl());\n}\n```\n\nLoad CSV data\n\nThis sample creates a new table and loads a CSV file from Google Drive into it. \nadvanced/bigquery.gs \n[View on GitHub](https://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs) \n\n```gosu\n/**\n * Loads a CSV into BigQuery\n */\nfunction loadCsv() {\n // Replace this value with the project ID listed in the Google\n // Cloud Platform project.\n const projectId = 'XXXXXXXX';\n // Create a dataset in the BigQuery UI (https://bigquery.cloud.google.com)\n // and enter its ID below.\n const datasetId = 'YYYYYYYY';\n // Sample CSV file of Google Trends data conforming to the schema below.\n // https://docs.google.com/file/d/0BwzA1Orbvy5WMXFLaTR1Z1p2UDg/edit\n const csvFileId = '0BwzA1Orbvy5WMXFLaTR1Z1p2UDg';\n\n // Create the table.\n const tableId = 'pets_' + new Date().getTime();\n let table = {\n tableReference: {\n projectId: projectId,\n datasetId: datasetId,\n tableId: tableId\n },\n schema: {\n fields: [\n {name: 'week', type: 'STRING'},\n {name: 'cat', type: 'INTEGER'},\n {name: 'dog', type: 'INTEGER'},\n {name: 'bird', type: 'INTEGER'}\n ]\n }\n };\n try {\n table = BigQuery.Tables.insert(table, projectId, datasetId);\n console.log('Table created: %s', table.id);\n } catch (err) {\n console.log('unable to create table');\n }\n // Load CSV data from Drive and convert to the correct format for upload.\n const file = DriveApp.getFileById(csvFileId);\n const data = file.getBlob().setContentType('application/octet-stream');\n\n // Create the data upload job.\n const job = {\n configuration: {\n load: {\n destinationTable: {\n projectId: projectId,\n datasetId: datasetId,\n tableId: tableId\n },\n skipLeadingRows: 1\n }\n }\n };\n try {\n const jobResult = BigQuery.Jobs.insert(job, projectId, data);\n console.log(`Load job started. Status: ${jobResult.status.state}`);\n } catch (err) {\n console.log('unable to insert job');\n }\n}\n```"]]