I循环for formulas.push(rowFormulas)不工作

Question

下面的代码是从不同的日历中提取数据，并使用formulas.push(rowFormulas)在工作表中编写公式。我遇到的问题是，我不知道如何为脚本中的最后一行执行循环。每次用户更改时，lastRow都会在第8行重新启动，而不是继续。有人能帮我纠正我的循环吗？非常感谢

function export_gcal_to_gsheetLast8(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extraction - Principal");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin"); //Sheet name where you will have the list of calendar ID's, startDate and endDate
var lastRow = sheet.getLastRow()+8;
  sheet.clear() // If you'll be runnning the script several times, the data will be duplicated so I added this line to clear the sheet before adding the data
  // other option would be to create a script to check if data already exists before adding it to the sheet

  // Set filters
  var startDate = sheet2.getRange('k15').getValue(); //Range for startDate
  var endDate = sheet2.getRange('k16').getValue(); //Range for endDate
  var users = sheet2.getRange('b3:B').getValues(); //Range where you have the calendar ID's

  // Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
  // of the getRange entry below
  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++){
    //here we do the things we do once per calander
   
    if (users[j] == ""){
      break;
    }
    
    else{
      var cal = CalendarApp.getCalendarById(users[j]);
      var events = cal.getEvents(startDate, endDate);
   

      // Loop through all calendar events found and write them out starting on the next empty row
      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(b' +lastRow+';5))+(MINUTE(RIGHT(b' +lastRow+ ';5))/60))-(HOUR(LEFT(b' +lastRow+ ';5))+(MINUTE(LEFT(b' +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)'
        ]
        lastRow = lastRow +1;
        formulas.push(rowFormulas)
      
      }
   }
  
  }

  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,sheet.getLastRow()).setNumberFormat('.00');

}

Answer 1

我不得不将var lastRow = sheet.getLastRow()+8移出循环，因为每个循环都重新初始化了lastRow。(我还必须将lastRow初始化为8：var lastRow = 8)

以下是正确的脚本：

function export_gcal_to_gsheetPrincipal(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Extraction - Principal");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Id Calendriers - Dates Debut et Fin"); //Sheet name where you will have the list of calendar ID's, startDate and endDate
  var lastRow = 8;
  sheet.clear() // If you'll be runnning the script several times, the data will be duplicated so I added this line to clear the sheet before adding the data
  // other option would be to create a script to check if data already exists before adding it to the sheet

  // Set filters
  var startDate = sheet2.getRange('k1').getValue(); //Range for startDate
  var endDate = sheet2.getRange('k2').getValue(); //Range for endDate
  var users = sheet2.getRange('b3:B').getValues(); //Range where you have the calendar ID's

  // Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
  // of the getRange entry below
  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++){
    //here we do the things we do once per calander
   
    if (users[j] == ""){
      break;
    }
    
    else{
      var cal = CalendarApp.getCalendarById(users[j]);
      var events = cal.getEvents(startDate, endDate);
   

      // Loop through all calendar events found and write them out starting on the next empty row
      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(b' +lastRow+';5))+(MINUTE(RIGHT(b' +lastRow+ ';5))/60))-(HOUR(LEFT(b' +lastRow+ ';5))+(MINUTE(LEFT(b' +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)'
        ]
        lastRow = lastRow +1;
        formulas.push(rowFormulas)
      
      }
   }
  
  }

  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,sheet.getLastRow()).setNumberFormat('.00');

}