Why would setFormula() turn a working formula into one with #REF

Question

My situation is that I have entered a formula in my google sheet and it works correctly. I am putting that exact formula in as a setFormula() but when I refresh the document, it replaces cell references with #REF!.

I have checked to make sure single or double quotes are not interfering with the formula. My formula does reference another tab but putting single quotes around the Tab name has no effect either. I have also visited these posts but none answer the question.

#REF! being added to formula by setFormula() instead of an actual reference: IF(NOT(ISBLANK(N3)),#REF!,0))

getFormulas() and setFormulas() convert references to #REF

My Code is as follows...

function setFormulas(){

  var setup = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Set Up");
  var c1id = setup.getRange("J2").clearContent().setFormula("=index(\'MRTool\'!B1:B39,Match(D2,'\MRTool'\!A1:A39,0))");

I expect the formula in cell J2 to look like this: =index(MRTool!B1:B39,Match(D2,MRTool!A1:A39,0))

What I see is this: =index(#REF!,Match(#REF!,#REF!,0))

`visited these posts` Have you done what the post's answers/comments suggested? If so, add it to your question. — TheMaster, Feb 10 '19 at 07:19
Welcome. +1 for @TheMaster comments. On a tangent. I notice that you've escaped the single quote surrounding the _MrTool_ sheet. Two things about this: first, you don't need to single quote if there's not a space in the sheet name; second: if you leave out the escape characters, the code works. This is my equivalent (_working_) line of code: `var c1id = setup.getRange("J2").clearContent().setFormula("=index(MrTool!B1:B39,match(D2,MrTool!A1:A39,0))");`. — Tedinoz, Feb 10 '19 at 10:18
@TheMaster I have visited those sites and none have an example as similar to mine that I could use their solutions to answer this question, thus the reason for this post. Thanks for checking though. — Eureeka Havoc, Feb 10 '19 at 19:34
@Tedinoz Thanks for the insight as to the single quotes in referencing tabs. Something new I've learned. I have removed the '\ from the formula and I am still getting the reference error. Here is the [link](https://docs.google.com/spreadsheets/d/1N9svrsc6v6jlVF32C1kX8ukkGBx5HndCzFrdD8Cpz-8/edit?usp=sharing) if you wish to visit. This issue is happening on some lines but not all. I'm working in the Set Up tab and the code is in the Fix Formulas GAS. — Eureeka Havoc, Feb 10 '19 at 19:36
Did you and I quote `Add a SpreadsheetApp.flush() after clearing content.`? — TheMaster, Feb 10 '19 at 20:09
@TheMaster Yes I have. my current line of code is `var c1id = setup.getRange("J2").clearContent().SpreadsheetApp.flush().setFormula("=index(MRTool!B1:B39,Match(D2,MRTool!A1:A39,0))");` — Eureeka Havoc, Feb 10 '19 at 21:14

score 1 · Accepted Answer · answered Feb 13 '19 at 20:44

The OP experienced problems with using setFormula returning #REF rather than inserting the desired formula.

Modified code is shown below. There are several areas of change, and escape of characters is the major point. I am NOT and do NOT claim to be an expert in the rules for escaping of characters, I have simply found through trial and error the following to be necessary for this code to be successful.

when a formula refers to a sheet name, it's not necessary to have single quotes around a sheetname unless there is a space in the name.
when a formula includes a sheet name containing a space, then the sheetname should be surrounded by single quotes however the single quotes should not be escaped.
as a general statement, it's not necessary to escape double quotes unless the object contains spaces or other punctuation characters. For example: var c1r1ct (line 48) includes a date definition which contains spaces and commas, a colon and a forward slash. Similarly, var memData1 and var memData2 include an option containing a fullstop (period) as well as spaces.
IF statements appear to require all double quotes to be escaped; probably because the formula includes a variety of punctuation types.

Note: to escape a character insert a backslash

There were also several changes unrelated to formatting, but rather the formula was directed at the wrong cell.

var c1r1tz = setup.getRange("C4") & var c1r2tz = setup.getRange("C5") should refer to E4 and E5.
var c1r1ct = setup.getRange("D4") & var c1r2ct = setup.getRange("D4") should refer to H4 and H5
var c2r1tz = setup.getRange("C12") & var c2r2tz = setup.getRange("C13") should reference E12 and E13
var c2r1ct = setup.getRange("D12")& var c2r2ct = setup.getRange("D13") should reference H12 and H13
var c1r1ct, var c1r2ct, var c2r1ct, var c2r2ct, var mrsub1, var mrsub2, var memData1, var memData2 - escape double quotes (only). Because the formula included both single and double quotes.
var memData1, var memData2 - escape the period (.) in the formula

function so5461418804() {

  // WAR ROSTER FIXES
  var matchtool = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Match Tool");
  var setup = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Set Up");

  // Match Tool Tab Formulas
  // Clear Content
  var clear1 = matchtool.getRange("E8:F57").clearContent();
  var clear2 = matchtool.getRange("L8:M57").clearContent();

  // Reformat Alignments for Clan 1
  var c1center1 = matchtool.getRange("A:A").setHorizontalAlignment("center"); //Left Numbers
  var c1center2 = matchtool.getRange("B7:B").setHorizontalAlignment("center"); //MR Sub Column
  var c1center3 = matchtool.getRange("D7:E7").setHorizontalAlignment("center"); //Player ID, Name
  var c1center4 = matchtool.getRange("F7:F").setHorizontalAlignment("center"); //TH Column
  var c1left1 = matchtool.getRange("C8:E").setHorizontalAlignment("left"); //Check Mark, Player ID, Name

  // Reformat Alignments for Clan 2
  var c2center1 = matchtool.getRange("N:N").setHorizontalAlignment("center"); //Right Numbers 
  var c2center2 = matchtool.getRange("I7:I").setHorizontalAlignment("center"); //MR Sub Column
  var c2center3 = matchtool.getRange("K7:L7").setHorizontalAlignment("center"); //Player ID, Name
  var c2center4 = matchtool.getRange("M7:M").setHorizontalAlignment("center"); //TH Column 
  var c2left1 = matchtool.getRange("J8:L").setHorizontalAlignment("left");

  // MR or Sub Formula
  var mrsub1 = matchtool.getRange("B8:B57").clearContent().setFormula("=if(or(D8=\"\",D8=\"Player Not Found\"),\"\",if(iferror(Match(D8,'MR1'!$B$6:$B$67,0)),\"MR\",\"Sub!\"))");
  var mrsub2 = matchtool.getRange("I8:I57").clearContent().setFormula("=if(or(K8=\"\",K8=\"Player Not Found\"),\"\",if(iferror(Match(K8,'MR2'!$B$6:$B$67,0)),\"MR\",\"Sub!\"))");

  // Roster Member Data Formula
  var memData1 = matchtool.getRange("D8:D57").clearContent().setFormula("=if(or(C8=\"\",C8=\"\.     Blank\"),\"\",iferror(index('MR1'!$B$8:$D$87,(match(C8,'MR1'!$J$8:$J$87,0))),\"Player Not Found\"))");
  var memData2 = matchtool.getRange("K8:K57").clearContent().setFormula("=if(or(J8=\"\",J8=\"\.     Blank\"),\"\",iferror(index('MR2'!$B$8:$D$87,(match(J8,'MR2'!$J$8:$J$87,0))),\"Player Not Found\"))");

  //Set Up Tab Formuals
  var acctsleft = setup.getRange("C24").setFormula("=if(not(isblank(D19)),C19-SUM(C20:C23),\"\")"); //Account left in Break Down
  var abbr = setup.getRange("F21").setFormula("=if(D1=\"Grand Warden League\",\"GWL\",if(D1=\"Archer Queen League\",\"AQL\",if(D1=\"Barbarian King League\",\"BKL\",if(D1=\"Battle Machine League\",\"BML\",if(D1=\"Clan Castle League\",\"CCL\",\"\")))))"); //Set's the League Abbrivation for a shorter Standard Breakdown formula
  var stdBD12 = setup.getRange("D20").setFormula("=if(F21=\"GWL\",5,if(F21=\"AQL\",3,if(F21=\"BKL\",2,if(F21=\"BML\",1,\"\"))))"); //Sets the Standard Breakdown for TH12s
  var stdBD11 = setup.getRange("D21").setFormula("=if(F21=\"GWL\",12,if(F21=\"AQL\",8,if(F21=\"BKL\",5,if(F21=\"BML\",3,\"\"))))"); //Sets the Standard Breakdown for Th11s
  var stdBD10 = setup.getRange("D22").setFormula("=if(F21=\"GWL\",18,if(F21=\"AQL\",19,if(F21=\"BKL\",13,if(F21=\"BML\",8,\"\"))))"); //Sets the Standard Breakdown for TH10s
  var stdBD9 = setup.getRange("D23").setFormula("=if(F21=\"GWL\",0,if(F21=\"AQL\",0,if(F21=\"BKL\",5,if(F21=\"BML\",8,\"\"))))"); //Sets the Standard Break Down for TH9s

  //Clan 1
  var c1id = setup.getRange("J2").setFormula("=index(MRTool!B1:B39,Match(D2,MRTool!A1:A39,0))"); //Clan 1 ID#
  var c1r1n = setup.getRange("B4").setFormula("=index(MRTool!$D$3:$D$53,match(D2,MRTool!$A$3:$A$53,0))"); //Rep 1 Name
  var c1r2n = setup.getRange("B5").setFormula("=index(MRTool!$F$3:$F$53,match(D2,MRTool!$A$3:$A$53,0))"); //Rep 2 Name
  var c1r1tz = setup.getRange("E4").setFormula("=index(MRTool!$E$3:$E$53,match(D2,MRTool!$A$3:$A$53,0))"); //Rep 1 Time Zone
  var c1r2tz = setup.getRange("E5").setFormula("=index(MRTool!$G$3:$G$53,match(D2,MRTool!$A$3:$A$53,0))"); //Rep 2 Time Zone
  var c1r1ct = setup.getRange("H4").setFormula("=if(E4=\"\",\"\", TEXT('TZ Table'!$I$1+index('TZ Table'!H:H,match(E4,'TZ Table'!B:B,0))*time(ABS(index('TZ Table'!E:E,match(E4,'TZ Table'!B:B,0))),index('TZ Table'!G:G,match(E4,'TZ Table'!B:B,0)),0),\"dddd, mmm dd, h:mm AM/PM\"))"); //Rep 1 Current Time
  var c1r2ct = setup.getRange("H5").setFormula("=if(E5=\"\",\"\", TEXT('TZ Table'!$I$1+index('TZ Table'!H:H,match(E5,'TZ Table'!B:B,0))*time(ABS(index('TZ Table'!E:E,match(E5,'TZ Table'!B:B,0))),index('TZ Table'!G:G,match(E5,'TZ Table'!B:B,0)),0),\"dddd, mmm dd, h:mm AM/PM\"))"); //Rep 2 Current Time

  //Clan 2
  var c2id = setup.getRange("J10").setFormula("=index(MRTool!B1:B39,Match(D10,MRTool!A1:A39,0))"); //Clan 2 ID#
  var c2r1n = setup.getRange("B12").setFormula("=index(MRTool!$D$3:$D$53,match(D10,MRTool!$A$3:$A$53,0))"); //Rep 1 Name
  var c2r2n = setup.getRange("B13").setFormula("=index(MRTool!$F$3:$F$53,match(D10,MRTool!$A$3:$A$53,0))"); //Rep 2 Name
  var c2r1tz = setup.getRange("E12").setFormula("=index(MRTool!$E$3:$E$53,match(D10,MRTool!$A$3:$A$53,0))"); //Rep 1 Time Zone
  var c2r2tz = setup.getRange("E13").setFormula("=index(MRTool!$G$3:$G$53,match(D10,MRTool!$A$3:$A$53,0))"); //Rep 2 Time Zone
  var c2r1ct = setup.getRange("H12").setFormula("=if(E12=\"\",\"\", TEXT('TZ Table'!$I$1+index('TZ Table'!H:H,match(E12,'TZ Table'!B:B,0))*time(ABS(index('TZ Table'!E:E,match(E12,'TZ Table'!B:B,0))),index('TZ Table'!G:G,match(E12,'TZ Table'!B:B,0)),0),\"dddd, mmm dd, h:mm AM/PM\"))"); //Rep 1 Current Time
  var c2r2ct = setup.getRange("H13").setFormula("=if(E13=\"\",\"\", TEXT('TZ Table'!$I$1+index('TZ Table'!H:H,match(E13,'TZ Table'!B:B,0))*time(ABS(index('TZ Table'!E:E,match(E13,'TZ Table'!B:B,0))),index('TZ Table'!G:G,match(E13,'TZ Table'!B:B,0)),0),\"dddd, mmm dd, h:mm AM/PM\"))"); //Rep 2 Current Time
  var flsuh = SpreadsheetApp.flush()
}

Thank you @Tedinoz for the assistance. I did not expect you to fix my issue by doing it yourself, so I thank you for going above and beyond expectation. Your explanation is clear and concise. Thank you! A follow up question: - I see you removed all of the .clearContent(). Does .setFormula overwrite whatever is in the current cell no matter if it's clear or not? — Eureeka Havoc, Feb 14 '19 at 01:12
Argh. Last night... such a long time ;) My recollection was that it was hiccuping when clearcontent was left in, but ran flawlessly when I took it out. You might like to make the suggested edits (escaping and cell references but excluding the deletions of `clearcontents`) and try it out. If it works, great; if not, then go to plan B. — Tedinoz, Feb 14 '19 at 01:33

Why would setFormula() turn a working formula into one with #REF

1 Answers1

Linked