Sheets API导入带格式表格：未指定列时触发Range not found报错求助

我使用的脚本原本可将表格导入至另一文档，但移除srcRange中的列指定后脚本失效。需求是复制整张表格的格式至目标表格，且原表格的列数会不定期变化。

可正常运行的指定列范围代码 #

const srcSpreadsheetId = "1mVlva8Dyxxxxxxxxxxxxxx"; // 请设置源文档ID
const dstSpreadsheetId = "1a2Eb7fQOxxxxxxxxxxxxxx"; // 请设置目标文档ID
const srcRange = "Database!A:I";
const dstRange = "Database";

// 以序列号形式获取日期对象
const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange);
const sheetId = dstSheet.getSheetId();
Sheets.Spreadsheets.batchUpdate({ requests: [{ repeatCell: { range: { sheetId }, fields: "userEnteredValue" } }] }, dstSpreadsheetId);
Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstRange, { valueInputOption: "USER_ENTERED" });

// 复制数字格式
const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getNumberFormats();
dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);

仅指定表名时的报错代码 #

const srcSpreadsheetId = "1mVlva8Dyxxxxxxxxxxxxxx"; // 请设置源文档ID
const dstSpreadsheetId = "1a2Eb7fQOxxxxxxxxxxxxxx"; // 请设置目标文档ID
const srcRange = "Database";  // <<<<<<<<<<<<<<<<<< 未指定列范围
const dstRange = "Database";

// 以序列号形式获取日期对象
const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange);
const sheetId = dstSheet.getSheetId();
Sheets.Spreadsheets.batchUpdate({ requests: [{ repeatCell: { range: { sheetId }, fields: "userEnteredValue" } }] }, dstSpreadsheetId);
Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstRange, { valueInputOption: "USER_ENTERED" });

// 复制数字格式
const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getNumberFormats();
dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);

运行后持续触发报错：Exception: Range not found

解决方案 #

直接传入表名时，Google Sheets API和SpreadsheetApp的范围解析规则不兼容，导致范围识别失败。需先获取源表的实际数据范围，再执行后续操作。修改后的代码如下：

const srcSpreadsheetId = "1mVlva8Dyxxxxxxxxxxxxxx"; // 请设置源文档ID
const dstSpreadsheetId = "1a2Eb7fQOxxxxxxxxxxxxxx"; // 请设置目标文档ID
const srcSheetName = "Database";
const dstSheetName = "Database";

// 获取源表的实际数据范围
const srcSpreadsheet = SpreadsheetApp.openById(srcSpreadsheetId);
const srcSheet = srcSpreadsheet.getSheetByName(srcSheetName);
const srcDataRange = srcSheet.getDataRange();
const srcRangeA1 = srcDataRange.getA1Notation(); // 格式为 "A:I" 或动态列数对应的范围
const fullSrcRange = `${srcSheetName}!${srcRangeA1}`;

// 以序列号形式获取日期对象
const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, fullSrcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

// 处理目标表
const dstSpreadsheet = SpreadsheetApp.openById(dstSpreadsheetId);
const dstSheet = dstSpreadsheet.getSheetByName(dstSheetName);
const sheetId = dstSheet.getSheetId();

// 清空目标表的现有数据（仅清空源表数据对应的范围，避免影响其他内容）
const clearRange = {
  sheetId: sheetId,
  startRowIndex: 0,
  endRowIndex: values.length,
  startColumnIndex: 0,
  endColumnIndex: values[0]?.length || 0
};
Sheets.Spreadsheets.batchUpdate({ 
  requests: [{ 
    repeatCell: { 
      range: clearRange, 
      fields: "userEnteredValue" 
    } 
  }] 
}, dstSpreadsheetId);

// 更新目标表数据
Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, `${dstSheetName}!${srcRangeA1}`, { valueInputOption: "USER_ENTERED" });

// 复制数字格式
const numberFormats = srcDataRange.getNumberFormats();
dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);

关键修改说明 #

1. 动态获取源表范围：通过getDataRange()自动获取源表中包含数据的最大范围，无需硬编码列数，适配列数变化的场景。
2. 统一范围格式：将获取到的范围转换为Sheet!A:X的标准A1符号格式，确保Sheets API和SpreadsheetApp都能正确识别。
3. 精准清空目标表：清空操作仅针对源表数据对应的范围，避免误删目标表中其他区域的内容。

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