My deleteRows trigger is affecting my formula in Google Sheets-CodePudding

I have a deleteRow trigger in my Google Sheet, but after it is executed is changes the range of my formula.

The deleteRow trigger:

function deleteRows() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Daily Attendance - inAcc'); 
   var start, howManyToDelete; 
   start = 2;
   howManyToDelete = sheet.getLastRow() - start   1;
   sheet.deleteRows(start, howManyToDelete);
}

The formula which is affected:

How it should read:

={"TRIP 1 TIME";ARRAYFORMULA(IF(ISBLANK(D2:D),"",D2:D - C2:C))}

How it is reading after the trigger is executed:

={"TRIP 1 TIME";ARRAYFORMULA(IF(ISBLANK(D501:D),"",D501:D - C501:C))}

Any suggestions to avoid this problem?

CodePudding user response：

you can write back the formula using scripts, try below:

Assuming the formula is entered in cell E1, change per your need

function deleteRows() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Daily Attendance - inAcc'); 
   var start, howManyToDelete; 
   start = 2;
   howManyToDelete = sheet.getLastRow() - start   1;
   sheet.deleteRows(start, howManyToDelete);
   sheet.getRange('E1').setValue('={"TRIP 1 TIME";ARRAYFORMULA(IF(ISBLANK(D2:D),"",D2:D - C2:C))}')
   
}