How can I improve my script to make it faster when search from multiple sheets?-CodePudding

So this is my searching code in App script. I used it on My google sheet but sometimes it have to wait so long before I have the result from the script. I'm not sure how to make it faster.

function search_all(){
  const all = ["Sheet1","Sheet2"];
  const wsSheetForm = SpreadsheetApp.getActive().getSheetByName('SheetForm');

  for(var i in all){
  const wsPO = SpreadsheetApp.openById("sheetID").getSheetByName(all[i]);
  const searchCell = wsSheetForm.getRange("H23").getDisplayValue();
  const values = wsPO.getDataRange().getDisplayValues();
 
   for(var n=0; n<values.length; n  ){
      var rowValue = values[n];

      if(rowValue[1] === searchCell && rowValue[5] === '1' && rowValue[25] == "0.00"){
        wsSheetForm.getRange("H22").setValue("N")
        wsSheetForm.getRange("C4").setValue(rowValue[10])
        wsSheetForm.getRange("H24").setValue(rowValue[2])
        wsSheetForm.getRange("C27:E27").setValue(rowValue[3])
        wsSheetForm.getRange("C28").setValue(rowValue[4])
        wsSheetForm.getRange("H27").setValue(rowValue[11])
        wsSheetForm.getRange("J27").setValue(rowValue[13])
        wsSheetForm.getRange("J28").setValue(rowValue[14])
        wsSheetForm.getRange("J74").setValue(rowValue[23])
        return;}

      if(rowValue[1] === searchCell && rowValue[5] === '1' && rowValue[25] != "0.00"){
        wsSheetForm.getRange("H22").setValue("V")
        wsSheetForm.getRange("C4").setValue(rowValue[10])
        wsSheetForm.getRange("H24").setValue(rowValue[2])
        wsSheetForm.getRange("C27:E27").setValue(rowValue[3])
        wsSheetForm.getRange("C28").setValue(rowValue[4])
        wsSheetForm.getRange("H27").setValue(rowValue[11])
        wsSheetForm.getRange("J27").setValue(rowValue[13])
        wsSheetForm.getRange("J28").setValue(rowValue[14])
        wsSheetForm.getRange("J74").setValue(rowValue[23])
        return;}
    }}};

I try to find the way to make it faster but not sure how gonna do this.

CodePudding user response：

The code you show seems fine. Chances are that the bad performance you mention is caused by the spreadsheet rather than the script. To improve spreadsheet performance, see these optimization tips.

You can make the code a bit better structured this way:

function search_all() {
  const sheetRegex = /^(Sheet1|Sheet2)$/i;
  const formSheet = SpreadsheetApp.getActive().getSheetByName('SheetForm');
  const searchFor = formSheet.getRange('H23').getDisplayValue();
  const processRow = (row) => {
    if (row[1] !== searchFor || row[5] !== '1') {
      return false;
    }
    copyRowToForm_(row, formSheet);
    return true;
  }
  SpreadsheetApp.getActive().getSheets()
    .filter(sheet => sheet.getName().match(sheetRegex))
    .some(sheet =>
      sheet.getDataRange().getDisplayValues().some(processRow)
    );
}

function copyRowToForm_(row, formSheet) {
  formSheet.getRange('H22').setValue(row[25] === '0.00' ? 'N' : 'V');
  formSheet.getRange('C4').setValue(rowValue[10]);
  formSheet.getRange('H24').setValue(rowValue[2]);
  formSheet.getRange('C27:E27').setValue(rowValue[3]);
  formSheet.getRange('C28').setValue(rowValue[4]);
  formSheet.getRange('H27').setValue(rowValue[11]);
  formSheet.getRange('J27').setValue(rowValue[13]);
  formSheet.getRange('J28').setValue(rowValue[14]);
  formSheet.getRange('J74').setValue(rowValue[23]);
}