Subtract date google spreadsheet script

Question

I have what seems to be a very tiny problem...

I have set up a script in Google spreadsheets that returns the current Month & Year and puts it into a cell. (This forms part of a much bigger sheet, so I do need the luxury of executing this within a script). I can't seem to display the previous month, whioch what I want as this is a report for the last month.

So, for example if I want to report on March 2013, how do I get the cell to display this using the current date, or is there a better way to do this?

I am currently doing this but get stuck with the calculation as it error's out "missing ) after argument list on line 9"

function myFunction() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var today_date = Utilities.formatDate(new Date(), "GMT", "MMMM yyyy");

var date = sheet.getRange("A2");
date.setValue(today_date);

date.setFormula("=EDATE("today_date",-1)");

}

Or can I just do it by adding something into the Utilities.formatDate(new Date(), "GMT", "MMMM yyyy");

your help is most appreciated.

AdamL AdamL · Accepted Answer · 2013-04-10T06:16:43

The correct Javascript would be:

date.setFormula("=EDATE(" + today_date + ",-1)");

but unfortunately this won't work on the spreadsheet side. You'll need something like this to make it work:

date.setFormula('=EDATE(VALUE("1 ' + today_date + '");-1)');

and presumably you would like it formatted as MMMM yyyy:

date.setFormula('=TEXT(EDATE(VALUE("1 ' + today_date + '");-1);"MMMM yyyy")');

That being said, it does seem like a bit of a convoluted way of doing it. You could do this with Javascript:

function monthOffset(date, months) {
  //returns the first day of the month with the required months offset
  var result = new Date();
  result.setFullYear(date.getFullYear(), date.getMonth() + months, 1);  
  return result;
}

And then use in your main function:

var calc_date = Utilities.formatDate(monthOffset(new Date(), -1), "GMT", "MMMM yyyy");

https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Date

Subtract date google spreadsheet script

1 Answers