TypeError: Cannot read property 'range' of undefined at onformsubmit again after creating an event object

Question

This is what I have written in Script editor so as to auto-apply a formula to a new added row when a new response is submitted on the Google Forms linked to my spreadsheet. First I was using onFormSubmit(e) function without creating an event object, resulting the error:TypeError: Cannot read property 'range' of undefined at onFormSubmit. then, as suggested, I created the event object onFormSubmitEvent() but still the very error is occurring.As you can see in photo, the first column and the first 5 rows are not used for the Form responses. the 4th row shows form questions, 5th row just column numbers, from 6th row the responses are collected(may this also be one of the reasons?). Please help to solve the problem.

function onFormSubmitEvent() {
  var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
  var data = dataRange.getValues();
  var headers = data[0];
  for (var row=1; row < data.length; row++) {
    var e = {};
    e.values = data[row].filter(Boolean);
    e.range = dataRange.offset(row,0,1,data[0].length);
    e.namedValues = {};

    for (var col=0; col<headers.length; col++) {
      e.namedValues[headers[col]] = [data[row][col]];
    }

    onFormSubmit(e);
  }
}


function onFormSubmit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = e.range;
  var row1 = range.getRow();
  var sheet = range.getSheet();

  sheet.getRange(row,17).setFormula("=if($O"+row+"=\"UZS\",index(googlefinance(\"currency:UZSUSD\",\"price\",$B"+row+"),2,2)*$P"+row+",if($O"+row+"=\"EUR\",index(googlefinance(\"currency:EURUSD\",\"price\",$B"+row+"),2,2)*$P"+row+",$P"+row+"))");

Answer 1

How to Fix your problem

1. Manually install the onFormSubmit trigger.

2. When installing the Trigger, under "Choose which function to run", choose the function onFormSubmit (the trigger event argument () isn't shown when creating the trigger.

3. There should only be one onformSubmit trigger, and only function executed by the trigger. If you find that the function onFormSubmitEvent is already installed, then delete it from the trigger.

4. Regarding function onFormSubmitEvent:

   • Delete line 15 [onFormSubmit(e);]. Your function onFormSubmit() will run via the trigger when a Form Response is received.
   • If you want the rest of the code in the onFormSubmitEvent function to be executed when a Form Response is received, then add it to the function onFormSubmit(e)

5. In your function onFormSubmit(e)

   • Add a curly bracket } to close the function
   • Line 7 (setformula) is failing because it uses a variable called row but row is not defined. The variable that is actually defined is called row1 - on Line 4. The easiest thing to do would be to edit line 4 so that it reads var row = range.getRow();

Once you have done these things and saved the changes to the functions in the Script editor, submit a new Form. The trigger will execute onFormSubmit(e) and the formula will be set.

Why are you having a problem.

There two reasons.

1 - Syntax errors in your script: the curly bracket missing to close the function and using the wrong variable name in line 7

2 - Not having installed the onFormSubmit trigger.

Just to be clear: onFormSubmit is the name of an installable trigger. It runs when a form response is received. When you install the trigger, you nominate which of your script functions is to be executed by the trigger. If you wish, it is possible to use a script function named onFormSubmit(), but this isn't obligatory - your function could have any name.

In order to access Event Objects, you must assigning an argument to the function being triggered. Most often, people use "e", or "event", but any argument would do.

Your function onFormSubmitEvent CANNOT call onFormSubmit(e) as a sub-routine. Only the function installed under the trigger can access Event Objects.