title: Google Apps Script - getLastRow where certain columns are empty
body: <p>I have the following Google Apps Script which copies data from the source sheet and pastes it into another sheet. It currently looks for the last empty row and last empty column of the target sheet. I now have certain columns in the target sheet populated with formula to deal with the pasted data. The target sheet column range is A:K and has formula in columns I:K. in Can someone help me with new code to look for the last row where columns A:H are empty?</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>function onEdit(e) {
    var spreadsheet = e.source;
    var sheet = spreadsheet.getActiveSheet();
    var sourcesheetname = "SOP Register"
    var range = e.range;
    var sheet = range.getSheet();
    var row = range.getRow();
    var column = range.getColumn();
    var editedColumn = range.getColumn();
    var editedRow = range.getRow();
    var column = 7;
    var date = range.getValue();
    if(Object.prototype.toString.call(date) === '[object Date]' &amp;&amp; editedColumn == column &amp;&amp; editedRow &gt; 2 &amp;&amp; sheet.getName() == sourcesheetname) {
      var targetsheetname = "Internal Audit Register";
      var target = e.source.getSheetByName(targetsheetname);
      var numCols = sheet.getLastColumn();
      var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
      values.splice(9) //Up to and including column I
      values.splice(7, 1) //Remove column H
      values.splice(2, 3); //Keep columns all columns and leave out columns C, D &amp; E 
      var lastRow = target.getLastRow();
      var lastCol = target.getLastColumn();
      values.unshift("SOP"); //Append "SOP" to column A
      target.appendRow(values); // Append new row  
      sheet.hideColumns(6,2);}}//End of onEdit Functions</code></pre>
  </div>
  </div>
  </p>
  
  <p>After Tanaike's post I have added his function getLastRow(sheet) and all of my onEdit(e) script below:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>function getLastRow(sheet) {
    const values = sheet.getRange("A1:H" + sheet.getLastRow()).getDisplayValues();
    let lastRow = 0;
    for (let r = values.length - 1; r &gt;= 0 ; r--) {
      if (!lastRow &amp;&amp; !values[r].every(e =&gt; e == "")) {
        lastRow = r + 1;
        break
      }
    }
    return lastRow;
  }
  
  // Cut Employees Left from Unit Standards sheet and paste in Unit Standards - Employees Left sheet
  function onEdit(e) {
    var ss = e.source;
    var sheet = ss.getActiveSheet();
    var sheetName = "Unit Standards"
    var range = e.range;
    var editedColumn = range.getColumn();
    var editedRow = range.getRow();
    var column = 4;
    var date = range.getValue();
    // Object.prototype.toString.call(date) === '[object Date]' --&gt; checks if value is date
    // editedColumn == column &amp;&amp; editedRow &gt; 4 --&gt; checks if edited cell is from 'Date Left'
    // sheet.getName() == sheetName --&gt; checks if edited sheet is 'Unit Standards'
    if(Object.prototype.toString.call(date) === '[object Date]' &amp;&amp; editedColumn == column &amp;&amp; editedRow &gt; 4 &amp;&amp; sheet.getName() == sheetName) {
      var numCols = sheet.getLastColumn();
      var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
      var destinationSheet = ss.getSheetByName("Unit Standards - Employees Left");
      // Get first empty row:
      var emptyRow = destinationSheet.getLastRow() + 1;
      // Copy values from 'Unit Standards'
      destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
      sheet.deleteRow(editedRow);
      sheet.hideColumns(column); }
    
  // Copy and paste from Events/Incidents sheet to Vehicle Damage sheet
   {var range = e.range;
    var sheet = range.getSheet();
    var row = range.getRow();
    var column = range.getColumn();
    var sourcesheetname = "Events/Incidents";
    var checkbox = range.getValue();
    if (sheet.getName() == sourcesheetname &amp;&amp; column == 25 &amp;&amp; row &gt; 2 &amp;&amp; checkbox == true) {
      var targetsheetname = "Vehicle Damage";
      var target = e.source.getSheetByName(targetsheetname);
      var numCols = sheet.getLastColumn();
      var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
      values.splice(17)
      values.splice(8, 8)
      values.splice(5, 1)
      values.splice(3, 1); // Removing undesired values
      var lastRow = target.getLastRow();
      var lastCol = target.getLastColumn();
      target.appendRow(values); }// Append new row   
  
  //SOP Internal Audit Required CheckBox if True
   {var range = e.range
    var sheet = range.getSheet();
    var row = range.getRow();
    var column = range.getColumn();
    var sourcesheetname = "SOP Register";
    var checkbox = range.getValue();
    if (sheet.getName() == sourcesheetname &amp;&amp; column == 5 &amp;&amp; row &gt; 2 &amp;&amp; checkbox == true) {
      sheet.showColumns(6,2);
      sheet.getRange("F3").activate();}
  
  // Copy and paste from SOP Register sheet to Internal Audit sheet
   {var spreadsheet = e.source;
    var sheet = spreadsheet.getActiveSheet();
    var sourcesheetname = "SOP Register"
    var range = e.range;
    var sheet = range.getSheet();
    var row = range.getRow();
    var column = range.getColumn();
    var editedColumn = range.getColumn();
    var editedRow = range.getRow();
    var column = 7;
    var date = range.getValue();
    if(Object.prototype.toString.call(date) === '[object Date]' &amp;&amp; editedColumn == column &amp;&amp; editedRow &gt; 2 &amp;&amp; sheet.getName() == sourcesheetname) {
      var targetsheetname = "Internal Audit Register";
      var target = e.source.getSheetByName(targetsheetname);
      var numCols = sheet.getLastColumn();
      var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
      values.splice(9) //Up to and including column I
      values.splice(7, 1) //Remove column H
      values.splice(2, 3); //Keep columns all columns and leave out columns C, D &amp; E 
      var lastRow = getLastRow(target);
      var lastCol = target.getLastColumn();
      values.unshift("SOP"); //Append "SOP" to column A
      target.appendRow(values); // Append new row  
      sheet.hideColumns(6,2);}}}}}//End of onEdit Functions</code></pre>
  </div>
  </div>
  </p>
  
  <p>This is my desired outcome and to paste the new data in cells A5:F5 but the current script is pasting it into cells A6:F6 and cells I6:K6 are empty:</p>
  
  <p><a href="https://i.stack.imgur.com/QDAH2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QDAH2.png" alt="enter image description here"></a></p>
  
  <p>Unwanted Outcome</p>
  
  <p><a href="https://i.stack.imgur.com/c95L5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/c95L5.png" alt="enter image description here"></a></p>
  
  <p>A Sample of the source sheet and the target sheet are below:</p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/1cCv8cT4rOAfcTOZhzsVzoMntRuHE8v8Cy2j0HmweYqk/edit?usp=sharing" rel="nofollow noreferrer">Sample Spreadsheet</a></p>