Google Apps Script setFormula function

Question

I'm facing some issues with Googles Apps Script while using the following code to insert ArrayFormulas in 5 specific cells:

function AddForm() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh=ss.getSheetByName('sheetname');
  var cell = sheet.getRange("Z2");
  cell.setFormula('=iferror(arrayformula(vlookup(J2:J,othersheetname!$L:$M,2,false)),"")');
  var cell = sheet.getRange("AA2");
  cell.setFormula('=iferror(arrayformula(vlookup(K2:K,othersheetname!$P:$Q,2,false)),"")');
  var cell = sheet.getRange("AB2");
  cell.setFormula('=iferror(arrayformula(vlookup(K2:K,othersheetname!$P:$Q,2,false)),"")');
}

I'm getting a "ReferenceError: Run_AddForm is not defined" error message and don't understand why.

Can anyone help, please?

Thank you in advance for your kind support

Here's the call function that is still having issues:

function ManualSGAConso() {
 Run_MID2019();
 Run_2019SC();
 Run_MID2020();
 Run_AddForm();
}

Marios · Accepted Answer · 2020-07-30T13:57:00.343

2

First of all, you made a small mistake in the AddForm() function:

sh should be sheet

  function AddForm() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet=ss.getSheetByName('sheetname');
  var cell = sheet.getRange("Z2");
  cell.setFormula('=iferror(arrayformula(vlookup(J2:J,othersheetname!$L:$M,2,false)),"")');
  var cell = sheet.getRange("AA2");
  cell.setFormula('=iferror(arrayformula(vlookup(K2:K,othersheetname!$P:$Q,2,false)),"")');
  var cell = sheet.getRange("AB2");
  cell.setFormula('=iferror(arrayformula(vlookup(K2:K,othersheetname!$P:$Q,2,false)),"")');
}

Since the error message you are getting is looking for the Run_AddForm() function, try to replace Run_AddForm() with AddForm():

function ManualSGAConso() {
 Run_MID2019();
 Run_2019SC();
 Run_MID2020();
 AddForm();
}

edited Jul 30 '20 at 13:57

answered Jul 30 '20 at 13:36

Marios

26,333
8
32
52

Thank you, Marios. I've tried it with **sheet**, but I keep getting the same error message – user13332855 Jul 30 '20 at 13:41
1

Can you share the full code with us by editing your original question? or at least the part that calls Run_AddForm ? Because as a standalone script, AddForm() works perfectly fine. – Marios Jul 30 '20 at 13:42
Yes, I've tried as a standalone script and it works perfectly. Thank you for your previous tip. The code I'm using to call the function AddForm() is called by:""function ManualSGAConso() { Run_MID2019(); Run_2019SC(); Run_MID2020(); Run_AddForm(); }"" The other functions are basically copy pastes from 3 other files that are consolidated into a single database. Thank you. – user13332855 Jul 30 '20 at 13:49
1

Please share with us the part of the code that does not work. Right now, the code you are sharing with us is working, therefore there is nothing to be answered or observed here. Edit your original question and include the code that does not work or accept my answer since I solved the part of the code you shared and asked a new question with the part of the code that does not work. – Marios Jul 30 '20 at 13:51
Please refer to the original posting, I've added the call function with the issues. Thank you. – user13332855 Jul 30 '20 at 13:55
@user13332855 try to replace Run_AddForm() with AddForm(). See also my edited answer. – Marios Jul 30 '20 at 13:55
Of course... I need some rest. Sorry for the inconvenience and thank you so much for your help with this issue! – user13332855 Jul 30 '20 at 14:00

Google Apps Script setFormula function

1 Answers1