Google Contacts Fields to fill variables in email template

Question

First of all, thank you for your time.

I have been looking for a while for a program, a script or anything that could help me automate a task that otherwise is going to take very long. See, i'm a french computer technician working for almost exclusively doctors here in France. The doctors receive results by email, the results are then imported to the patient's folder from the email automatically. But in order for them to receive that information we have to communicate an email address from a special domain + the doctor's ID that is like your driver's ID.

We use google contact as an address book because it's convenient. Since whenever we make a new maintenance contract with a doctor we input everything to google contact the info is already there. Sometimes we have up to 20 doctors in the same cabinet to set.

Link to a Google Sheet Contact Sample

The fields are the following :

Structure's Name : {{contact company name}} (all the doctors share the same structure) Strutre's Adress : {{contact full address}} (all the doctors share the same structure)

1. First doctor

• Last Name : {{last_name}}
• First Name : {{first_name}}
• eMail Address : {{email_address}} (this one is tagged MSSANTE in ggC)
• Doc's ID : {{custom_field}} (this is a custom field tagged RPPS in ggC)

2. Second doctor

• Last Name : {{last_name}}
• First Name : {{first_name}}
• eMail Address : {{email_address}} (this one is tagged MSSANTE in ggC)
• Doc's ID : {{custom_field}} (this is a custom field tagged RPPS in ggC)

So on and so on. Then this as to be sent to many laboratories all in BCC and the customers/doctors usually in CC

I was thinking of using google sheets or google's people API somehow... Can someone give me a strategy or some code to start ?

Again thanks to anyone who can help even a bit.

Answer 1

Try

function email() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const emails = ss.getSheetByName('LABS mails').getRange('C2:C').getValues().flat().filter(r => r != '').join(',')
  MailApp.sendEmail({
    to: emails,
    subject: 'titre du mail',
    htmlBody: body()
  })
}
function body() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const template = ss.getSheetByName('Mail Template (Exemple)')
  const docteurs = ss.getSheetByName('Doctors')

  let [headers, ...data] = docteurs.getDataRange().getDisplayValues()
  let debut = template.getRange('A2:A').getValues().flat().filter(r => r != '').join('<br>')
  let variable = template.getRange('B2:B').getValues().flat().filter(r => r != '').join('<br>')
  let fin = template.getRange('C2:C').getValues().flat().filter(r => r != '').join('<br>')
  const liste = ['{CABINET}', '{NOM}', '{PRENOM}', '{EMAIL}', '{RPPS}']
  const colonnes = [1,4,3,8,7]

  let message = debut
  data.forEach((r, row) => {
    var texte = variable
    for (var i = 0; i < liste.length; i++) {
      texte = texte.replace(liste[i], r[+colonnes[i] - 1])
    }
    message += texte + '<br><br>'
  })
  message += fin

  return (message)
}

Put the text as follows (you will need a little html tags)

The email will be