0

I call the below function to dynamically reset the IDs of task, sub-task, and milestone entries, after I've inserted new task, sub-task, and milestone entries. It seems like a very straight forward function, but it lags when being executed. Any ideas on how to make this run faster?

function ResetIDs() {
  // init vars
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var entryType, iterateRow;
  var taskID = 0, subTaskID = 1, milestoneID = 1;

  // loop through all elements and update IDs accordingly
  iterateRow = FIRSTROW;
  while (iterateRow <= spreadsheet.getLastRow()) {
    entryType = spreadsheet.getRange('A'+iterateRow).getValue();
    switch (entryType){
      case entryType.match("^Task*")?.input:
        taskID++;
        spreadsheet.getRange('B'+iterateRow).setValue(taskID);
        subTaskID = 1;
        iterateRow += 2;
        break;
      case entryType.match("^Sub-Task*")?.input:
        spreadsheet.getRange('B'+iterateRow).setValue(taskID + '.' + subTaskID);
        subTaskID++;
        iterateRow += 2;
        break;
      case entryType.match("^Milestone*")?.input:
        spreadsheet.getRange('B'+iterateRow).setValue("M"+milestoneID);
        milestoneID++;
        subTaskID = 1;
        iterateRow++;
        break;
    }
  }
  Logger.log("reset task and sub-task IDs");
}

Thanks!

Session Mwamufiya
  • 1
  • 1
  • 2
    I would suggest instead of calling the `getRange` again n again in while loop, get all the data you need for processing and do the processing accordingly store the values in array and use `setValues` to update the data all at once. – vector Feb 21 '22 at 09:44

0 Answers0