Google apps script converting date within formula

Question

I'm using Google Apps Script to create a new set of tabs within a sheet, and everything is going well apart from one date within a formula. The code I'm using is

function MakeANewDaySheet() {
  var date = Utilities.formatDate(new Date(), "GMT", "dd/MM/yyyy")
  var spreadsheet = SpreadsheetApp.getActive();
...
...
  spreadsheet.getRange('O2').activate();
  spreadsheet.getCurrentCell().setFormula("=iferror(indirect(" + date + '!$B3),\"Please select a department\")')
}

The formula I want the script to put in the cell points to another sheet that's created during this script, and its name is simply today's date. When the formula is entered using the script it puts ' around the year, which I cant seem to stop it doing.

I'm wanting to use the formula '=iferror(indirect(02/06/2020!$B3),"Please select a department")' and its giving me '=iferror(indirect(2/6/'2020'!$B3),"Please select a department")'

I've tried changing around between " and ', including \ and not, hoping it was that easy, but no luck.

Can anyone point out what is probably (hopefully) a simple syntax error.

Iamblichus Iamblichus · Accepted Answer · 2020-06-03T09:04:44

This is how you would reference another sheet (tab) with INDIRECT:

=INDIRECT("'"&"03/06/2020"&"'!"&"$B3")

To set this formula via Apps Script, you have to use the escape character (\") each time a there is double quote in the formula. So you can do this:

var formula = "=IFERROR(INDIRECT(\"'\"&\"" + date + "\"&\"'!\"&\"$B3\"), \"Please select a department\")";
spreadsheet.getCurrentCell().setFormula(formula);

Note:

Also, if you want to set this formula to O2, there is not need to activate the cell first, you can just do this:

spreadsheet.getRange('O2').setFormula(formula);

Reference:

INDIRECT

Google apps script converting date within formula

2 Answers

Note:

Reference: