email spreadsheet rows if a cell contains a certain word - Google Spreadsheet + app script

Question

I'm creating a "deadline reminder system" using a google spreadsheet to manage contracts and projects deadlines, here's the file: https://docs.google.com/spreadsheets/d/1CEk67cXN9NrDy_-ueOxcdwmo2dyyaX47OYIQXi5bHpA/edit#gid=1331412473

I'm trying to use Google app script to create a script that every day check every row of each sheet and send me an email with rows where last column says "in scadenza" (about to expire) or "scaduto" (expired).

For the email template I would like to revve rows as an html table or as a pdf file (what do you think is better?)

I would also find a way to avoid sending duplicates (for example adding a "reminder sent" text in stato (state) column or near this column and colour that cells.

As you can see some tables have a slightly different layout so I think I have to write down a function for each sheet since the colun range that I have to scan is different.

I'm absolutely a beginner with google script, I've found a lot of examples about how to send emails from google sheets but I can't find the correct way to put all together and write down a script that works...

I would really aprecciate if you can take a look to my spreadsheet and help me to write down the script.

Many thanks to all!

Just for reference, here some resources that I've found:

• https://www.groovypost.com/howto/google-sheets-send-email-based-on-cell-value/#:~:text=Step%201%3A%20Sending%20an%20Email,that%20contains%20an%20email%20address.

• How to send several rows of google sheet table via email if cell match today's date ( this example uses date as filter, I have to use the cell text content)

• https://www.labnol.org/code/19869-email-google-spreadsheets-pdf

Answer 1

Solution

You can use a Clock trigger to run a function that filters your Sheets rows in the desired Spreadsheet according to the Stato row value.

Code

First you will need to build the filtering functions:

This function takes some rows as input ad builds a table body HTML with the filtered rows:

function getSheetValuesAsTable(values) {
  var states = ['in scadenza', 'scaduto']; // Here you can add new watched statuses
  return values.filter(row => states.includes(row[row.length -1]))
  .map(row => "<tr>"+row.map(x => "<td>"+x+"</td>").join('')+"</tr>");
}

While the Sheet structure has the Stato column as the last column you will be able to build different tables for each one of your Sheets.

So you will need another function to build the table headers:

function getSheetHeadersAsTable(values) {
  var headers =  values[0];
  Logger.log(values);
  var tabHeaders = headers.map(header => `<th>${header}</th>`);
  return `<table><tr>${tabHeaders.join('')}</tr>`;                                                 
}

Now you can wrap up the table headers and body in a HTML string and send it with the GmailApp.sendEmail() method:

function emailSpreadsheetAsHTMLTable() {
  var ss = SpreadsheetApp.openById('spreadsheet-id');
  
  var body = buildMessage(ss.getSheets());
  var subject = `Scadenze Contratti/Domini/Progetti`;
  var email = 'esposito.luca94@gmail.com';

  if (MailApp.getRemainingDailyQuota() > 0)

    GmailApp.sendEmail(email, subject,body, {
      htmlBody: body
    });
}

function buildMessage(sheets) {
  return sheets.map(sheet => {
         var values = sheet.getDataRange().getValues();
         return `<h2>${sheet.getName()}</h2>${buildSheetTable(values)}</table>`; // Here I add the Sheet name for each table
         }).join('');      
}

function buildSheetTable(values) {
  var tabHeaders = getSheetHeadersAsTable(values);
  var tabBody = getSheetValuesAsTable(values);
  return tabHeaders + tabBody;
}

Now you can install the Clock trigger which will call the emailSpreadsheetAsHTMLTable():

function buildClockTrigger() {
  ScriptApp.newTrigger('emailSpreadsheetAsHTMLTable').timeBased().everyDays(1).create();
}

Reference

Clock Triggers

JS filter

JS map