GAS How to use getDataRange and getLastRow when the sheet contains full column arrayformulas in a dynamic spreadsheet

Question

I am trying to get a form submission spreadsheet to create an all day google calendar event. The problem is that my spreadsheet has multiple ARRAYFORMULA's in it and because the code uses:

var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();

It counts the ENTIRE sheet because the ARRAYFormula fools the .getDataRange into counting the rows without submissions on them yet

Full Code:

var calendarId = CalendarApp.getCalendarById("xxxxxxxxxx");
//below are the column ids of that represents the values used in the spreadsheet (these are non zero indexed)

//Column containg the Start Date/Time for the event
var startDtId = 2;
//Column containg the End Date/Time for the event
var endDtId = 2;
//Column containg the First Part of the Title for the event (In this case, Shift Area)
var areaId = 9;
//Column containg the Second part of the Title for the event (In this case, Shift)
var shiftId = 3;
//Column containg the Second part of the Title for the event (In this case, Name)
var nameId = 7;
//Column containg the Third part of the Title for the event (In this case, Reason)
var reasonId = 13;
//Column containg the Description for the event (In this case, Phone)
var phoneId = 8;
//Column containg the Time Stamp for the event (This will always be 1)
var formTimeStampId = 1;
//Column containg the Location for the event
var addressId = 14;


function getLatestAndSubmitToCalendar() {
//Allow access to the Spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
//Removed setting of Hour and Minute for the Start and End times as they are all day
var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
var endDt = sheet.getRange(lr,endDtId,1,1).getValue();
//Create an addition to the Description to included who added it and when
var subOn = "This shift was added: "+sheet.getRange(lr,formTimeStampId,1,1).getValue()+" by: "+sheet.getRange(lr,nameId,1,1).getValue()+" - Phone: "+sheet.getRange(lr,phoneId,1,1).getValue();
//Setting the Comments as the description, and adding in the Time stamp and Submision info
var desc = sheet.getRange(lr,reasonId,1,1).getValue()+", "+sheet.getRange(lr,nameId,1,1).getValue()+" is scheduled to ride on the "+sheet.getRange(lr,shiftId,1,1).getValue()+" in "+sheet.getRange(lr,areaId,1,1).getValue()+<br/>+subOn;
//Create the Title
var title = sheet.getRange(lr,areaId,1,1).getValue()+"-"+sheet.getRange(lr,shiftId,1,1).getValue()+"["+sheet.getRange(lr,reasonId,1,1).getValue()+"] - "+sheet.getRange(lr,nameId,1,1).getValue();
//Run the Create event Function
createEvent(calendarId,title,startDt,desc);
};

function createEvent(calendarId,title,startDt,endDt,desc) {
var cal = CalendarApp.getCalendarById("xxxxxxxxxxxxxxxxxxxxxxxxx");
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
var start = new Date(startDt);
var end = new Date(endDt);
var subOn = "This shift was added: "+sheet.getRange(lr,formTimeStampId,1,1).getValue()+" by: "+sheet.getRange(lr,nameId,1,1).getValue()+" - Phone: "+sheet.getRange(lr,phoneId,1,1).getValue();
var desc = sheet.getRange(lr,reasonId,1,1).getValue()+", "+sheet.getRange(lr,nameId,1,1).getValue()+" is scheduled to ride on the "+sheet.getRange(lr,shiftId,1,1).getValue()+" in "+sheet.getRange(lr,areaId,1,1).getValue()+<br/>+subOn;
//Manually set the Location, this can be modified to be dynamic by modifying the code if need be
var loc = sheet.getRange(lr,addressId,1,1).getValue();

//Set the Options, in this case we are only using Description and Location, as we do not need Guests or sendInvites
var event = cal.createAllDayEvent(title, start, {
description : desc,
location : loc
});
};

Please Help :)

Answer 1

I have actually found a work around to this. I preserved the .getLastRow() route above and rather than use an array formula in the sheet, I used .getLastRow() again and added my formulas via .setFormula(). This allowed me to erase the arrays and therefore the .getDataRange() would now correctly get only rows with data rather than all rows due to the array.

To put everything in the correct order, I set the function containing the formula input to the .onFormSubmit trigger and called the calendar event maker from the end of that function. This now works beautifully.

// Declare the columns needed to access for formula input first and set to their index

var columnId = 3;

// Now set the formulas

function formulaInput() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName('Sheet 1')
var range = sheet.getDataRange()
var last = range.getLastRow()


//This sets a formula in the columns of the last row of data that used to have an array formula sheet-side (Using the onFormSubmit trigger)

sheet.getRange(last,columnId,1,1).setFormula('= insert former array without the arrayformula prefix')

// Now use this to initiate the calendar event from the original code in question which ensures the formula input occurs 1st and subsequently triggers the calendar event after ALL the deaired data is present.

return getLatestAndSubmitToCalendar()

}