Google Apps Script无法定位目标单元格，求每日自动下移取数修复方案

需求背景 #

开发Google Apps Script实现：从电子表格A逐行提取数据（每次取上一次取数单元格的下方单元格），每日或定时自动写入电子表格B，但当前脚本无法找到目标单元格，问题未解决。

原脚本代码 #

function moveImportedData() {
  // Open the source and target spreadsheets by their IDs and get the specific sheets.
  var sourceSheet = SpreadsheetApp.openById('1hoyVaEtnff-XqkVzSahi0l_sFhxO0TAnwOGhDg9mg1c').getSheetByName('Pew');
  var targetSheet = SpreadsheetApp.openById('1iIQf6VYTlG9ANKBkYnXsP_e1dupS9vgZcUye5DQzki0').getSheetByName('October')

  // Get the current date.
  var currentDate = new Date();

  // Get the script properties, which are used to store data between script runs.
  var scriptProperties = PropertiesService.getScriptProperties();

  // Get the initial offset from script properties, default to 42 if not set.
  var initialOffset = scriptProperties.getProperty('offset');
  if (!initialOffset) {
    initialOffset = 3;
  }

  // Calculate the day number based on the difference between the current date and January 1, 2023, in milliseconds.
  var dayNumber = Math.floor((currentDate - new Date("2023-01-01")) / (1000 * 60 * 60 * 24)) + initialOffset;

  // Construct the cell reference in the source sheet based on the calculated day number.
  var sourceCellReference = 'D' + dayNumber;

  // Get the data from the source cell in the source sheet.
  var cellValue = sourceSheet.getRange(sourceCellReference).getValue();

  // Check if the cell contains a value.
  if (cellValue !== "") {
    // Check the data type of the cell value (string or number).
    if (typeof cellValue === 'string' || typeof cellValue === 'number') {
      // Set the imported data into a specific cell in the target sheet.
      targetSheet.getRange('C4').setValue(cellValue);

      // Increment the offset for the next script run.
      scriptProperties.setProperty('offset', parseInt(initialOffset) + 1);
    } else {
      // Log a message if the cell value is not a string or number.
      Logger.log("Source cell contains unsupported data type. No data imported.");
    }
  } else {
    // Log a message if the source cell is empty.
    Logger.log("Source cell is empty. No data imported.");
  }
}

问题根源分析 #

1. 日期计算逻辑偏差：通过当前日期与2023-01-01的差值计算行号，再叠加offset，会导致行号快速超出工作表实际行数，引用不存在的单元格。
2. 初始化逻辑矛盾：注释标注默认offset为42，但代码实际设为3，可能导致初始取数位置错误。
3. 缺乏单元格存在性校验：直接调用getRange()引用计算出的单元格，若行号超出工作表范围，会直接抛出异常终止脚本。

修复后的脚本 #

function moveImportedData() {
  // 打开源表和目标表
  const sourceSheet = SpreadsheetApp.openById('1hoyVaEtnff-XqkVzSahi0l_sFhxO0TAnwOGhDg9mg1c').getSheetByName('Pew');
  const targetSheet = SpreadsheetApp.openById('1iIQf6VYTlG9ANKBkYnXsP_e1dupS9vgZcUye5DQzki0').getSheetByName('October');
  
  // 获取脚本属性（存储上次取数的行号）
  const scriptProps = PropertiesService.getScriptProperties();
  // 上次取数的行号，默认从第3行开始（对应D3）
  let lastRow = parseInt(scriptProps.getProperty('lastSourceRow')) || 3;
  
  // 校验行号是否在源表有效范围内
  const maxSourceRows = sourceSheet.getLastRow();
  if (lastRow > maxSourceRows) {
    Logger.log('已到达源表数据末尾，无可用单元格');
    return;
  }
  
  // 获取源表目标单元格数据
  const sourceCell = sourceSheet.getRange('D' + lastRow);
  const cellValue = sourceCell.getValue();
  
  // 校验数据有效性
  if (cellValue === "" || cellValue === null) {
    Logger.log(`源表D${lastRow}单元格为空，未导入数据`);
    return;
  }
  if (typeof cellValue !== 'string' && typeof cellValue !== 'number') {
    Logger.log(`源表D${lastRow}单元格数据类型不支持，未导入数据`);
    return;
  }
  
  // 将数据写入目标表C4（若需逐行写入目标表，可修改此处逻辑）
  targetSheet.getRange('C4').setValue(cellValue);
  
  // 更新存储的行号，下次取数取下一行
  scriptProps.setProperty('lastSourceRow', lastRow + 1);
}

关键修改说明 #

• 简化取数逻辑：直接存储上次取数的行号，确保每次执行都取下一行，避免日期偏差导致的行号错误。
• 增加有效性校验：先检查行号是否在源表有效行数内，避免引用不存在的单元格。
• 统一初始化逻辑：明确默认取数起始行，注释与代码保持一致。
• 优化变量命名：提升代码可读性与维护性。

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