Log multiple values and use the log to send a dail

问题:

I've been able to write the following code which based on an installable trigger (daily) checks for the word "backorder" on column "M" in the first 100 rows. If it finds a row with the matching criteria it will grab the info from 3 other columns (customer name, project name, project $) for the particular row and then email that information out. So far that appears to work well.

What Im trying to accomplish is the following;

1- If column M reads "backorder" then copy rows for columns I, J and K and send all data at once via email daily(Thanks to @Gerneio this works now)

2- Same as Goal 1 but only for new items (not previously emailed items)

3- Send a report at the end of the week with ALL items logged during the week

Is this possible?

Published sheet link

Sample Data (Picture)

   function backorder2()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var col = 13;

// start table and add header cells and row
var html = "<table><tr><th>Customer</th><th>Project</th><th>$ Amt</th></tr>";

for (var row2 = 1;row2 <=100; row2++)
{
var rownumber = sheet.getRange(row2,col);
var customer = sheet.getRange(row2,col-4).getValue();
var project = sheet.getRange(row2,col-3).getValue();
var $ = sheet.getRange(row2,col-2).getValue();
var timestamp = new Date();
timestamp = Utilities.formatDate(timestamp, "GMT-05:00", "MM/dd/yyyy, hh:mm:ss");

if (rownumber.getValue() == "backorder")
{
sheet.getRange(row2,col).setNote(timestamp);

html += "<tr>";
html += "<td>" + customer + "</td>";
html += "<td>" + project + "</td>";
html += "<td>$" + $ + "</td>";
html += "</tr>";
}
else
{
sheet.getRange(row2,col).clearNote();
}
}

html += "</table>";

MailApp.sendEmail('[e-mail]', 'Backorders on project', 'Following projects have items on backorder:', {htmlBody : html});
}

---

回答1:

How frequently are you wanting the email sent out? Same time frame as that backorder() trigger already runs, so daily? Or another time frame? If it is the same time frame, and assuming that the only criteria that qualifies for grouping is if it list "backorder", then the simplest way is to concatenate all your rows into a formatted html table.

Updated Code:

function backorder()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var col = 13;

var dayOfWeekForWeekly = 'Sunday';

var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];

var timestamp = new Date();
//timestamp = Utilities.formatDate(timestamp, "GMT-05:00", "MM/dd/yyyy, hh:mm:ss");

  // in case weekly report is needed
  var maxDateBound = new Date(timestamp.getFullYear(), timestamp.getMonth(), timestamp.getDate() + 1);
  var minDateBound = new Date(maxDateBound.getFullYear(), maxDateBound.getMonth(), maxDateBound.getDate() - 7);

// start table and add header cells and row
var html = "<table><tr><th>Customer</th><th>Project</th><th>$ Amt</th></tr>";

var validRowExist = false;

for (var row2 = 1;row2 <=100; row2++)
{
var rownumber = sheet.getRange(row2,col);
var customer = sheet.getRange(row2,col-4).getValue();
var project = sheet.getRange(row2,col-3).getValue();
var $ = sheet.getRange(row2,col-2).getValue();

var existingNote = rownumber.getNote();
var existingTimestamp = null;
if (existingNote != "")
  existingTimestamp = new Date(existingNote);    

var isValidRowForDaily = (rownumber.getValue() == "backorder" && existingNote == "");
var isValidRowForWeekly = (rownumber.getValue() == "backorder" && days[timestamp.getDay()] == dayOfWeekForWeekly && (existingTimestamp >= minDateBound && existingTimestamp < maxDateBound));

if (isValidRowForDaily || isValidRowForWeekly)
{
rownumber.setNote(timestamp);

html += "<tr>";
html += "<td>" + customer + "</td>";
html += "<td>" + project + "</td>";
html += "<td>$" + $ + "</td>";
html += "</tr>";
validRowExist = true;
}
else if (rownumber.getValue() != "backorder")
{
rownumber.clearNote();
}
}

html += "</table>";

var subject = "";

if (validRowExist)
{
if (days[timestamp.getDay()] == dayOfWeekForWeekly)
{
  subject = "Weekly Backorders on Projects: ";
  subject += minDateBound.toDateString() + " - ";
  subject += timestamp.toDateString();
}
else
{
  subject = "Daily Backorders on Project: ";
  subject += timestamp.toDateString();
}

MailApp.sendEmail('[e-mail]', subject, 'Following projects have these items on backorder:', {htmlBody : html});
}
}
else
{
   // write code to send follow up email since table is empty
 }

Update - See above for code referring to daily and weekly emails of new entries only. There are tons of ways to approach this, and perhaps some are more suitable than what I got for you here. But you can give the above a try. Essentially what it does is rely on the note that you set with a date each time you send an email with applicable rows. At the end of the week (in this case I used Sunday, which you can change near the top) a report will be generated for the entire week including all the new entries of that day. I hope this is what you were looking for.