In Google SpreadSheet, Sheet1 has 3 values. [Duration is calculated by =DATEDIF(StartDate, EndDate, "M")]
Sheet2 has 2 predefined columns (Col1, Col2) and dynamic columns that need to be appended based on the Duration value.
Example 1:
StartDate = 01-Sep-2016 and EndDate = 30-Nov-2016
So the Duration is 2
Example 2:
StartDate = 01-Sep-2016 and EndDate = 31-Dec-2016
So the Duration is 3
As the columns are dynamic, is it possible to set the Columns value based on column and row index instead of hard coded as below code.
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s1= ss.getSheetByName("Sheet1");
var s2= ss.getSheetByName("Sheet2");
var sDate = s1.getRange("B5").getValue();
var sDuration = s1.getRange("B7").getValue();
var sMonth = Utilities.formatDate(sDate, Session.getScriptTimeZone(), "MMM");
var sYear = Utilities.formatDate(sDate, Session.getScriptTimeZone(), "YYYY");
for (var cell = 1; cell <= sDuration; cell++) {
s2.getRange("C1").setValue(sMonth + ", " + sYear);
}
}
Thanks.