使用Google Spreadsheet访问BigQuery [英] Accessing BigQuery with Google Spreadsheet
问题描述
我在网上查看了解如何在Google电子表格中导入bigquery数据。
我找到了这个appscript示例,但它没有网络API不在同一级别,而且我也没有找到如何在API中使用API2或API @ beta1进行查询。
I look on the internet to see how can I import bigquery data inside google spreadsheet. I found this appscript sample, but it doesn'twork API are not at the same level, and I do not find how to query with API2 or API@beta1 in appscript.
function runQuery() {
var ss = SpreadsheetApp.getActive();
var range = ss.getRangeByName('query');
var query = range.getCell(1, 1).getValue();
//var results = bigquery.query(query);
var header = ss.getRangeByName('header');
header.clearContent();
var output = ss.getRangeByName('output');
output.clearContent();
for (var i = 0; i < results.fields.length; i++) {
var field = results.fields[i];
header.getCell(1, 1 + i).setValue(field.id);
}
for (var i = 0; i < results.rows.length; i++) {
var row = results.rows[i].f;
for (var j = 0; j < row.length; ++j) {
output.getCell(1 + i, 1 + j).setValue(row[j].v);
}
}
}
感谢您的想法,
GQ
推荐答案
一个新的BigQuery + Apps脚本教程,应该引导您在这里回答这个问题: https://developers.google.com/apps-script/articles/bigquery_tutorial
@GQuery:我们最近更新了AppsScript以拥有访问最新的BigQuery API版本(v2)。这里有一个简单的例子来开始,将在AppScript日志中显示结果。我们正在对AppScript / BigQuery文档进行更新。
@GQuery: We've very recently updated AppsScript to have access to the latest BigQuery API version (v2). Here's a simple example to get started, will display results in the AppScript log. We are working on an update to the AppScript/BigQuery documentation.
function runQuery() {
var projectId = 'YOUR PROJECT';
var sql = 'select word, word_count from publicdata:samples.shakespeare limit 100';
var queryResults;
// Run the query
try {
queryResults = BigQuery.Jobs.query(projectId, sql);
}
catch (err) {
Logger.log(err);
return;
}
// Loop until successful job completion
while (queryResults.getJobComplete() == false) {
try {
queryResults = BigQuery.Jobs.getQueryResults(projectId, queryResults.getJobReference().getJobId());
}
catch (err) {
Logger.log(err);
return;
}
}
var tableRows = queryResults.getRows();
for (var i = 0; i < tableRows.length; i++) {
var rowString = '';
var cols = tableRows[i].getF();
for (var j = 0; j < cols.length; j++) {
rowString += cols[j].getV() + '\t';
}
Logger.log(rowString);
这篇关于使用Google Spreadsheet访问BigQuery的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!