CODEWITHSUNDEEP
google-apps-script
Tom
Tom

Reputation: 173

Sorry ... Another question about on form submit trigger

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

}

Upvotes: 0

Views: 69

Answers (1)

ale13
ale13

Reputation: 6052

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

Upvotes: 1

Related Questions