0

In Google SpreadSheet, Sheet1 has 3 values. [Duration is calculated by =DATEDIF(StartDate, EndDate, "M")]

enter image description here

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

enter image description here

Example 2: 
StartDate = 01-Sep-2016 and EndDate = 31-Dec-2016 
So the Duration is 3

enter image description here

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.

Saravanan Sachi
  • 2,572
  • 5
  • 33
  • 42

2 Answers2

0

You found 80% of the solution, I just added some corrections to your code to make it works dynamically:

function myFunction(){

  onOpen();
}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1= ss.getSheetByName("Sheet1");
  var s2= ss.getSheetByName("Sheet2");
  var sDate = new Date(s1.getRange("B1").getValue()); // changed to B1 - the start date value, and sDate must be a Date Object, not string.
  var sDuration = s1.getRange("B3").getValue(); // changed to B3 - the duration value
  for (var cell = 0; cell < sDuration; cell++) {  // cell starts from 0
    var sMonth = Utilities.formatDate(sDate, Session.getScriptTimeZone(), "MMM"); // I moved this into FOR loop
    var sYear = Utilities.formatDate(sDate, Session.getScriptTimeZone(), "YYYY"); // I moved this into FOR loop
    s2.getRange(1,cell+3).setValue(sMonth + ", " + sYear); // geting range with row & column value, not with Column name
    sDate.setMonth(sDate.getMonth()+1); // add one month to sDate
  }
}

there are comments on the code where I changed. I hope you find this helpful.

Armin.G
  • 381
  • 2
  • 12
0

Slightly modified the code in Change value of cell in same row with google script if column has certain value, it works.

Modified onOpen() is,

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 lastColumn = s2.getLastColumn();
  for (var cell = 3; cell <= lastColumn + sDuration; cell++){
    var currentCell = s2.getRange(1, cell);
    currentCell.setValue(Utilities.formatDate(sDate, Session.getScriptTimeZone(), "MMM") + ", " 
                    + Utilities.formatDate(sDate, Session.getScriptTimeZone(), "YYYY"));
    sDate.setMonth((sDate.getMonth() + 1) % 12);
  }
}
Community
  • 1
  • 1
Saravanan Sachi
  • 2,572
  • 5
  • 33
  • 42