1

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!

Dean Ransevycz
  • 933
  • 8
  • 22
AdamG
  • 11
  • 4

1 Answers1

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

Dean Ransevycz
  • 933
  • 8
  • 22
  • Hi Dean, thanks so much for providing this answer. Can you clarify something for me? Does this approach require that I manually provide a dictionary of all email addresses which may be input through this function? "email_data_store[email].push(/* an array of your values */);" or does it collect the emails into the dictionary as it runs through? As a side thought, what do you think of a function that registers the emails to an array/dictionary as it goes, then increase the count (++) on a variable when a newly registered email in range == the same as any previous ones? – AdamG Nov 21 '17 at 00:17
  • @AdamG - i've added more explanation & some documentation links that address your questions. In short: the code creates your dictionary on the fly by creating dictionary keys as they are needed. By storing your data in arrays under the keys you can then do the tabular formatting you want. I don't see the need to create a seperate dict of addresses: you can get the number of data rows for each email address by querying `email_data_store[email].length` – Dean Ransevycz Nov 21 '17 at 01:59