Full script not running when onFormSubmit Triggered but will work manually with onEdit()

Question

function BatteryInspection(e) {
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn()
  var sheet = range.getSheet();
  Logger.log("The row is " + row + " and column is " + col + " the sheet is " + sheet);

  var sheet, sheetName, colToCapitalize;

  var sheetName, colToCapitalize;
  colToCapitalize = 2;
  sheetName = "FormResponses1";
  if (sheet.getName() !== sheetName || e.range.rowStart < 2 || e.range.columnStart !== colToCapitalize || typeof e.value == "object") return;
  e.range.setValue(e.value.toUpperCase());

  sheet.getRange(row, 10).setFormula("=SUBSTITUTE(B" + row + ",\" \",\"\")");

  var cellcopy = sheet.getRange(row, 10);
  var cellvalues = cellcopy.getValues();

  sheet.getRange(row, 2).setValues(cellvalues);
  cellcopy.clearContent();

  sheet.getRange(row, 8).setFormula("=VLOOKUP(B" + row + ",LookUpTable!A2:C1001,2,0)");
  sheet.getRange(row, 9).setFormula("=VLOOKUP(B" + row + ",LookUpTable!A2:C1001,3,0)");
}

The outcome I want is that the above script runs when a new row of data is added from a google form to the worksheet "FormResponses1". I have created the google form already, and new data is added to the sheet "FormResponses1" when a user submits the form.

Here is a YouTube video of the screen showing the problem I'm having

The above code runs fine and does what I need if I manually add a new value to a cell e.g. if I write "dll 463" in cell "B2" on sheet "FormResponses1".

I have set up a trigger "From spreadsheet - On form submit" for the function onEdit shown above, using Edit - Current project's triggers from the toolbar in the <> script editor view.

The trigger does seem to fire, and if I view the logs after submitting a new response from the form, I can see the log shows the row and column that got edited via the Logger.log line in the code above.

I have added more Logger.log lines to the above code to see how far the script runs when it is triggered by the "From spreadsheet - On form submit" trigger, but no other logs are shown after the row and column edit log.

What makes this more weird for me, is that I could get the trigger to work and run the code yesterday. Albeit, it was only writing in the setFormula lines at the end of the current script, and since then I have added the capitalise column values, and substitute formula to the script.

I have tried: - Deleting and adding a new trigger. - Creating a new version - Deleting form repsonses - Changing the function from onEdit() to onFormSubmit() - Unlinking the spreadsheet and adding a new spreadsheet (with the same sheet names and data) with the script copied into the new spreadsheet script editor and deleting the old spreadsheet.

Answer 1

The issue you are having (besides the code you wrote is also a little messy) is that when an event is triggered by an "On edit" or by an "On form submit", the Event Objects are different for them, therefore they will not have the same properties and you can apply the same logic as you were doing in your code.

This is how an Edit Object looks like and this is how a Form submit Object looks like.

Knowing that. I modified your code in the following way:

In your main function, I check what is the cause of the trigger, if it's an edit or a submit event, then depending on the event and if the conditions for both of them are true, I call the onEditChange or onSubmitChange functions.

function BatteryInspection(e) {
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var sheet = range.getSheet();
  var colToCapitalize = 2;
  var sheetName = "FormResponses1";
  // Let's see if it is a form or edit object, checking the namedValues properties
  if(!e.namedValues){
    // Edit event
    // Check the right conditions for an edit event
    if (sheet.getName() !== sheetName || row < 2 || col !== colToCapitalize || typeof e.value == "object") return;
    else onEditChange(e, sheet, row);
  } else {
    // Submit Event
     // Check the right conditions for a submit event
    if (sheet.getName() !== sheetName || row < 2 | typeof e.values != "object") return;
    else onSubmitChange(e, sheet, row);
  }
}

Inside the onEditChange and onSubmitChange functions, I set the values in the right cell to uppercase and then call the setFormulas function.

// Call when there is an edit event 
function onEditChange(evt, sheet, row){
  // Uppercase the value where the triggered occured
  evt.range.setValue(evt.value.toUpperCase());
  setFormulas(sheet, row);
}

// Call when there is an submit event 
function onSubmitChange(evt, sheet, row){
  // Uppercase the value just in the value of the second col
  sheet.getRange(row, 2).setValue(evt.values[1].toUpperCase());
  setFormulas(sheet, row);
}

In the setFormulas function as the name indicates, I just set the formulas that you were setting previously in your main function BatteryInspection.

// Function for setting the formulas
// Call whether an edit or submit event happens
function setFormulas(sheet, row){
  sheet.getRange(row, 10).setFormula("=SUBSTITUTE(B" + row + ",\" \",\"\")");
  var cellcopy = sheet.getRange(row, 10);
  var cellvalues = cellcopy.getValues();
  sheet.getRange(row, 2).setValues(cellvalues);
  cellcopy.clearContent();
  sheet.getRange(row, 8).setFormula("=VLOOKUP(B" + row + ",LookUpTable!A2:C1001,2,0)");
  sheet.getRange(row, 9).setFormula("=VLOOKUP(B" + row + ",LookUpTable!A2:C1001,3,0)");
}

The whole code now would look like this:

function BatteryInspection(e) {
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();
  var sheet = range.getSheet();
  var colToCapitalize = 2;
  var sheetName = "FormResponses1";
  // Let's see if it is a form or edit object, checking the namedValues properties
  if(!e.namedValues){
    // Edit event
    // Check the right conditions for an edit event
    if (sheet.getName() !== sheetName || row < 2 || col !== colToCapitalize || typeof e.value == "object") return;
    else onEditChange(e, sheet, row);
  } else {
    // Submit Event
     // Check the right conditions for a submit event
    if (sheet.getName() !== sheetName || row < 2 | typeof e.values != "object") return;
    else onSubmitChange(e, sheet, row);
  }
}

// Call when there is an edit event 
function onEditChange(evt, sheet, row){
  // Uppercase the value where the triggered occured
  evt.range.setValue(evt.value.toUpperCase());
  setFormulas(sheet, row);
}

// Call when there is an submit event 
function onSubmitChange(evt, sheet, row){
  // Uppercase the value just in the value of the second col
  sheet.getRange(row, 2).setValue(evt.values[1].toUpperCase());
  setFormulas(sheet, row);
}

// Function for setting the formulas
// Call whether an edit or submit event happens
function setFormulas(sheet, row){
  sheet.getRange(row, 10).setFormula("=SUBSTITUTE(B" + row + ",\" \",\"\")");
  var cellcopy = sheet.getRange(row, 10);
  var cellvalues = cellcopy.getValues();
  sheet.getRange(row, 2).setValues(cellvalues);
  cellcopy.clearContent();
  sheet.getRange(row, 8).setFormula("=VLOOKUP(B" + row + ",LookUpTable!A2:C1001,2,0)");
  sheet.getRange(row, 9).setFormula("=VLOOKUP(B" + row + ",LookUpTable!A2:C1001,3,0)");
}

Docs

For more info, you can also check:

• Class Range

• Installable Triggers Restrictions