如何获取连接BigQuery的Google Sheet的最后行与列？

连接BigQuery的数据源工作表限制了getLastRow()、getLastColumn()这类方法的使用，下面提供两种可靠的解决思路：

方案1：直接从BigQuery元数据获取列数 #

既然工作表是和BigQuery表绑定的，最准确的方式是通过BigQuery API读取表的元数据来获取列数，完全避开Sheets的受限方法。

操作步骤： #

1. 在App Script编辑器中，点击顶部菜单「扩展程序」→「Apps Script」，进入脚本编辑器后，点击「服务」→「添加服务」，找到BigQuery API并启用。
2. 添加以下函数获取BigQuery表的列数：

function getBQTableColumnCount(projectId, datasetId, tableId) {
  const table = BigQuery.Tables.get(projectId, datasetId, tableId);
  return table.schema.fields.length;
}

3. 给每个工作表映射对应的BigQuery表信息：

function getBQTableInfo(sheetName) {
  // 替换成你实际的GCP项目、数据集、表名
  return {
    'dataset1': {project: '你的GCP项目ID', dataset: '数据集ID', table: '表ID'},
    'dataset2': {project: '你的GCP项目ID', dataset: '数据集ID', table: '表ID'}
  }[sheetName];
}

4. 修改原代码中获取行列的逻辑：
   • 用上面的函数获取列数
   • 行数可以通过读取一个足够大的范围（比如10000行），过滤掉空行后得到实际行数

方案2：逐列探测最后一列（适合小型数据集） #

如果无法启用BigQuery API，可以从左到右逐列检查是否有数据，直到找到最后一列：

function getLastColumnForDataSourceSheet(sheet) {
  let lastCol = 1;
  // 根据你的数据情况设置最大列数上限
  const maxColumns = 100;
  while (lastCol <= maxColumns) {
    // 读取当前列的前10行样本，判断是否有非空内容
    const sampleRange = sheet.getRange(1, lastCol, 10);
    const sampleValues = sampleRange.getValues();
    const hasData = sampleValues.some(row => row[0] !== '');
    if (!hasData) break;
    lastCol++;
  }
  return lastCol - 1;
}

在原代码中直接调用这个函数替换sheet.getLastColumn()即可。

修改后的完整代码（方案1示例） #

const idTargetSpreadSheet = "SECRET";
const targetInitialLine = 3;

function collectDataToReport() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = spreadSheet.getSheets().map(s => s.getName());
  const targetSpreadSheet = SpreadsheetApp.openById(idTargetSpreadSheet);

  allSheets.forEach(sheetName => {
    if (sheetName == 'zero') return;

    Logger.log(`Copying data from ${sheetName}...`);
    const sheet = spreadSheet.getSheetByName(sheetName);

    try {
      const bqInfo = getBQTableInfo(sheetName);
      if (!bqInfo) {
        Logger.log(`No BigQuery info mapped for ${sheetName}`);
        return;
      }

      // 获取列数
      const lastColumn = getBQTableColumnCount(bqInfo.project, bqInfo.dataset, bqInfo.table);
      // 获取行数：读取大范围后过滤空行
      const maxRows = 10000;
      const tempRange = sheet.getRange(1, 1, maxRows, lastColumn);
      const allValues = tempRange.getValues();
      const filteredData = allValues.filter(row => row.some(cell => cell !== ''));
      const lastRow = filteredData.length;

      if (lastRow === 0) {
        Logger.log(`No data found in ${sheetName}`);
        return;
      }

      // 排序数据
      const sortedColumn = getSortedColumn(sheetName);
      filteredData.sort((a, b) => b[sortedColumn] - a[sortedColumn]);

      // 写入目标表格
      const targetSheet = targetSpreadSheet.getSheetByName(convertSheetName(sheetName));
      const targetRange = targetSheet.getRange(targetInitialLine, 1, lastRow, lastColumn);
      targetRange.setValues(filteredData);

    } catch(err) {
      Logger.log(`Error processing ${sheetName}: ${err}`);
    }
  })
}

function getBQTableColumnCount(projectId, datasetId, tableId) {
  const table = BigQuery.Tables.get(projectId, datasetId, tableId);
  return table.schema.fields.length;
}

function getBQTableInfo(sheetName) {
  return {
    'dataset1': {project: 'your-gcp-project-id', dataset: 'your-dataset', table: 'your-table'},
    'dataset2': {project: 'your-gcp-project-id', dataset: 'your-dataset', table: 'your-table'}
  }[sheetName];
}

function convertSheetName(sheetName) {
  return {
    'dataset1': 'newSheetName1',
    'dataset2': 'newSheetName2'
  }[sheetName];
}

function getSortedColumn(sheetName) {
  return {
    'dataset1': 5,
    'dataset2': 5
  }[sheetName];
}

内容的提问来源于stack exchange，提问作者dfop02