CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
J.Mapz
J.Mapz

Reputation: 511

Perform text to columns in appscript and send as table

I currently have a code that sends a concatenated set of data from a google sheets to gmail.

The input is like this in google sheets:

1st image

enter image description here

while the output is like this in email:

2nd image

enter image description here

However, I want to ask if it is possible to do a 'text-to-column' type of way to split the activities and due dates into two columns, and send them in a form of a table, which should look like this:

3rd image

enter image description here

I've tried scanning several sites but it would seem replicating a 'text-to-column' approach is not a common thing (not sure) in app script. I've as well searched for alternative measures, but it doesn't work well with how our template is structured, which has email recipients in one column while the items to be sent to them on another column. May I please ask for your advise on how to go about with this - either on the 'text-to-column' replication or a new method of doing it.

Thanks!

----Edit----

Just to share, we have this table of activities, email recipients and due dates.

4th image

4th image

From this list, we now use several formulas to arrive with the first image above (which only contains the activities that are due and as well their email recipients). This is since we actually have around hundreds of activities, and we don't want to be sending emails per row of due activity, but per email recipient and with only his/her due activities.

Our code loops to the different recipients above and send them each of their own past due activities. What it sends, however, is the one in the second image, while what we want is the third image.

After reading the comments, maybe we could have instead asked: how to extract past due activities from our source data, create tables of it for each of the recipients, and send it to each of the recipients. Through this, we would no longer do the concatenating of past due activities per recipient (1st image).

Upvotes: 0

Views: 324

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Assuming that:

  • You have few columns of data that you want to send by email.
  • Some email addresses are repeated and you want to send only one email to each recipient (one recipient can have several activities).
  • You want to build an HTML table for each email that you send.

In this case, I would recommend getting an array of the different recipients that exist in your source data, and then send the emails based on that. I wrote a small code to accomplish this (check inline comments to get more details of what the code is doing at each line):

function sendEmails() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Sheet1"); // Please change accordingly
  var firstRow = 2;
  var firstCol = 1;
  var numRows = sheet.getLastRow() - 1;
  var numCols = 3;
  var values = sheet.getRange(firstRow, firstCol, numRows, numCols).getValues(); // Get values from source (first 3 columns, starting at row 2)
  // Create an array with the different recipients:
  var recipients = [];
  for (var i = 0; i < values.length; i++) {
    if (recipients.indexOf(values[i][1]) == -1) { // Check that the recipient has not been previously added to the array
      recipients.push(values[i][1]); // Add new recipient to the array
    }    
  }
  // Create and send an email for each recipient in the array:
  for (var i = 0; i < recipients.length; i++) {
    var htmlBeginning = "<div>Body beginning</div>";
    var htmlEnding = "<div>Body ending</div>";
    var table = "<table border=\"1\"><tr><th>Activities</th><th>Due date</th></tr>"; // Table headers
    for (var j = 0; j < values.length; j++) {
      if (recipients[i] == values[j][1]) { // Check that the email address matches.
        // Add row for each activity with this recipient:
        var row = "<tr><td>" + values[j][0] + "</td><td>" + values[j][2] + "</td></tr>";
        table = table.concat(row); // Append new row to the table
      }
    }
    table = table.concat("</table>");
    var emailBody = htmlBeginning.concat(table, htmlEnding);
    // Send email
    MailApp.sendEmail({
      to: recipients[i],
      subject: "Your subject",
      htmlBody: emailBody
    });
  }
}

There are several things to consider regarding this code:

  • You should include whatever parts of the email body which come before and after the table (in variables htmlBeginning and htmlEnding respectively in the code I provided).
  • I consider that data is placed in columns A (activities), B (email addresses) and C (end date) starting at row 2.
  • The sheet where source data is located is called Sheet1 in this example, please change accordingly.

I hope this is of any help.

Upvotes: 1

Related Questions