title: Write Google sheet arrayformula in GAS code - enhance performance
body: <p>I'm sure that my code shows that I'm not very experienced with this.
  When I run the script on test data (just 30 lines) it does what it is supposed to, so I'm happy with that.
  But with I run the script on the actual data (approx 9000 lines) it is really slow.</p>
  <p>Here is what I'm trying to do:</p>
  <ol>
  <li>Create a list (on sheet2) containing only the unique values from sheet1 column A</li>
  <li>if the value in sheet1 column A has multiple identical values</li>
  <li>then place the most recent date (from sheet1 column E) in sheet2 column E</li>
  <li>also get the values from the other columns (B, C, D, F, G and H)</li>
  </ol>
  <p>I've managed to do this by using the hardcoded (array)formulas in Sheet2, but I know this is not the best approach.
  Here is the link to the file : <a href="https://docs.google.com/spreadsheets/d/10VN6qhMjRdQUx7wvlAI1yo4BoMYPwbjFzRh7l6JSaro/edit?usp=sharing" rel="nofollow noreferrer">Form UniqData</a>
  Can anyone help me improve this script?</p>
  <pre><code>function UniqueData() {
  var ss = SpreadsheetApp.getActive();
  var md = ss.getSheetByName(&quot;Sheet1&quot;);
  var ws = ss.getSheetByName(&quot;Sheet2&quot;);
  
      ws.getRange(&quot;a2:H&quot; + ws.getLastRow()).clear({contentsOnly: true, skipFilteredRows: true});
      ws.getRange(&quot;a1&quot;).setFormula(&quot;=UNIQUE(Sheet1!A:A)&quot;);     
    
  var unRng = &quot;A2:A&quot; + ws.getLastRow();
    sortRng = &quot;Sheet1!A2:H&quot; + md.getLastRow();
  
  ws.getRange(&quot;B2:H2&quot;).clear({contentsOnly: true, skipFilteredRows: true});  
  
  formulaP1 = &quot;=ARRAYFORMULA(VLOOKUP(&quot; + unRng + &quot;,SORTN(SORT(&quot; + sortRng + &quot;,2,FALSE,1,TRUE),&quot; + ws.getLastRow() + &quot;,2,1,TRUE),&quot;
  formulaP2 = &quot;,FALSE))&quot;
  mdLC      = md.getLastColumn();
  
    for (var i = 2; i &lt; mdLC+1; i++) {
        var row = 2
        ws.getRange(row, i).setFormula(formulaP1 + i + formulaP2);
      }
  }
  </code></pre>