Google-sheets – TypeError: Cannot read property ‘range’ of undefined at onformsubmit again after creating an event object

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-forms

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+"))");

Best Answer

How to Fix your problem

Manually install the onFormSubmit trigger.
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.
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.
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)
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.

Related Solutions

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Google-sheets – Import JSON to Google Sheet using Google Apps Script

As JPV pointed out, the line

var jsonData = ImportJSON(apiUrl);

is not valid in Apps Script (unless you have defined ImportJSON function somewhere). To fetch something from an external URL in Apps Script, one uses UrlFetchApp. So the line could be

var jsonData = JSON.parse(UrlFetchApp.fetch(apiUrl).getContentText());

After this, jsonData is a JavaScript object parsed from the JSON string returned by the server. To import it in a spreadsheet, one has to create a double array suitable for passing to setValues. How to do this depends on the structure of the object.

The pullJSON gist that you referenced gives a simple model of this process, which works when jsonData is already an array. But different APIs work differently: for example, Stack Exchange API returns an object with several "wrapper" properties, one of which is items that holds an array of objects of interest. So in this case one would loop over the elements of jsonData.items, extracting the properties of each object in the array and pushing them into a double array.

Best Answer

Related Solutions

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

Google-sheets – Import JSON to Google Sheet using Google Apps Script

Related Topic