I'm having trouble with the getLastRow() function in my script. I want to retrieve the last row with data, but the script returns all rows because there are empty rows below the filled ones. How can I modify it to stop at the last row with data? The data includes an array formula and here's the script snippet to give you a better idea:
function Productivity()
{
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Do you want to send emails to all PRICE ACCURACY resources now?', ui.ButtonSet.YES_NO);
// Process the user's response.
if (response == ui.Button.YES)
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Productivity Report Out')
var range = ss.getDataRange().getValues()
var lastrow = ss.getLastRow();
//Logger.log(range.length)
for(var i=1;i<=lastrow; i++) //i<range.length
{
Logger.log("Index:"+i+" | ["+range[i]+"]")
var status = range[i][14]
if(status !='No')
{
var name = range[i][0]
var ldap = range[i][1]
var target = range[i][2]
var new_mvt = range[i][3]
var onboarding = range[i][4]
var ubio_rates = range[i][5]
var violation = range[i][6]
var reevaluation = range[i][7]
var total = range[i][8]
var current_productivity = range[i][9]
var deficit = range[i][10]
var remaining_checks = range[i][11]
var week = range[i][12]
var date = range[i][13]