如何通过Apps Script复制BigQuery数据连接器工作表数据？

报错原因 #

BigQuery数据连接器生成的是特殊的数据源工作表（DATASOURCE sheet），这类表是与BigQuery实时联动的只读型数据容器，copyTo()方法属于直接操作工作表范围的动作，不被这类特殊工作表支持，因此会抛出"The action is not supported for DATASOURCE sheet."错误。

修正方案 #

不要直接复制范围，而是先提取数据源工作表中的数据值，再将这些值写入目标工作表。以下是修正后的代码：

function copyData() {
  var sourceSheetNames = ["Connector Sheet 1", "Connector Sheet 2"];
  var targetSheetName = "Sheet1";
  var targetCellStartRow = [3, 16];
  var targetCellStartColumn = 1;
  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = spreadsheet.getSheetByName(targetSheetName);
  
  for (var i = 0; i < sourceSheetNames.length; i++) {
    var sourceSheet = spreadsheet.getSheetByName(sourceSheetNames[i]);
    var sourceData = sourceSheet.getDataRange().getValues(); // 提取数据值
    
    // 计算目标范围的大小，匹配源数据的行数和列数
    var targetRange = targetSheet.getRange(
      targetCellStartRow[i], 
      targetCellStartColumn, 
      sourceData.length, 
      sourceData[0]?.length || 0
    );
    
    targetRange.setValues(sourceData); // 将数据写入目标范围
  }
}

代码说明 #

• getValues()：从数据源工作表中提取所有单元格的原始值，返回一个二维数组，这是数据源工作表支持的操作。
• setValues()：将提取到的二维数组写入目标工作表的指定范围，确保数据完整复制。
• 增加了sourceData[0]?.length || 0的判断，避免源数据为空时出现索引错误。

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