Move data from one tab to another, and paste data in the last row ignoring formulas

1
votes

The goal is once I check a box in tab 1, it will copy information from rows 21 to 29 onto Tab 2. Tab 2 it will find the first row in which column A has a blank value and paste the information there. LastRow() does not work as I have formulas in columns J-K in Tab 2. I am guessing I have the wrong format or order of IF statements.

  function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var targetSheet = ss.getSheetByName("Tab 2");
  var firstCell = targetSheet.getRange("A1");
     if(!firstCell.isBlank()){
     var firstEmptyRow = firstCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
     } else{
    var firstEmptyRow = firstCell.getRow();
    }

  if(s.getName() == "Tab 1" && r.getColumn() == 18 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var target = targetSheet.getRange(firstCell);
    s.getRange(row, 21, 1, numColumns).copyTo(target,{contentsOnly:true});
1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

You want to find the first blank cell in column A

A useful method to do so is getNextDataCell().

This method finds the edge between data containing and empty cells going along the specified direction.

Sample:

var firstCell = targetSheet.getRange("A1");
if(!firstCell.isBlank()){
 var firstEmptyRow = firstCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
} else{
  var firstEmptyRow = firstCell.getRow();
}

Note:

isBlank() on its own can also be used to find the first empty cell, but in your case it might not be the most elegant method.