Sorry ... Another question about on form submit trigger

Question

I have a script to turn form responses into contacts in contactsApp. When I run it from the script editor, it works.

I would like submission of the form associated w the spreadsheet to trigger the script so have created an on form submit trigger, but ut does not work (needless to say).

Is the explanation for the trigger not working that there's some kind of delay between the form submission and the spreadsheet getting the new data? So the script is triggered before it has data to work with.

Anyway, here's the code:

function oneNewContact() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shts = ss.getSheets();
  var lr = shts[0].getLastRow();

  var acol = shts[1].getRange(1, 1, lr-1, 1).getValues();
  var drng = shts[1].getRange(1, 2, lr-1, 6).getValues();

  for(var i = 0; i < lr-1; i++) {

    if(acol[i][0]!==1) {     

    var first = drng[i][0];
    var surname = drng[i][1];
    var phone = drng[i][2];
    var email = drng[i][3];
    var consentDate = drng[i][5];
    var grp = 'qcbread';

    //create contact
      var contact = ContactsApp.createContact(first, surname, email);
      var contactID = contact.getId();

    //add info via bug workaround ie getting the new contact via contactID 
    contact = ContactsApp.getContactById(contactID); 
      console.log(contact.getFullName());
    contact.addPhone('mobile', phone);    
    contact.setNotes('contact consent given to MQC on '+ consentDate);

    //update contact
    var group = ContactsApp.getContactGroup(grp);
    contact = contact.addToGroup(group);

    }// end of if

    //added contact marked as processed
    var acell = i + 1;// add 1 to array counter
    var mark = shts[1].getRange('A'+ acell );
    mark.setValue(1);

  }// end of for loop

}

Don't you want this var lr = shts[0].getLastRow(); to be this var lr = shts[1].getLastRow(); — Cooper
I don't understand why you want to go through every line of the form submission sheet on every form submission. — Cooper

Unknown Unknown · Accepted Answer · 2020-04-20T08:09:03

In order for the script to work as intended, you should make use of event objects. In this way you are able to retrieve the data wanted directly and not go through every line of the form submission sheet every time a new form submission is sent.

function newContact(e) {

    var sht = e.source.getActiveSheet();
    var row = e.range.getRow();

    var drng = sht.getRange(row, 2, 1, 6).getValues();
    // drng is a single row 2D array

    var first = drng[0][0];
    var surname = drng[0][1];
    var phone = drng[0][2];
    var email = drng[0][3];
    var consentDate = drng[0][5];
    var grp = 'qcbread';

    //create contact
    var contact = ContactsApp.createContact(first, surname, email);
    var contactID = contact.getId();

    //add info via bug workaround ie getting the new contact via contactID 
    contact = ContactsApp.getContactById(contactID);
    contact.addPhone('mobile', phone);
    contact.setNotes('contact consent given to MQC on ' + consentDate);

    //update contact
    var group = ContactsApp.getContactGroup(grp);
    contact = contact.addToGroup(group);

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sht2 = ss.getSheets()[1];
    sht2.getRange(row - 1, 1).setValue(1);

}

Reference

Apps Script Event Objects

Sorry ... Another question about on form submit trigger

1 Answers

Reference