for-loopgoogle-apps-scriptgoogle-sheetssourceforge-appscript

App Script For Loop is Slow Need to optimize code for faster update


This code works perfectly but its slow like turtle. Generally I don't take this approach but I was not able to find any other option.

Well my requirement is to where ever in the column code finds 1 get the key and value of the that index defined range and go to To sheet find the key and paste the value in the required columns.

If I can jump to cells where 1 is in selected column and get the index to find the key and value and then jump to To page key column where key is instead of going to every cell and checking for it whether it is there or not.

I am new to app script and little help would be great. Thank you in advance.

function Data_Update(){

  //assigning sheet name to variables
  var ss = SpreadsheetApp.getActive()
  var from = ss.getSheetByName("From Sheet")//update here from sheet name(Updated)
  var To= ss.getSheetByName("To Sheet")//update here to sheet name(Updated)

  // Creating Loop 
  for (var i = 4;i<=7000;i++){//Update here from which row to start
    //assigning values to check if there is 1 in column K
    var udaterang = from .getRange("K"+i).getValue()//update here from which column to check for
    Logger.log(i)
   // Checking condition if the value is diffrent from the value already is
    if (udaterang == 1) {
      //creating key to find the value
      var name1 = from .getRange("A"+i).getValue()//update here the key column 1
      var name2 = from .getRange("B"+i).getValue()//update here the key column 2
      var name3 = from .getRange("C"+i).getValue()//update here the key column 3
      var name = name1.trim()+name2.trim()+name3.trim()
      var rng = from .getSheetValues(i,4,1,7)//start row, start column, # rows, # columns
      
      // Looping through each cell to check if the data needs update
      for(var j=2;j<=12500;j++){
        var key = To.getRange("AP"+j).getValue()
        if(key == name){ //[1] because column B
          To.getRange("AI"+j+":"+"AO"+j).setValues(rng)
          break
      }
      }
      
    }

  }
  
}

Solution

  • Try this:

    function Data_Update() {
      const ss = SpreadsheetApp.getActive();
      const fsh = ss.getSheetByName("From Sheet");
      const fkvs = fsh.getRange(4, 11, fsh.getLastRow() - 3).getValues().flat();
      const fvs = fsh.getRange(4, 1, fsh.getLastRow() - 3, fsh.getLastColumn()).getValues();
      const tapvs = tsh.getRange(2, 42, tsh.getLastRow() - 1).getValues().flat();
    
      fkvs.forEach((k, i) => {
        let udaterang = k;
        if (k == 1) {
          let name = fvs[i][0].toString().trim() + fvs[i][1].toString().trim() + fvs[i][2].toString().trim();
          let idx = tapvs.indexOf(name);
          if (~idx) {
            tsh.getRange(idx + 2, 35, 1, 7).setValues(tsh.getRange(i + 2, 4, 1, 7).getValues());
          }
        }
      });
    }
    

    This may take some tweaking because I have not tested this as I don't have the data to do so.