CODEWITHSUNDEEP
google-apps-script
adsigel
adsigel

Reputation: 111

Google Script Editor - Cannot find method formatDate(object,string,string)

I'm new to Google Scripts, and I'm trying to create a macro that will automatically filter a spreadsheet based on a given date. However, I keep getting an error when I try to reformat the dates. The second line results in the error, "Cannot find method formatDate(object,string,string)"

This is working elsewhere in my script on a single cell value, but not for this array. How can I reformat all the dates to my desired format?

var filterMonthRange = sh.getRange('B2:B'+sh.getLastRow()).getValues();
var filterMonthRange = Utilities.formatDate(filterMonthRange, "GMT", "MMMMM YYYY");

Upvotes: 1

Views: 647

Answers (1)

Tanaike
Tanaike

Reputation: 201643

  • You want to convert the date object to the format of MMMMM YYYY.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

date of Utilities.formatDate(date, timeZone, format) is required to be the date object. The reason of the error is that an array is used instead of the date object.

Pattern 1:

In this pattern, the values retrieved from cells "B2:B" are converted with Utilities.formatDate().

Modified script:

Please modify your script as follows.

From: 
var filterMonthRange = Utilities.formatDate(filterMonthRange, "GMT", "MMMMM YYYY");
To: 
var filterMonthRange = filterMonthRange.map(function(e) {return [Utilities.formatDate(e[0], "GMT", "MMMMM YYYY")]});
  • In this case, filterMonthRange is 2 dimentional array like [["January 2020"],,,]. Please be careful this.

Pattern 2:

In this pattern, the format of cells "B2:B" are modified to MMMMM YYYY.

Sample script:

var filterMonthRange = sh.getRange('B2:B'+sh.getLastRow());
filterMonthRange.setNumberFormat("mmmm\" \"yyyy");
  • When above script is run, the cell format is modified.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

Related Questions