1

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.

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?

TheEsnSiavashi
  • 1,245
  • 1
  • 14
  • 29
Marsha
  • 13
  • 3

2 Answers2

1

You must be carefull with the use of the "". Like you write it, the console get it like 

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

and he don't find the missing ).

You should write it like 

cell.setFormula("=IF(ISNUMBER(FIND(\"14.29\",U2)),U2*Q2,IF(ISNUMBER(FIND(\"5.24\",U2)),U2*Q2,\"\"))");
Pierre-Marie Richard
  • 1,914
  • 2
  • 19
  • 25
1

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 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",

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,''))");
Anton Dementiev
  • 5,451
  • 4
  • 20
  • 32
  • My spreadsheet didn't like the new formula format (single quotes), but it worked when I changed the javascript double quotes to singles i.e. `cell.setFormula('=IF(ISNUMBER(FIND("14.29",P2)),14.29,IF(ISNUMBER(FIND("5.24",P2)),5.24,""))');` Thank you! – Marsha Jun 20 '17 at 18:44
  • JavaScript could use single quotes or double quotes to declare a string. See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Grammar_and_types and [When to use double or single quotes in JavaScript?](https://stackoverflow.com/q/242813/1595451) – Rubén Jun 26 '17 at 21:07