Google Script For "Marking" Non Blank Rows

Question

I'm trying to create a google script that will look at my sheet, get the length of data and then mark "processed" in Column P. I can't do this with an array/if formula because I need to know when a new line comes in and has not been processed. My current script just writes never ending data to column P. I want it to only write to the last non blank row based on Column A.

Here is my example sheet and script that isn't working.

https://docs.google.com/spreadsheets/d/13Q4I91O4vMDSCHwAMG0DOLLkbzLCxWiQzbugJ-6nPjs/edit?usp=sharing

 function MarkrowsProcessed() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spr.getSheetByName("Sheet1");
  var column = spr.getRange('A:A').getValues();

  for (var i = 0; i < column.length; ++i) 
  {
    var rowData = column[i];
    var emailSent = rowData[16];
    if (emailSent != 'Processed') 
    {       
   sheet.getRange(i+1, 16, column.length, 1).setValue('Processed');// Make sure the cell is 
updated right away in case the script is interrupted
 }

}
}

Thanks in advance for the help.

Answer 1

I'm not sure if I understand the task right. Either you need write 'Processed' in every cell in column 'P' or only at the end of it?

Here is corrected function that gets the length of column 'A', iterates trough column 'P' and writes 'Processed':

function MarkrowsProcessed() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spr.getSheetByName("Sheet1");
  
  // get data of column 'A'
  var column_A = spr.getRange('A:A').getValues();

  // get a len of column 'A'
  var len = column_A.filter(String).length; // it works if the column has no empty cells inside  
  
  // get data of column 'P'
  var column_P = spr.getRange('P:P').getValues();  
  
  // loop trough data of column 'P' (until last row in column 'A') and write 'Processed'
  for (var i=0; i<len; ++i) 
  {
    if (column_P[i] != 'Processed') sheet.getRange('P' + (i+1)).setValue('Processed');
  }
}

If you need the very last row on the sheet (not the last row in column 'A') it will be even easier. You need to change one line in the function:

  var len = sheet.getLastRow();

Answer 2

Your line sheet.getRange(i+1, 16, column.length, 1).setValue('Processed') assigns the value to a static line.

• To change it an elegant way would be to start at a line of which you know for sure that it is not empty and find from there the last non-blank row with getNextDataCell().

Sample:

var notBlank = sheet.getRange("A1");
var lastRow = notBlank.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow():
sheet.getRange(lastRow, 16, column.length, 1).setValue('Processed');

This would find the last row before the first blank row (could be an interdispersed blank row between non-empty rows).

• If you want to find the very last row of a sheet, then use getLastRow().

Sample:

var lastRow = sheet.getLastRow();
sheet.getRange(lastRow, 16, column.length, 1).setValue('Processed');

Careful:

Make sure that you really want to set the data into the last non-blank row (this means overwriting already existant data).