-1

I am working on an apps script that writes contact info (name and email) from google sheet into google contacts.

  there are 3 columns on google sheet, the first column is a timestamp, the second col is email, and the third col is name.

the Apps script works, as adding contact to google contact, however, I want when any email removed from the google sheet, that contact info removes from google contacts as well, upon google sheet update/edit. I tried some, but stuck and could not make it. I am new to Apps script.

I am grateful if help me with the script.

Here is what I got:

function AddToGoogleContacts() {

var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1");  
for (var i = 0; i < sheet.getLastRow()-1; i++) { 

var contactEmail = sheet.getRange(i+2,2,1,2).getValue(); 
var myContact = ContactsApp.getContact(contactEmail); 

 if (myContact === null){  ContactsApp.createContact(sheet.getRange(i+2,3,1,2).getValue(),"",sheet.getRange(i+2,2,1,2).getValue());  

 }
 }
 }
Cooper
  • 59,616
  • 6
  • 23
  • 54
DD99
  • 5
  • 3
  • All that you have now is creating contacts. Which contact do you wish to delete? – Cooper Apr 15 '21 at 18:28
  • Thanks for your reply; when any email or row of data removed from google sheet, I want that removed data also taken off the google contacts,the sheet is updated by google form (unsubscribe form), so anyone who is subscribed to the list may unsubscribe, so at anytime I want to have a clean email list in my google contacts. – DD99 Apr 15 '21 at 18:39
  • How do contacts get removed from the data sheet? – Cooper Apr 15 '21 at 18:45
  • Please describe the event object 'values' array for the unsubscribe form – Cooper Apr 15 '21 at 18:50
  • Hi I just want to confirm if when you add contact info in your sheet, how do you trigger your AddToGoogleContacts()? Do you use a button? custom menu? or onEdit trigger?. In addition, based on my understanding if the contact info was deleted in your sheet, you want it to be deleted google contacts as well. Again how do you plan/want to trigger that process? when you delete info in your sheet you will click a button? custom menu? something like that? – Ron M Apr 15 '21 at 21:50

1 Answers1

0
function delGoogleContact(email) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("sheet1");
  const vs = sh.getRange(2,1,sh.getLastRow()-1,3).getValues();
  vs.forEach((r,i)=>{
    if(r[1]==email){
      ContactsApp.deleteContact(ContactsApp.getContact(email));
    } 
  });
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • thanks for the code, I tried it, I removed one row from sheet, and run the code, it dose not remove contact from google contact, i get no error either. – DD99 Apr 15 '21 at 18:48
  • The code is setup to remove the contact whose email you provide in the parameter – Cooper Apr 15 '21 at 18:52
  • How can I know how to delete contacts that are no longer there? – Cooper Apr 15 '21 at 18:53
  • Do you wish to delete all contacts that are no longer in the list? – Cooper Apr 15 '21 at 18:53
  • I don't know about your contacts but my contacts tend to have a lot of emails so deleting them on the basis of email alone might not be such a good idea – Cooper Apr 15 '21 at 18:58
  • thanks for your reply, I get your point now, well, on my google sheet, I have one sheet which is linked to subscribe form, another sheet to unsubscribed form, and the third sheet is a clean list, with a sheet formula ( compare two sheets) so this third sheet has a clean list in it and an Apps script that sends contact info (email and name) to google contact, – DD99 Apr 15 '21 at 19:08
  • I was struggling to use: `var sh = SpreadsheetApp.getActive().getSheetByEmailAddress("Sheet1"); var currentEmail = sh.getRange("B:B").getValues().map(function(value); var contact = ContactsApp.getContactByEmailAddresses(contactIds[i]);` but I could not come up with the rest. – DD99 Apr 15 '21 at 19:10
  • If you want to unsubscribe i.e. delete a contact from a unsubscribe form submission. That's easy just take the email from e.values and send it to the function I've given you and it's done. – Cooper Apr 15 '21 at 19:14