Optimising Google Scripts Function - Finding Last Row

Question

I've cobbled together a script for google scripts, to use in Google sheets, which aims to find certain rows and move them about.

When a check box in a certain column is marked, I get a range of cells from that row, and then move them to another column. I want them to go to the last empty row in that column. Or, if it's easier, they could go to the first empty row.

I have the functionality in place, but I'm worried that once my sheet gets to a hundred or a few hundred rows, this function is going to run incredibly slowly.

Can anyone help me to write this code in a more optimised/efficient way?

Edit: I need the last from from a specific subset of columns, not just the last row in the whole sheet.

Code is below:

function moveDoneTask()
{

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();

 var columnNumberToWatch = 7;
 var range = sheet.getActiveCell();

 var lastRowinProgressColumns; 
 var lastRowinDoneColumns = 0;

 for (var i=1, doneRows=sheet.getRange("K:S").getNumRows(); i<=doneRows; i++) {
    if ( !sheet.getRange(i, 11, 1, 9).isBlank() ) 
    {
      lastRowinDoneColumns = i;
      Logger.log("last done row" +lastRowinDoneColumns);
    }
  }

  for (var j=1, notDoneRows=sheet.getRange("A:I").getNumRows(); j<=notDoneRows; j++) {
    if (!sheet.getRange(j, 1, 1, 9).isBlank()) 
    {
      lastRowinProgressColumns = j;
      Logger.log("last not done row" +lastRowinProgressColumns);
    }
  }

 if (range.getColumn() == columnNumberToWatch && range.isChecked()) 
 {
   Logger.log("is checked");

   targetRange = sheet.getRange(lastRowinDoneColumns + 1, 11)
   cellsToMove = sheet.getRange(range.getRow(), 1, 1, 9)
   belowCellsToMove = cellsToMove.offset(1, 0) //get the row of cells below the cells that are about to be moved
   rangeMovingUp = sheet.getRange("A"+belowCellsToMove.getRow()+":I"+lastRowinProgressColumns)

   Logger.log("row below me is row " + belowCellsToMove.getRow());

   cellsToMove.moveTo(targetRange);
   rangeMovingUp.moveTo(cellsToMove);

 }
}

Thanks for your help!

Edit 2: Took the advice below and made some changes to the script, seems to be running faster now:

function moveDoneTask()
{
 var sheet = SpreadsheetApp.getActiveSheet(); 
 var columnNumberToWatch = 7;
 var range = sheet.getActiveCell(); 

 if (range.getColumn() == columnNumberToWatch && range.isChecked()) 
 {
   targetRow = (sheet.getRange('M11:S').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1);
   targetRange = sheet.getRange(targetRow, 11)
   notDoneRows = sheet.getRange("A:I").getNumRows()
   cellsToMove = sheet.getRange(range.getRow(), 1, 1, 9)
   belowCellsToMove = cellsToMove.offset(1, 0)
   rangeMovingUp = sheet.getRange("A"+belowCellsToMove.getRow()+":I"+notDoneRows)

   cellsToMove.moveTo(targetRange);
   rangeMovingUp.moveTo(cellsToMove);
 }
}

It might be helpful to see the sheet, or an example, to fully understand but you can do lastRow = sheet.getLastrow() to get the last used row in the sheet (so you'd need to paste to lastRow + 1 — James Payne
I originally wanted to use .getLastRow() but I need the last row in a specific column(s), not the last row in the whole sheet. So I'm looking to move say cells A10, B10, C10 and D10 to columns E, F, G and H, but to an empty row within those columns only. It's basically a to do list, so I'm moving a done task from one side of the spreadsheet to the other. — Hannah

ziganotschka ziganotschka · Accepted Answer · 2020-01-20T16:24:51

A possibility to get the last row of a certain column is to use getNextDataCell(direction). This returns you the next cell on the edge to a empty range (which is equivalent to the first empty row if you do not have blank cells inbetween). Mind that there is a documentation bug, the correct syntax is: var lastRowinProgressColumns=sheet.getRange(1, 11).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
To make the code more efficient, check first either the active range is in the correct column (if (range.getColumn() == columnNumberToWatch && range.isChecked())), and retrieve lastRowinProgressColumns within the if statement (there is no reason to retrieve it if the codition is not fulfilled).

Optimising Google Scripts Function - Finding Last Row

2 Answers