Consolidating multiple list entries into one email (where a row associates content with email address)

Question

My google sheets system reads through live list and sends automated emails to the person responsible for the content in a given row.

This list includes variables such as date, description, and the assignee's email. The list runs row-by-row, so due to the code that I am using, one person may receive several emails for each row to which they are assigned.

I would like to change the code so that it compiles like-email addresses on the list and sends the content of each row in a single email (ideally formatted as a table). How can I achieve this?

The script I am currently using is below:

function AssignmentEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Assignments");
  var range = sheet.getRange("A3:G950");
  var info = range.getValues();
  for (i in info) {
    var col = info[i];
    var date = Utilities.formatDate(col[0], "PST", "YYYY-MMM-dd");
    var observation = col[1];
    var lab = col[2];
    var contact = col[3];
    var action = col[4];
    var email = col[6];
    if (email="distribution list address") {
      MailApp.sendEmail(email, "*Text and variables for group*");
    }
    else if (email!="") {
      MailApp.sendEmail(email,"*Text and variables for individual*");
    }
  }
}

Thanks for your help!

Answer 1

You could push each row to an array in a dictionary using the email address as the key. You'd use JavaScript object to do this. The dictionary will store your data in in key:value pairs, with the email address as the key & an array of values storing the data you want to send to that address. What you'll end up with is dictionary looking a bit like this:

{"email.address@domain.tld": [['data row 1', 1, 'foo'],
                              ['data row 2', 2, 'bar']
                             ],
 "another.email@domain.tld": [['only one data row', 0, 'baz']],
 "group.email@domain.tld":   [['1st of many', 10, 'lorem'],
                              ['2nd row', 20, 'ipsum'],
                              ['3rd row', 30, 'dolor'],
                              ['Nth row', 100, 'si amet']
                             ]
}

So, you extract the email address from your data row & look for that email in the keys of your dictionary:

• if the email is present, push the new row of data to the array that at that key;
• if not, create a new array with the email address as they key & the row of data as the first element.

Once the dictionary is populated you can then iterate over the keys using the for(var key in dictionary){} construction. You access the values under each key in the form dictionary.key or dictionary[key]. It's just an array with names instead of numbers for the indices! (Actually not, but the analogy suffices.) So you can access the first element of the array under a given key in the form dictionary[key][0] (or dictionary.key[0]). And you can still use the value in key (in your case, the email address), so you could write Logger.log("key = %s, values = %s", key, dictionary[key]).

The code would look something like this:

/*...connect to your data source as above...*/
var info = range.getValues();
/* Create an empty JS Object to provide our dictionary.
 *+ we'll add each email address as a dict key as we see it.
 *+ each key will point to an array which will be the data 
 *+ to be entered into each email to the recipient address (the key) */
var email_data_store = {}; 
for (i in info) {
  var col = info[i];
/*...variable assignments as above...*/
  var email = col[6];
  if(email != ""){
    if(!(email in email_data_store)){ // Does a key matching this email already exist?
      // if not, create it:
      email_data_store[email] = [];
      // so now we have an empty array under the key `email`
    }
    email_data_store[email].push(/* an array of your values */);
  }
}

// now iterate over the dict to format the emails & send
for(var email in email_data_store){
  /* in here, iterate over the 2D arrays in email_data_store[email]
   *+ You can use array notation to address all items, 
   *+ so that you don't have the potential confusion of 
   *+ mixing array & object notation */
  for(var i = 0, lim = email_data_store[email].length; i < lim; ++i){
    /* format your data here */
  }
  MailApp.sendEmail(email, /* your formatted email body */);
}
/* whatever cleanup you want to do before ending your function */

More documentation: MDN on JS Objects