Question

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

Answer 1

更新：我们刚刚添加了一个新的BigQuery+Apps脚本教程，它应该会引导您在这里找到这个问题的答案：https://developers.google.com/apps-script/articles/bigquery_tutorial一

@GQuery：我们最近更新了AppsScript，以访问最新的BigQuery API版本（v2）。这里有一个简单的开始示例，将在AppScript日志中显示结果。我们正在对AppScript/BigQuery文档进行更新。

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() +  	 ;
    }
    Logger.log(rowString);

Answer 2

I dont have the reputation to comment on hurricaneditka16. Therefore, I posted this answer: This line

queryResults = BigQuery.Jobs.query(projectId, sql);

应替换为

.query(
    resource,
    projectId);

资源是对您之前使用的sql的轻微转换。尝试这种转换，它就会奏效。

function getResource(sql) {
  var resource =  {"query": "sql"} 
  resource = resource.replace( sql , sql);
  return resource
}

友情链接