title: Optimizing .gs search & replace script?
body: <p>Hoping someone could just point out my probably obvious mistakes.</p>
  <p>Code searches a Data page &amp; copies matched info to a Results page. This is fine &amp; works quickly.
  What happens is when I add an overwrite section to the script. It overwrites matched entries as &quot;Done&quot; on the data page, so if the Data page gets added to, the previous entries won't match again.</p>
  <p>I have 2 variants I've tried that <em>work</em> but are quite slow...as in you can sit &amp; watch each match change to &quot;Done&quot; once every few seconds.</p>
  <p>Appreciate any insight.</p>
  <p>Here's the code:</p>
  <pre><code>function cellMatch() {
    // Sheet Import
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet1 = ss.getSheetByName(&quot;Raw_Data&quot;); // Data
    var sheet2 = ss.getSheetByName(&quot;Pallet_Data&quot;);// Result
    var sheet3 = ss.getSheetByName(&quot;Close&quot;);// Search
    // Data Import
    var lr = sheet1.getLastRow();
    var data = sheet1.getRange(2,1,lr-1,2).getValues();
    var lc = sheet2.getLastColumn()+1;
    var key = sheet3.getRange(&quot;A2&quot;).getValue();
    var matched = [[&quot;Pallet &quot;+lc+&quot; (&quot;+key+&quot;)&quot;]];
  
    //Start
    for (var i=0; i&lt;lr-1; i++) {
      if (data[i][1] == key) {
        var temp = [];
        temp.push(data[i][0]);
        matched.push(temp);
      
  // Slow Overwrite-----------------------------------
  /*
  if(i&gt;0){
    var temp1 = sheet1.getRange(i,2).getValue();
    var temp2 = sheet1.getRange(i+1,2).getValue();
    var temp3 = sheet1.getRange(i+2,2).getValue();
      // Testing1
      if(temp1 == key ){
        sheet1.getRange(i,2).setValue(&quot;Done&quot;);
      } 
      if (i== lr-2){
        if (temp2 == key){
            sheet1.getRange(i+1,2).setValue(&quot;Done&quot;);
         }
        if (temp3 == key){
           sheet1.getRange(i+2,2).setValue(&quot;Done&quot;);
         }
      }
  } 
  */    
    }
  
    // Shorter code but even slower overwrite----------------
  /*
    for(var i=1; i&lt;=lr; i++){
      var temp1 = sheet1.getRange(i,2).getValue();
      if(temp1 == key){
        sheet1.getRange(i,2).setValue(&quot;Done&quot;);
       }
     }
  */
  
  // Location Update ---------------------------------
      var A7 = sheet3.getRange(&quot;A7&quot;);
      A7.setValue(matched);
  
  // Data Write -----------------------------------------
    var result = sheet2.getRange(1,lc,matched.length);
    result.setValues(matched);
  
  }
  </code></pre>
  <p>Link to the <a href="https://docs.google.com/spreadsheets/d/1bm-hNS2YioSDd9B7-EFXt7WC2di_QLKu4YeIuazNZ-o/edit?usp=sharing" rel="nofollow noreferrer">Test Sheet</a></p>