My script has many get and setvalues.I know it make script slower. I want to improve my code but I'm not sure how can I do this. I use this for seach information from another sheet. It take 2-3 min sometime.

var wsformBN = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("formBN");
var wsCustomer = SpreadsheetApp.openById("SheetID").getSheetByName("SheetName");

//////////////

function clearCustomerInfo(){

wsformBN.getRange("C7:E8").clearContent();
wsformBN.getRange("C9:D12").clearContent();
wsformBN.getRange("H7:I11").clearContent();
wsformBN.getRange("H13:I15").clearContent();

}

//////////////

function searchCUtoBN(){
var values = wsCustomer.getDataRange().getDisplayValues();
var valuesFound=false;

for(var i=0 ; i<values.length; i  ){
  var rowValue = values[i];
  var searchCustomerID = wsformBN.getRange("C4").getDisplayValue();

  clearCustomerInfo()

  if(rowValue[2] === searchCustomerID){    

    wsformBN.getRange("C7:E7").setValue(rowValue[3]);
    wsformBN.getRange("C8:E8").setValue(rowValue[4]);
    wsformBN.getRange("C9:E9").setValue(rowValue[5]);
    wsformBN.getRange("C10:E10").setValue(rowValue[6]);
    wsformBN.getRange("C11:E11").setValue(rowValue[7]);
    wsformBN.getRange("C12:E12").setValue(rowValue[8]);

    wsformBN.getRange("H7:I7").setValue(rowValue[11]);
    wsformBN.getRange("H8:I8").setValue(rowValue[12]);
    wsformBN.getRange("H9:I9").setValue(rowValue[13]);
    wsformBN.getRange("H10:I10").setValue(rowValue[14]);
    wsformBN.getRange("H11:I11").setValue(rowValue[15]);

    wsformBN.getRange("H13:I13").setValue(rowValue[17]);
    wsformBN.getRange("H14:I14").setValue(rowValue[18]);
    wsformBN.getRange("H15:I15").setValue(rowValue[19]);

    valuesFound=true;
    return;
  }}

   if (valuesFound==false){
clearCustomerInfo()
}};

If you know how to write the improve of this please help. I really want this.

CodePudding user response：

Your code is not optimal, but if it really takes two to three minutes to run, chances are that the spreadsheet is on the heavy side. To improve spreadsheet performance, see these optimization tips.

To improve the code, use Range.setValues(), like this:

const wsformBN = SpreadsheetApp.getActive().getSheetByName('formBN');
const wsCustomer = SpreadsheetApp.openById('SheetID').getSheetByName('SheetName');

function clearCustomerInfo() {
  wsformBN.getRangeList(['C7:E8', 'C9:D12', 'H7:I11', 'H13:I15',]).clearContent();
}

function searchCUtoBN() {
  clearCustomerInfo();
  const setValues_ = (rangeA1, values) => wsformBN.getRange(rangeA1)
    .offset(0, 0, values.length, values[0].length)
    .setValues(values);
  const searchCustomerID = wsformBN.getRange('C4').getDisplayValue();
  const values = wsCustomer.getDataRange().getDisplayValues();
  values.some(row => {
    if (row[2] === searchCustomerID) {
      const valuesC7e12 = row.slice(3, 8   1).map(value => [value, value, value]);
      setValues_('C7', valuesC7e12);
      const valuesH7i11 = row.slice(11, 15   1).map(value => [value, value]);
      setValues_('H7', valuesH7i11);
      const valuesH13i15 = row.slice(17, 19   1).map(value => [value, value]);
      setValues_('H13', valuesH13i15);
      return true;
    }
  });
}

See Apps Script at Stack Overflow and Clean Code JavaScript.