为什么setFormula()使用#REF将工作公式变成一个工作公式 [英] Why would setFormula() turn a working formula into one with #REF

问题描述

我的情况是我已经在Google工作表中输入了一个公式，它可以正常运行.我将确切的公式作为setFormula()放入其中，但是当我刷新文档时，它将用#REF!替换单元格引用.

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！由setFormula()而不是实际引用添加到公式中:IF(NOT(ISBLANK(N3))，#REF！，0))

getFormulas()和setFormulas()将引用转换为#REF

我的代码如下...

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))");

我希望单元格J2中的公式如下所示: =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))

我看到的是: =index(#REF!,Match(#REF!,#REF!,0))

推荐答案

OP在使用setFormula返回#REF而不是插入所需公式时遇到了问题.

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.

• 当公式引用工作表名称时，除非工作表名称中有空格，否则不必在工作表名称周围使用单引号.
• 当公式包含包含空格的工作表名称时，则工作表名称应用单引号引起来，但不应转义单引号.
• 一般来说，除非对象包含空格或其他标点符号，否则不必转义双引号.例如:var c1r1ct(第48行)包括日期定义，其中包含空格和逗号，冒号和正斜杠.同样，var memData1和var memData2包含一个选项，其中包含句号(句点)以及空格.
• IF语句似乎要求转义所有双引号；可能是因为该公式包含各种标点符号类型.

• 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")应该指的是E4和E5.
• var c1r1ct = setup.getRange("D4")& var c1r2ct = setup.getRange("D4")应指H4和H5
• var c2r1tz = setup.getRange("C12")& var c2r2tz = setup.getRange("C13")应引用E12和E13
• var c2r1ct = setup.getRange("D12")& var c2r2ct = setup.getRange("D13")应引用H12和H13
• var c1r1ct，var c1r2ct，var c2r1ct，var c2r2ct，var mrsub1，var mrsub2，var memData1，var memData2-转义双引号(仅).因为公式同时包含单引号和双引号.
• var memData1，var memData2-跳过公式中的句点(.)

• 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()
}

这篇关于为什么setFormula()使用#REF将工作公式变成一个工作公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！