BigQuery 服务
使用集合让一切井井有条
根据您的偏好保存内容并对其进行分类。
借助 BigQuery 服务,您可以在 Apps 脚本中使用 Google BigQuery API。借助此 API,用户可以管理其 BigQuery 项目、上传新数据和执行查询。
参考
如需详细了解此服务,请参阅 BigQuery API 的参考文档。与 Apps 脚本中的所有高级服务一样,BigQuery 服务使用的对象、方法和参数均与公共 API 相同。如需了解详情,请参阅方法签名是如何确定的。
如需报告问题并查找其他支持,请参阅 Google Cloud 支持指南。
示例代码
以下示例代码使用 API 的版本 2。
运行查询
此示例查询每日热门 Google 搜索字词列表。
加载 CSV 数据
此示例会创建一个新表,并将 Google 云端硬盘中的 CSV 文件加载到该表中。
如未另行说明,那么本页面中的内容已根据知识共享署名 4.0 许可获得了许可,并且代码示例已根据 Apache 2.0 许可获得了许可。有关详情,请参阅 Google 开发者网站政策。Java 是 Oracle 和/或其关联公司的注册商标。
最后更新时间 (UTC):2025-08-31。
[null,null,["最后更新时间 (UTC):2025-08-31。"],[[["\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,["# BigQuery Service\n\nThe 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---------\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-----------\n\nThe sample code below uses [version 2](/bigquery/docs/reference/v2) of the API.\n\n### Run 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```javascript\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\n### Load 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```javascript\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```"]]