Is it possible to access my Google contacts from Google Sheets? I've created a simple spread sheet where I enter information for my drivers. Once the data on a row is entered it may be modified by other users that access the same sheet, but eventually - one of the users will need to send the data in that row to a specific driver via email. I currently accomplish this by using a Google Sheets Script to take the row values and build a mailto: hyperlink onEdit() in the last cell:
var emailtoString = '=HYPERLINK("mailto:[email protected]?&subject=' + subjectData + '&body=' + formatedData + '", "send email")';
e.source.getActiveSheet().getRange(row,9).setValue(emailtoString);
Which is working as expected and allows for whatever users is clicking the hyperlink to open the preformated gmail in their account - they can then delete the "[email protected]" email address that is allowing the hyperlink formula to create and then they have access to their contacts in the to: field - so if they start typing a user name, a selection list displays to let them select the contact.
This works, but separates the process of sending the email from the sheets script - just to get access to the drop down contacts list. If I could access that TO: dialog directly from the sheet cell - so the user sending the email is really just selecting an address from their contacts, then I could use the sheets script to just send the email message without having to open a gmail message and I could further flag another cell as "Message sent to emailaddress". By using the hyperlink it is making it difficult for me to figure out how to set a flag letting other users know an email has already been sent to this driver. So I am wondering if its possible avoid all this hyperlink creation and instead, just present the user(s) of the spread sheet with a selection dialog from the user accessing the sheets personal contacts so they can select someone to send the email and I can script sending the email rather than building the Hyperlink to open Gmail.
