1、使用App Script编写脚本来优化查询。通过使用App Script，可以将大数据量查询分段，并使用锁定以减少带宽占用量。

以下是使用App Script查询BigQuery并将结果写入Google Sheets的代码示例：

function runQuery() {
  var projectId = 'YOUR_PROJECT_ID';
  var request = {
    query: 'SELECT * FROM `bigquery-public-data.samples.gsod` LIMIT 10;'
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

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

  // Get all the rows of results.
  var rows = queryResults.getRows();

  // Print column headers.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headers = queryResults.getSchema().getFields();
  for (var i = 0; i < headers.length; i++) {
    sheet.getRange(1, i + 1).setValue(headers[i].name);
  }

  // Print rows of results.
  for (var rowNum = 0; rowNum < rows.length; rowNum++) {
    var values = rows[rowNum].f;
    for (var colNum = 0; colNum < headers.length; colNum++) {
      var cell = sheet.getRange(rowNum + 2, colNum + 1);
      cell.setValue(values[colNum].v);
    }
  }
}

2、使用Google Sheets的数据提取功能。将最近查询的结果存储到Google Sheets中，然后在新的查询中引用这些结果。

下面是一个使用数据提取功能的示例，它将BigQuery中存储的数据提取到Google Sheets中，并通过App Script将数据刷新成最新的数据：

function refreshData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

  // Get the current values.
  var currentValues = sheet.getDataRange().getValues();

  // Fetch the latest data from your BigQuery table.
  // `newData` should be a `2D array` of values.
  var newData = fetchNewData();

  // Replace the `currentValues` with `newData` if `currentValues` isn't empty.
  if (currentValues.length > 1) {