0

I've searched everywhere on the web and on this site as well. What I want to do is relatively simple, however I don't know enough about javascript to write it myself but I am learning.

I have a google sheet with email addresses as well as data in 5 columns. Let's say there are 3 email addresses total. Now say there are 3 rows with the same email address and each row has new/different data than the previous row. I want to send JUST 1 email to that address with the 3 rows / 5 columns of data. There also may be an email address but with no data in the rows/columns. Obviously nothing should be sent from that row. Then there's the second email address. Repeat but send to the new email address. Next email, etc.

Here's the sheet to give you a clearer picture. I would like to keep the look/color of the table of column B through Column F in each email.

The following script had me most of the way there (sans the look/color of the table), however when I tried using it a few days ago it was sending a new email for each row of data instead of just one email for several rows of data...but it previously was working! (again sans the look/color of my sheet, it was simple text)

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("sheet1"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange("A2:m8");
  var data = dataRange.getValues();

  // Modified
  var temp = [];
  var emailAddress, data1, data2, data3, data4, data5;
  for (var i in data) {
    [emailAddress, data1, data2, data3, data4, data5] = data[i];
    var message = '\n\n' + data1 + ' ' + data2 + ' ' + data3 + ' ' + data4 + ' ' + data5;
    if (emailAddress || !data1) {
      if (temp.length > 0) {
        MailApp.sendEmail(temp[0], "Subject", temp.slice(1,temp.length).join("\n"));
        var temp = [];
      }
      temp.push(emailAddress, message);
      if (!dimension) break;
    } else {
      temp.push(message);
    }
  }
}

The format of the body of the email should look like the following, but again, I also want it to retain the look/feel/color from the sheet:

screenshot of data/sheet in email

Once that's working, I would like my email signature from Gmail included in the email. I'm not sure if I can get it exactly as is in my Gmail account, or just simple text via javascript.

I have this line but that's simple text. Any way to get my logo/formatting/etc. to match my Gmail or outlook account? 

 var signature = "\n---\nsample signature";

Your help is much appreciated. Thank you.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Ichi Futures
  • 3
  • 4
  • @Tanaike let me know what you think :) thank you. – Ichi Futures Mar 15 '18 at 14:50
  • **1)** [Don't use for-in with arrays](https://stackoverflow.com/questions/500504/javascript-for-in-with-arrays) **2)** Make an object (`{}`) with email address as keys in the `for` loop on `data`, e.g. `{"person@domain.com":{"subject": subjectText, "body": bodyText, "mySig": mySigText}, "person2@example.com":{....}, ... }`. Then once you have built the object, iterate over it to send the emails: `for(var email in mailData) { /* use data in mailData[email] ... */ }` For HTML formatting: https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object) – tehhowch Mar 15 '18 at 16:24
  • Appreciate that, @tehhowch I'm really not sure what to make of most of that so I'll keep reading. – Ichi Futures Mar 16 '18 at 21:45
  • @tehhowch - I have a list of 50 emails...each email has different data although each one uses the same columns. So say one email has 4 rows for of data, I need the script to send the 4 rows of data in that one email. I want to use the current code which was working without adding 50 different emails. So 4, 5 rows of data and one email. Right now it's sending an email for each row of data. Can we start there? What do I need to do? – Ichi Futures Mar 19 '18 at 22:09

0 Answers0