使用 Google 电子表格访问 BigQuery

问题描述

我在互联网上查看如何在谷歌电子表格中导入 bigquery 数据.我找到了这个 appscript 示例，但它不起作用 API 不在同一级别，而且我在 appscript 中找不到如何使用 API2 或 API@beta1 进行查询.

function runQuery() {var ss = SpreadsheetApp.getActive();var range = ss.getRangeByName('query');var query = range.getCell(1, 1).getValue();//var 结果 = 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 

预先感谢您的想法，

GQ

解决方案

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

@GQuery:我们最近更新了 AppsScript 以访问最新的 BigQuery API 版本 (v2).这是一个简单的入门示例，将在 AppScript 日志中显示结果.我们正在更新 AppScript/BigQuery 文档.

function runQuery() {var projectId = '你的项目';var sql = 'select word, word_count from publicdata:samples.shakespeare limit 100';var 查询结果；//运行查询尝试 {queryResults = BigQuery.Jobs.query(projectId, sql);}抓住(错误){Logger.log(err);返回;}//循环直到作业成功完成while (queryResults.getJobComplete() == false) {尝试 {queryResults = BigQuery.Jobs.getQueryResults(projectId, queryResults.getJobReference().getJobId());}抓住(错误){Logger.log(err);返回;}}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);

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);
    }
  }
}

Thanks in advance for your ideas,

GQ

解决方案

UPDATE: We just added a new BigQuery + Apps Script Tutorial that should walk you through the answer to this question here: https://developers.google.com/apps-script/articles/bigquery_tutorial

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

这篇关于使用 Google 电子表格访问 BigQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！