尝试使用“设置公式”将代码添加到清除的工作表时出错 [英] Error when trying to add code back into cleared sheet using Set Formula

问题描述

我试图在Google工作表中创建手动归档功能（基于表单响应）。在这一点上，这是一个弗兰肯斯坦的努力，因为我已经收集了点点滴滴把它放在一起。我非常接近完成，但当我试图在清单中将公式重新添加到存档中时，我碰到了一堵墙。

I am attempting to create a manual archive function in a google sheet (based on form responses). It's a bit of a Frankenstein effort at this point as I've gathered bits and pieces to put it together. I am so close to completion, but I've hit a wall when I try to add formulas back into the sheet after clearing it to the archive.

我确定这对我来说并不奇怪，我觉得我在这里错过了一些简单的东西。下面的公式直接从活动电子表格中复制，他们工作正常，但由于某种原因，我无法解析脚本以便在清除表单后将其放回。

I'm sure it's no surprise that am new to this, and I feel like I am missing something simple here. The formulas below are copied directly from the active spreadsheet where they are working fine, but for some reason, I can't get the script to parse in order to put them back after clearing the sheet. I would appreciate any assistance anyone is willing to offer.

我收到错误消息：

I get the error:

参数列表后面的

缺少）。

Missing ) after argument list.

在cell.setFormula代码的两行代码中，不会在下面显示代码块：

on the two lines of "cell.setFormula" code that won't "code block" below:

function addFormulas(){

  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheet = sheet.getSheetByName("Form Totals");

  //Add formula back into Column A
  var cell = sourcesheet.getRange("A2:A1000");
  cell.setFormula("=D2");

 //Add formula back into Column U
  var cell = sourcesheet.getRange("U2:U1000");

  cell.setFormula("=IF(ISNUMBER(FIND("14.29",P2)),14.29,IF(ISNUMBER(FIND("5.24",P2)),5.24,""))");

  //Add formula back into Column V
  var cell = sourcesheet.getRange("V2:V1000"); 
  cell.setFormula("=IF(ISNUMBER(FIND("14.29",U2)),U2*Q2,IF(ISNUMBER(FIND("5.24",U2)),U2*Q2,""))");

}

这是怎么回事？我的公式是错误的，即使他们在电子表格中工作吗？

What's going on here? Are my formulas "wrong" even though they work in the spreadsheet?

推荐答案

在JavaScript中，双引号用于表示字符串类型。如果你在中放置任何东西，它会告诉JS代码解析器将这些双引号之间的任何东西当作一个字符串处理。

In JavaScript, double quotes are used to denote string type. If you place anything inside " ", it tells the JS code parser to treat anything between these double quotes as a string.

String也是Range类的setFormula（）方法的唯一有效参数类型，因此括号内的任何内容都应该是此类型的。

String is also the only valid argument type for setFormula() method of the Range class, so anything that goes inside the brackets should be of this type.

在你的公式中看看这部分

Take a look at this part in your formula

cell.setFormula("=IF(ISNUMBER(FIND("14.29",

语法解析器会识别第一个 pair 作为一个字符串，但14.29将被视为一个数字，因为这是字符串结束的地方。解析器将立即停止并抛出一个错误。

The syntax parser would recognize the part between the first pair of double quotes as a string, but 14.29 will be treated a a number as that's where the string ends. The parser will immediately stop and throw an error.

解决方法是在公式中使用单引号字符串，例如

The solution is to use single quotes for strings inside your formula, e.g.

range.setFormula("=IF(ISNUMBER(FIND('14.29',P2)),14.29,IF(ISNUMBER(FIND('5.24',P2)),5.24,''))");

这篇关于尝试使用“设置公式”将代码添加到清除的工作表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！