Google Spreadsheet to add and edit/update Gmail Contacts

Question

Some Background:
(I wish I had more rep to flesh this out, as I would like to post images and links to my research).

I am using the Awesome Tables gadget on our company Intranet Portal (on Google Sites) for phone directories, page navigation, serial numbers and such. It is, well, Awesome!

The new project is to integrate the ability to add, and more importantly, edit/update Gmail Contacts directly from the intranet portal. I am using this example from the Awesome Tables site as my starting point.

What i need!:

A way for an edited Google Form to update a cell in the responses spreadsheet (on the correct Row) to "Needs Update". I have read that the code will need to reside in the Google Form for triggers to work (onFormSubmit doesn't work for editing if code is in spreadsheet).

Also, apologies for the code and if I am not phrasing coding terminology correctly, it is my first go at anything serious outside of some bash scripting. Also, if this can all be done some other way such that you can still have a "live" and editable reflection of your gmail contacts on a google site, I am open to that. Do please read on!

Where I am at:

Spreadsheet Code:

// Sheet Variables Below
var sheet = SpreadsheetApp.getActiveSheet();
var headerRows = 2;
var MaxRow = sheet.getLastRow(); 
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();     // Read all data
data.splice(0,headerRows);            // Remove header rows

function addContact() {
  for (var i = 0; i < data.length; i++) { 
    var row = data[i];
    var firstName = row[1]; 
    var lastName = row[2];      
    var emailAdd = row[3];
    var companyAdd = row[4];
    var positionAdd = row[5];
    var mobileAdd = row[6];
    var workphoneAdd = row[7];
    var addressAdd = row[8];
    var statusRow = row[9];
      if(statusRow != "Uploaded") {
        var contact = ContactsApp.createContact(firstName, lastName, emailAdd);
        contact.addPhone(ContactsApp.Field.MOBILE_PHONE, mobileAdd);
        contact.addPhone(ContactsApp.Field.WORK_PHONE, workphoneAdd);
        contact.addCompany(companyAdd, positionAdd);
        contact.addAddress(ContactsApp.Field.WORK_ADDRESS, addressAdd)

        var group = ContactsApp.getContactGroup("System Group: My Contacts");
        group.addContact(contact);

        // Finally, once we have uploaded the contact, set Status to "Uploaded".
        for (var iRow = 3; iRow <= MaxRow; iRow++) {
        sheet.getRange("K" + iRow).setValue('Uploaded');
        }
      }
  }
}


function updateContact() {
  for (var i = 0; i < data.length; i++) { 
    var row = data[i];
    var statusRow = row[9];  
      if(statusRow == "Needs Update") {
        // Retrieve Contact by email address and delete. If they have changed the email address though you are in big trouble and will receive an error. TODO!
        var contact = ContactsApp.getContact(row[3]); ContactsApp.deleteContact(contact);

        // Remove "Needs Update"
        for (var iRow = 3; iRow <= MaxRow; iRow++) {
        var value = sheet.getRange("K" + iRow).getValue();
          if(value == 'Needs Update'){
            sheet.setActiveRange(sheet.getRange(iRow, 11)).setValue('');
          }
        }
      }
  }
  // Re-run the addContact to effectively have "updated" the contact data. 
  addContact();
}

var formURL = 'https://<formURLgoesHERE>/viewform';
var sheetName = 'Form Responses';
var columnIndex = 11;
var statusIndex = 10;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 2; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnIndex-1] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      var status = "Just Added";
      var button = '<div style="text-align:center"><a href="' + editResponseUrl + '" ';
      button+= 'style="color:white!important;text-decoration:none;background-color:rgb(209,72,54);background-image:-webkit-linear-gradient(top,rgb(221,75,57),rgb(209,72,54));border:1px solid rgba(0,0,0,0);border-radius:2px;display:inline-block;font-size:11px;font-weight:bold;height:27px;line-height:27px;padding:0px 20px 0px 20px;text-align:center;text-transform:uppercase;white-space:nowrap"';
      button+= 'target="_blank">Edit entry</a></div>';
      sheet.getRange(i+1, statusIndex).setValue(status);
      sheet.getRange(i+1, columnIndex).setValue(button);
    }
  }
}

So, with the above, I can create a new contact from a form entry, and provided that the Status Column is "Needs Update", I can delete a contact and re-add the contact with new details (unless the email address is the value changed, which is a glaring flaw I know - bonus points if you can fix this too).

Summary:

Everything works fine for when they submit a new entry. But if they then hit the edit button and update their details in the form, I need a way to update the Status to "Needs Update", as that is what my "updateContact" function is looking for.

Would love thoughts or guidance, I have sunk hours into this and would really love to get it working. I feel it is "really close", I just need this one little variable for my function trigger to "get it over the line" and am hoping you can make that happen!

Tovly Deutsch Tovly Deutsch · Accepted Answer · 2015-02-20T04:32:27

I'm not sure if I completly understand your problem but here might be a solotion.

Creating the trigger

First you need to create a trigger to run the needsUpdate() function when the form is submitted. You can do this programmatically as shown below.

function onInstall(){  
 var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    ScriptApp.newTrigger("needsUpdate").forSpreadsheet(ss).onFormSubmit().create();
}

Alternatively, you can manually add the trigger in the script editor by selecting the "Resources" button in the top menu then select "Current project's triggers" then "Add a new trigger". Then you should select the needsUpdate() function in the first dropdown menu, "From spreadsheet" in the second dropdown menu, and "On form submit" in the third dropdown menu.

Creating the needsUpdate() function

Next, you would have to create a function that is run on the form submit. The function would go to the statusRow and add "Needs Update". The e passed to this function is data passed to it from the form submission.

function needsUpdate(e) {
// the columns and rows could change depending on how you spreadsheet is set up
var column = e.range.getColumn()
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
sheet.getRange(9, column).setValue("Needs Update");
}

Google Spreadsheet to add and edit/update Gmail Contacts

1 Answers