Search code examples
google-apps-scriptgoogle-sheets

Google Apps Script - getLastRow where certain columns are empty

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?

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]' && editedColumn == column && editedRow > 2 && 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 & 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

After Tanaike's post I have added his function getLastRow(sheet) and all of my onEdit(e) script below:

function getLastRow(sheet) {
  const values = sheet.getRange("A1:H" + sheet.getLastRow()).getDisplayValues();
  let lastRow = 0;
  for (let r = values.length - 1; r >= 0 ; r--) {
    if (!lastRow && !values[r].every(e => 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]' --> checks if value is date
  // editedColumn == column && editedRow > 4 --> checks if edited cell is from 'Date Left'
  // sheet.getName() == sheetName --> checks if edited sheet is 'Unit Standards'
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 4 && 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 && column == 25 && row > 2 && 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 && column == 5 && row > 2 && 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]' && editedColumn == column && editedRow > 2 && 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 & 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

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:

enter image description here

Unwanted Outcome

enter image description here

A Sample of the source sheet and the target sheet are below:

Sample Spreadsheet

Solution

  • I believe your goal as follows.

    • You have a sheet which has the values in the columns "A" to "K". And the columns "I" to "K" have the formulas.
    • You want to retrieve the numbers of last row in the range of "A:H".

    For this, how about this answer? In this answer, I prepare a function for retrieving the last row you want.

    Modified script:

    Please add the following function. This function returns the values of lastRow by inputting a sheet object.

    function getLastRow(sheet) {
  const values = sheet.getRange("A1:H" + sheet.getLastRow()).getDisplayValues();
  let lastRow = 0;
  for (let r = values.length - 1; r >= 0 ; r--) {
    if (!lastRow && !values[r].every(e => e == "")) {
      lastRow = r + 1;
      break
    }
  }
  return lastRow;
}

    And, in order to use above function in your script, please modify as follows.

    From: 
    var lastRow = target.getLastRow();
    To: 
    var lastRow = getLastRow(target);
    • By this, lastRow can be retrieved in the range of "A:H".

    Reference:

    Added 1:

    About one more modification, please test the following modification. In your current script, lastRow retrieved by getLastRow(target) is not used and values is appended to the last row of the sheet by appendRow. So in order to use lastRow, please modify as follows. In this case, from your updated question, please modify getRange("A1:H" + sheet.getLastRow()) to getRange("A1:F" + sheet.getLastRow()) for the function of getLastRow.

    From:

    var lastRow = getLastRow(target);
var lastCol = target.getLastColumn();
values.unshift("SOP"); //Append "SOP" to column A
target.appendRow(values); // Append new row

    To:

    var lastRow = getLastRow(target);
var lastCol = target.getLastColumn();
values.unshift("SOP");
target.getRange(lastRow + 1, 1, 1, values.length).setValues([values]);  // Modified

    Added 2:

    1. You have a sheet which has the values in the columns "A" to "K". And the columns "I" to "K" have the formulas.
    2. You want to retrieve the numbers of last row in the range of "A:H".
    3. And also, you want to copy the formulas of the columns "I" to "K" to the same row which puts the values.

    For this, please modify your script as follows. In this case, please use above getLastRow().

    From:

    var lastRow = getLastRow(target);
var lastCol = target.getLastColumn();
values.unshift("SOP"); //Append "SOP" to column A
target.appendRow(values); // Append new row

    To:

    var lastRow = getLastRow(target);
var lastCol = target.getLastColumn();
values.unshift("SOP");
target.getRange(lastRow + 1, 1, 1, values.length).setValues([values]);  // Modified
if (lastRow >= 3) target.getRange(lastRow, 9, 1, 3).copyTo(target.getRange(lastRow + 1, 9, 1, 3), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);  // Added
    • In this case, at least, it is required to have the row 3 has the formulas at the columns "I" to "K".
    • If the row 3 has surely the formulas at the columns "I" to "K", I think that you can also use if (lastRow >= 3) target.getRange(3, 9, 1, 3).copyTo(target.getRange(lastRow + 1, 9, 1, 3), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);.