自动提取

Question

我正在寻找一种方法来自动化使用查询从一个大型查询表中创建一个提取的过程。我会使用gsheet应用程序脚本还是python库？这个是可能的吗？

Answer 1

根据注释和可以完成的内容，app可以选择添加"Advance“(包括BigQuery API )，您可以查看指南以及它在这个BigQuery服务引用上的工作方式。

文档中的示例代码：

​

/**
 * Runs a BigQuery query and logs the results in a spreadsheet.
 */
function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  const projectId = 'XXXXXXXX';

  const request = {
    // TODO (developer) - Replace query with yours
    query: 'SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +
      'FROM `publicdata.samples.shakespeare` WHERE LENGTH(word) > 10;',
    useLegacySql: false
  };
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  let rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (!rows) {
    Logger.log('No rows returned.');
    return;
  }
  const spreadsheet = SpreadsheetApp.create('BiqQuery Results');
  const sheet = spreadsheet.getActiveSheet();

  // Append the headers.
  const headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (let i = 0; i < rows.length; i++) {
    const cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (let j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }
  sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s',
      spreadsheet.getUrl());
}

​

还有一种方法可以利用注释中的特性连接片。关于如何实施它，有一步一步的指导。它应该给您一个洞察或想法，如何使用大查询API或工作表与应用程序脚本。