I'm trying to change the reference cell in the DATE function in an if loop in Apps Script. The cell I'm referencing is formatted in the following way: "2020-01-31 14:26:58." Here is what I got so far:

 var s = SpreadsheetApp.getActive().getSheetByName("Prep");
 var data = s.getRange("I2:I").getValues();
 var data_len = data.length;

 for(var i=0; i<data_len; i++) {
      if(data[i][0].length !== 0) {
        s.getRange(i+2,4).activate();
        s.getCurrentCell().setFormula('=TEXT(DATE(2020,MID(I2,6,2),1),"mmmm")');
      } else {
      }
    }

This works fine if all of the data is from the same month. However, I'm trying to get I2 to update to I3, I4...IX as I go through the loop so that if the data references more than one month, it will be changed accordingly. I've tried Googling solutions and looking at videos and posts but I can't figure it out.

Markdown Example (Bolded text is what I want the output to show):

Link	Type	Year	Month	First Name	Last Name	Gender	Age	Application Submitted
-	Applicant	2020	January	John	Doe	M	40	2020-01-31 14:26:58
-	Applicant	2020	February	Jill	Bradley	F	45	2020-02-15 11:50:06
-	Applicant	2020	March	Jane	Alice	F	41	2020-03-31 10:36:04

I'm trying to change the reference cell in the DATE function in an if loop in Apps Script. The cell I'm referencing is formatted in the following way: "2020-01-31 14:26:58." Here is what I got so far:

 var s = SpreadsheetApp.getActive().getSheetByName("Prep");
 var data = s.getRange("I2:I").getValues();
 var data_len = data.length;

 for(var i=0; i<data_len; i++) {
      if(data[i][0].length !== 0) {
        s.getRange(i+2,4).activate();
        s.getCurrentCell().setFormula('=TEXT(DATE(2020,MID(I2,6,2),1),"mmmm")');
      } else {
      }
    }

This works fine if all of the data is from the same month. However, I'm trying to get I2 to update to I3, I4...IX as I go through the loop so that if the data references more than one month, it will be changed accordingly. I've tried Googling solutions and looking at videos and posts but I can't figure it out.

Markdown Example (Bolded text is what I want the output to show):

Link	Type	Year	Month	First Name	Last Name	Gender	Age	Application Submitted
-	Applicant	2020	January	John	Doe	M	40	2020-01-31 14:26:58
-	Applicant	2020	February	Jill	Bradley	F	45	2020-02-15 11:50:06
-	Applicant	2020	March	Jane	Alice	F	41	2020-03-31 10:36:04

• google-sheets
• google-apps-script

Share Improve this question Follow edited Mar 6 at 16:06 rpgbear95 asked Mar 6 at 15:49 rpgbear95rpgbear95 3544 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You could use the value of i to set the value dynamically:

    s.getCurrentCell().setFormula('=TEXT(DATE(2020,MID(I'+(i+2)+',6,2),1),"mmmm")');

Other option would be to set an array of formulas and use only once setFormulas

And a third option is not to use a script but an array formula in D1 (first, delete all formulas in that column:

=VSTACK("Header",MAP(I2:I,LAMBDA(each,  IF(each="","",TEXT(DATE(2020,MID(each,6,2),1),"mmmm"))))

Dynamically Convert Dates to Month Names

Here is an alternative way using Google Apps Script to dynamically retrieve the month name from date entries and set the values in a specified range of a Google Sheet.

I slightly modified your code and instead of setting up a formula, this code will directly input the values.

Modified Code:

function updateMonthColumn() {
  var ss = SpreadsheetApp.getActive().getSheetByName("Prep");
  var data = ss.getRange("I2:I").getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][0].length !== 0) { 
      var dateString = data[i][0]; // Get the date string from column I
      var date = new Date(dateString);
      var monthName = date.toLocaleString('default', { month: 'long' }); // Get the month name from Date 

      ss.getRange(i + 2, 4).setValue(monthName);  // Set the month name in column D
    }
  }
}

You can also change 'long' to 'short' if you prefer the month name to be shorter.

Sample Output:

Link	Type	Year	Month	First Name	Last Name	Gender	Age	Application Submitted
-	Applicant	2020	January	John	Doe	M	40	2020-01-31 14:26:58
-	Applicant	2020	February	Jill	Bradley	F	45	2020-02-15 11:50:06
-	Applicant	2020	March	Jane	Alice	F	41	2020-03-31 10:36:04

References:

• Date.prototype.toLocaleString()

• new Date()

• setValues()