Although the linked speadsheet is correctly updated and the values from the resubmitted form are flagged as changed those values do not appear to be collected by

var formValues = e.namedValues;
var firstName = formValues['First Name'][0];
var lastName = formValues['Last Name'][0];
var emailAddr = formValues['Email Address'][0];
var eventRow = e.range.getRow();

The script fails when it tries to send and email the message Exception: Failed to send email: no recipient at sendEmail(Code:101:15) .... The script executed is called send email

Maybe when you resubmit a form only the changed values are picked up in enamedValues?

Another answer on StackOverflow suggested using e.range.getValues()

I tried that as follows

function sendEmail(e) {
  
    // Get Relevant Data from Form Submission
    var responseArr = e.range.getValues();
    var eventRow = e.range.getRow();

    Logger.log('Event Row', responseArr);

    var responseSheet = e.source.getSheetByName('Form Responses 1');
    const lastResponseCol = responseSheet.getLastColumn();
    var headerArr = responseSheet.getSheetValues(1,1,1,lastResponseCol);

    Logger.log('Header Row', headerArr);

    var firstName = responseArr[headerArr.indexOf('First Name')];
    var lastName  = responseArr[headerArr.indexOf('Last Name')];
    var memType   = responseArr[headerArr.indexOf('Membership Type')];
    var maaaNum   = responseArr[headerArr.indexOf('MAAA Number')];
    var emailAddr = responseArr[headerArr.indexOf('Email Address')];

}

The log showed both responseArr and headerArr as empty.

One thing I learnt was that Logger.log values ONLY show if you turn off "Show in real time"

Any help appreciated.

Although the linked speadsheet is correctly updated and the values from the resubmitted form are flagged as changed those values do not appear to be collected by

var formValues = e.namedValues;
var firstName = formValues['First Name'][0];
var lastName = formValues['Last Name'][0];
var emailAddr = formValues['Email Address'][0];
var eventRow = e.range.getRow();

The script fails when it tries to send and email the message Exception: Failed to send email: no recipient at sendEmail(Code:101:15) .... The script executed is called send email

Maybe when you resubmit a form only the changed values are picked up in enamedValues?

Another answer on StackOverflow suggested using e.range.getValues()

I tried that as follows

function sendEmail(e) {
  
    // Get Relevant Data from Form Submission
    var responseArr = e.range.getValues();
    var eventRow = e.range.getRow();

    Logger.log('Event Row', responseArr);

    var responseSheet = e.source.getSheetByName('Form Responses 1');
    const lastResponseCol = responseSheet.getLastColumn();
    var headerArr = responseSheet.getSheetValues(1,1,1,lastResponseCol);

    Logger.log('Header Row', headerArr);

    var firstName = responseArr[headerArr.indexOf('First Name')];
    var lastName  = responseArr[headerArr.indexOf('Last Name')];
    var memType   = responseArr[headerArr.indexOf('Membership Type')];
    var maaaNum   = responseArr[headerArr.indexOf('MAAA Number')];
    var emailAddr = responseArr[headerArr.indexOf('Email Address')];

}

The log showed both responseArr and headerArr as empty.

One thing I learnt was that Logger.log values ONLY show if you turn off "Show in real time"

Any help appreciated.

• google-apps-script
• google-forms

• The log showed both responseArr and headerArr as empty. This is true BUT... When a submission is edited, the submission is not appended to the Linked sheet; instead, the original submission is edited. AND if event Objects are used, the value of rowStart is the same for the original and edited submission. So the row number is know, and the location of values can be determined by using getRange where row= rowStart and column = the relevant column. – Tedinoz Commented 2 days ago
• Maybe when you resubmit a form only the changed values are picked up in enamedValues? Correct. So, if an edited submission is detected, do you want to send the email "as usual", or send an amended email of some sort? – Tedinoz Commented 2 days ago
• Related question: How to check if current submission is editing response or a new response – Tedinoz Commented 2 days ago

1 Answer 1

You have a Google Form that contains questions titled "First Name", "Last Name", "Membership Type", "MAAA Number" and "Email Address" You are using namedvalues to create values for an email but some or all of these render as blank for an "edited" submission.

The reason for this is twofold:

• event objects for an "edited" submission contains only those "namedValues" and "values" for fields that are edited (plus the Timestamp)
• when an edited submission is received in a linked spreadsheet, the response is NOT appended to the linked sheet. Instead the data in the original submission is modified and this is reflected in the rowStart/rowEnd event objects.

This code will supply the information that is required for an email regardless of whether it is an original submission or an edited submission. The key is using e.range.rowStart to identify the row that contains the relevant data.

// This function is bound to the linked spreadsheet.
// It is triggered by an installable `onFormSubmit` trigger

    function sendEmail(e) {
      Logger.log(JSON.stringify(e)) // DEBUG
      var editedRow = e.range.rowStart
      Logger.log("DEBUG: The edited row = "+editedRow)
    
      var responseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 4")
      var lastResponseCol = responseSheet.getLastColumn()
      var data = responseSheet.getSheetValues(1,1,responseSheet.getLastRow(),lastResponseCol)
      Logger.log("DEBUG: The header row values = "+data[0])
      Logger.log("DEBUG: The data row values = "+data[editedRow-1])
    
    
      var firstName = data[editedRow-1][data[0].indexOf('First Name')]
      var lastName  = data[editedRow-1][data[0].indexOf('Last Name')]
      var memType   = data[editedRow-1][data[0].indexOf('Membership Type')]
      var maaaNum   = data[editedRow-1][data[0].indexOf('MAAA Number')]
      var emailAddr = data[editedRow-1][data[0].indexOf('Email Address')]
      Logger.log("DEBUG: First Name:"+firstName+", Last Name:"+lastName+", Membership Type: "+memType+", MAAA Num:"+maaaNum+", Email Address:"+emailAddr)
    
      // OP to supply balance of function
    
    }

Linked Sheet before Edited Submission

Linked Sheet after Edited Submission

Event Objects - Original Submission:

{
  "authMode":"FULL",
  "namedValues":{"Email Address":["[email protected]"],"Timestamp":["10/02/2025 23:22:34"],"MAAA Number":["6543"],"Last Name":["Star"],"First Name":["Ringo and barbara"],"Membership Type":["Life"]},
  "range":{"columnEnd":6,"columnStart":1,"rowEnd":3,"rowStart":3},
  "source":{},
  "triggerUid":"1333227230",
  "values":["10/02/2025 23:22:34","Ringo and barbara","Star","Life","6543","[email protected]"]
}

Event Objects - Edited Submission:

{
  "authMode":"FULL",
  "namedValues":{"Email Address":[""],"Timestamp":["10/02/2025 23:29:56"],"MAAA Number":[""],"First Name":["Ringo and Barbara"],"Last Name":["Starkey"],"Membership Type":[""]},
  "range":{"columnEnd":3,"columnStart":1,"rowEnd":3,"rowStart":3},
  "source":{},
  "triggerUid":"1333227230",
  "values":["10/02/2025 23:29:56","Ringo and Barbara","Starkey","","",""]
}