Google sheet + Apps script: faster then for loop

Question

Hi I'm using this code to loop trough another google worksheet find in column 'B' the id number 'idNum' once the id number is found the script replaces the whole row with new data but it seem to be taking a long time before the following script get triggered is there a way to make it faster to loop though or to make the next script trigger faster. here's my code Thanks

  function editRow(){
    
    var mainsheet = ('10_XEaQiR71----- Sheet ID ----uOhi9VVtk5FI')
    var tsheet = SpreadsheetApp.openById(mainsheet).getSheetByName('Data')
    var targetSheet1 = tsheet.getDataRange().getValues()
    var sheet = SpreadsheetApp.getActive();
    var sourceData = sheet.getSheetByName('Data Input').getRange('A2:IB2').getValues()[0];
    var idNum = sheet.getSheetByName('Data Input').getRange ("B2").getValue();
    var copyFrom = sheet.getSheetByName('Data Input').getRange('A2:IB2')
    var data = copyFrom.getValues()
  
    for(var i = 0; i<targetSheet1.length;i++){
      if(targetSheet1[i][1] == idNum){ 
        var row = i=i+1
        tsheet.getRange('A'+row+':IB'+row).setValues(data);
      
         break;
         }
       }
      next script
     }

Answer 1

I believe your goal as follows.

• You want to reduce the process cost of your script in your question.

Issue and workaround:

• In your script, the values are retrieved from getRange("B2") and getRange('A2:IB2'). In this case, I think that you can retrieve them from getRange('A2:IB2').
• When you are using V8 runtime, the process cost of for loop is almost the same with others. Ref So in this case, I would like to propose to use TextFinder instead of the for loop. Because I thought that TextFinder is run in the internal server and by this, the search process might be able to be reduced a little. But I'm not sure about your actual situation. So I'm not sure whether this is the correct direction for achieving your issue. So, please test the following script.

When your script is modified, it becomes as follows.

Modified script:

function editRow() {
  var mainsheet = '10_XEaQiR71----- Sheet ID ----uOhi9VVtk5FI';

  var sheet = SpreadsheetApp.getActive();
  var data = sheet.getSheetByName('Data Input').getRange('A2:IB2').getValues();
  var idNum = data[0][1];

  var tsheet = SpreadsheetApp.openById(mainsheet).getSheetByName('Data');
  var row = tsheet.getRange("B1:B" + tsheet.getLastRow()).createTextFinder(idNum).findNext().getRow();
  tsheet.getRange(row, 1, 1, data[0].length).setValues(data);

  // next script
}

Reference:

• Class TextFinder