I've been trying to save this macro but I get the error message at line 29. However, everything looks right to me? I cannot understand how to get rid of the error. I'd love some help.

Here's the code:

function FVMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('2:2').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('A3:B4').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A2:B4'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A2:B4').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('A3'));
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Fixed Data'), true);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('FAIR VALUE'), true);
  spreadsheet.getRange('C2').activate();
  spreadsheet.getRange('\'Fixed Data\'!B1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('N3:N4').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('N4'));
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('N2:N4'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('W3:W4').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('W4'));
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('W2:W4'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('D3:H3').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('D2').activate();
  spreadsheet.getCurrentCell().setValue('=iferror(arrayformula(if(B2:B="",,Ln(B2:B))))');
  spreadsheet.getRange('E2').activate();
  spreadsheet.getCurrentCell().setValue('=iferror(arrayformula(if(C2:C<=0,,ln(C2:C))))');
  spreadsheet.getRange('F2').activate();
  spreadsheet.getCurrentCell().setValue('=arrayformula(iferror(if(B2:B="",,'FV Charts'!B4*ln(B2:B) 'FV Charts'!C4)))');
  spreadsheet.getRange('G2').activate();
  spreadsheet.getCurrentCell().setValue('=arrayformula(iferror(if(F2:F="",,exp(F2:F))))');
  spreadsheet.getRange('H2').activate();
  spreadsheet.getCurrentCell().setValue('=arrayformula(if(F2:F="",,E2:E-F2:F))');
  spreadsheet.getRange('H3').activate();
};

This line causes the problem:

spreadsheet.getCurrentCell().setValue('=arrayformula(iferror(if(B2:B="",,'FV Charts'!B4*ln(B2:B) 'FV Charts'!C4)))');

CodePudding user response：

Replace the line which errors out with:

spreadsheet.getCurrentCell().setValue("=arrayformula(iferror(if(B2:B=\"\",,'FV Charts'!B4*ln(B2:B) 'FV Charts'!C4)))");

The error stemmed from the fact that you were using single quotes ' for the Sheet name instead of " and that made the parentheses not close properly. However, considering the fact that referencing sheet names in a formula has to be done by using single quotes, I have replaced the beginning single quote with a double quote and also escaped it using \ for the B2:B range such that everything closes properly.

You can also escape the single quote from your line like this:

spreadsheet.getCurrentCell().setValue('=arrayformula(iferror(if(B2:B="",,\'FV Charts\'!B4*ln(B2:B) \'FV Charts\'!C4)))');

Reference

• Handling text — strings in JavaScript ;