Short answer
Use ARRAY_CONSTRAIN(array,row_limit,column_limit)
Explanation
ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.
To calculate the number of rows/columns considering the data range by using built-in functions,
for rows use a formula like the following
=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))
for columns use a formula like the following
=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
The sample formula in the question will look like the following:
=ARRAYFORMULA(
ARRAY_CONSTRAIN(
MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
)
)
Here is how I come up with a solution to my this problem. In case in future anybody gets stuck in something like this, hope this solve his/her problem and gets job done. :)
Google Script:
function kalender() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 3");
var lrow = ss.getMaxRows();
var lcol = ss.getMaxColumns();
var rng = ss.getRange(1, 1, lrow, lcol);
var data = rng.getValues();
for(var i=1; i< 100; i++){
if(data[i][5] == 'new'){
var subject = data[i][2] + " with " + data[i][1];
var startdate = data[i][3];
var enddate = data[i][4];
// var enddate = new Date(addDays(startdate,times));
var event = CalendarApp.getDefaultCalendar();
event.createEvent(subject, startdate, enddate);
Logger.log('Event ID: ' + event.getId());
data[i][5] = "added to calendar";
rng.setValues(data);
}
}
}
Best Answer
From the Google Sheets main menu, try
Insert/Row above
orInsert/Row below
, or right click on the cell where you want the new cell and hitInsert row