Google Apps Script写入公式循环lastRow重置问题修复

原代码的行号计数逻辑有3处核心错误，导致公式引用行号和实际数据行错位：

• lastRow初始化时机错误：先读取工作表最后行号计算lastRow，之后才执行sheet.clear()清空工作表，清空后原有行号完全失效，初始值无意义
• lastRow初始值计算逻辑错误：表头固定写入第7行，所有事件数据统一从第8行开始写入，不需要基于清空前的工作表行号计算初始值
• 日历ID读取逻辑缺陷：getValues()返回二维数组，原代码直接判断一维数组是否为空字符串永远不生效，会遍历到空行触发日历读取报错，间接打断计数逻辑

function export_gcal_to_gsheetLast8(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extraction - Principal");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin");
  // 先清空工作表，再初始化行号计数：第一行数据固定从第8行开始（第7行是表头）
  sheet.clear();
  var lastRow = 8;

  // 读取筛选参数
  var startDate = sheet2.getRange('k15').getValue();
  var endDate = sheet2.getRange('k16').getValue();
  var users = sheet2.getRange('b3:B').getValues();

  const data = [];
  const formulas = [];
  const headers = [["Titre", "Description", "Location", "Début", "Fin", "Heures effectives","Extraction 2","Extraction 3","Heures Planifiées", "Vacances", "Maladie","Congé légal", "Absence"]]

  for (var j = 0; j < users.length; j++){
    // 修复二维数组取值问题，正确读取日历ID
    var calendarId = users[j][0];
    if (calendarId == ""){
      break;
    }

    var cal = CalendarApp.getCalendarById(calendarId);
    var events = cal.getEvents(startDate, endDate);

    // 遍历当前日历所有事件
    for (var i = 0; i < events.length; i++) {
      var details = [
        events[i].getTitle(), 
        events[i].getDescription(), 
        events[i].getLocation(), 
        events[i].getStartTime(), 
        events[i].getEndTime()
      ];
      data.push(details);

      // 用当前行号生成公式，再递增行号给下一条数据用
      const rowFormulas = [
        '=(HOUR(RIGHT(E' +lastRow+';5))+(MINUTE(RIGHT(E' +lastRow+ ';5))/60))-(HOUR(LEFT(D' +lastRow+ ';5))+(MINUTE(LEFT(D' +lastRow+ ';5))/60))',
        '=IFERROR(TEXT(INDEX(SPLIT(A'+lastRow+';" ");2);"hh:mm");"")',
        '=IFERROR(TEXT(INDEX(SPLIT(A'+lastRow+';" ");3);"hh:mm");"")',
        '=IF(OR(G'+lastRow+'="Maladie";G'+lastRow+'="Congé";G'+lastRow+'="Absence";G'+lastRow+'="00:00";G'+lastRow+'="Vacances");0;(HOUR(H'+lastRow+')+(MINUTE(H'+lastRow+')/60))-(HOUR(G'+lastRow+')+(MINUTE(G'+lastRow+')/60)))',
        '=IF(IFNA(VLOOKUP(D'+lastRow+'; feries;1;FALSE);1)<>1;0;IF(AND(G'+lastRow+'="00:00";H'+lastRow+'="Vacances");0.5;IF(G'+lastRow+'="Vacances";1;0)))',
        '=IF(G'+lastRow+'="Maladie";1;0)',
        '=IF(G'+lastRow+'="Congé";1;0)',
        '=IF(G'+lastRow+'="Absence";1;0)'
      ];
      formulas.push(rowFormulas);
      lastRow = lastRow + 1;
    }
  }

  // 写入表头、数据、公式
  sheet.getRange(7,1,headers.length, headers[0].length).setValues(headers);
  sheet.getRange(8,1,data.length,data[0].length).setValues(data);
  sheet.getRange(8,data[0].length + 1,formulas.length, formulas[0].length).setFormulas(formulas);
  // 修复数字格式设置的范围参数错误
  sheet.getRange(8,6,data.length,1).setNumberFormat('.00');
}

• 把sheet.clear()移到lastRow初始化之前，清空后直接设置初始行号为8，和数据写入起始行完全对齐，切换日历的时候行号会持续顺延不会重置
• 修复了日历ID的读取逻辑，正确读取二维数组里的ID值，空行判断正常生效
• 修正了第一个时长计算公式的列引用错误（原公式错引用B列描述列，改为D列开始时间、E列结束时间计算时长）
• 修复了最后设置数字格式时的范围参数错误，避免范围不匹配报错

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