How to use Google App Scripts to send HTML emails from Google Sheets with Airtable Data

Whew!!! That is a long title! Also, I don't demonstrate pulling data from Airtable in this video or code example. If you'd like that, check out my previous article on my client's Airtable Reporting Dashboard using Google App Scripts, BigQuery, and Google Data Studio. The code to pull Airtable data is there.

However, this is a Goals vs Actuals Dashboard in use by the same client. In this video and example, I demonstrate how to take the data you've pulled from Airtable into Google Sheets and then into a Dashboard, and from that dashboard, send out HTML formatted emails to the sales people.

I've changed all the data and other elements due to the proprietary nature of my client's business but the concept and code is the same.

Key Take Aways:

  • Do NOT navigate through the sheet. It is inefficient (slow).
  • Grab the sheet data into an array and work with that data.
  • Create re-useable mapping functions to simplify working with that data.

Line by Line Code Walk Thru

Image of Google Sheet Dashboard created by Airtable Data and sent out via HTML mail.

Click to open and then copy the Google Sheet.

In the video, I do a line-by-line walk thru of the code (mostly). I also take you through the HTML email template. You can view that below.

At the bottom of this article, I have the javascript and HTML template code. But you can also get that from the sheet.

The Google Sheet, with the code, can be found here or by clicking the image.

You'll want to copy that into your drive, fill some email addresses in the "STAFF_LIST" sheet, and then run the code. Of course, the first time you run the code, you will need to authorize permissions.

Let me know how you like this tutorial. Was it helpful? What else would you like to see?

Video Demonstration & Code Walk Thru

 

 

Google App Script Javascript

/*
This script and template provided as part of my tutorial on app scripts and HTML templated emails.
Enter your own emails for testing. You will need to approve permissions on first run.
You probably already know this if you are working with app scripts but... 

*/
function sendSalesweeklyupdate() {
  
  
  const goalSheetname = "2021-08 - Goals v Actuals"; 
  /*
      //makeSheetname(2021, 8, "report"); 
      I normally use this a function to return a variety of sheet names based on our app requirement.
  */
  const goalSheet = SpreadsheetApp.getActive().getSheetByName(goalSheetname);
  const sheetValues = goalSheet.getDataRange().getDisplayValues();
  // sheetValues = [
  // [r1c1,r1c2,r1c3,r1c4,r1c5.....], 
  // [r2c1,r2c2,r2c3,r2c4,r2c5.....],
  // [r3c1,r3c2,r3c3,r3c4,r3c5.....],
  // .....
  // ]
  const clientCol = sheetValues.map((col) => col[0]);  //get the first column of data
  const [clientMap, sectionMap] = mapClients(clientCol)
  const staffMap = mapStaff(sheetValues[0])  //pass the first row of sheet values
  const salesEmail = getActiveStaff();
  const onlineStart = sectionMap["onlinestart"]
  const phoneStart = sectionMap["phonestart"]
  const totalStart = sectionMap["gtstart"]
  const clientCt = Object.keys(clientMap).length;


  Object.entries(staffMap).forEach((salesRep) => {
    let [salesName, salesCol] = salesRep;
    let emailAddr = salesEmail[salesName];

    let [onlineData, phoneData, totalData] = getTotalsbysection(sheetValues, salesCol, clientCt, onlineStart, phoneStart, totalStart);

    if (totalData.length != 0) {
      let reportDate = new Date().toLocaleDateString('en-US');
      let reportName = "Sales Goals v Actuals Report: " + salesName;

      let htmlTemplate = HtmlService.createTemplateFromFile("goalTotals");
      htmlTemplate.reportDate = reportDate;
      htmlTemplate.reportName = reportName;
      htmlTemplate.staffORcompany = salesName;
      htmlTemplate.onlineData = onlineData;
      htmlTemplate.phoneData = phoneData;
      htmlTemplate.totalData = totalData;

      let emailMsg = htmlTemplate.evaluate().getContent();
      //let htmlOutput = HtmlService.createHtmlOutput(emailMsg);
       // I have this to during debug to bring up a dialog on the sheet      
      //SpreadsheetApp.getUi().showModalDialog(htmlOutput, "Numbers for: " + salesName);

      Logger.log(salesName + " at " + emailAddr);
      MailApp.sendEmail({
        name: "Reporting System",
        to: emailAddr,
        subject: reportName,
        htmlBody: emailMsg
      });
    }
  }
  )
}


function getTotalsbysection(goalSheetdata, startCol, clientCount, onlineStart, phoneStart, gtstart) {

  //containers for the figures
  let onlineNumbers = [];
  let phoneNumbers = [];
  let gtNumbers = [];

  for (var i = 0; i < clientCount; i++) {
    // 
    if (goalSheetdata[onlineStart - 1 + i][startCol - 1] > 0 || goalSheetdata[onlineStart - 1 + i][startCol] > 0) {
      let strdata = [goalSheetdata[onlineStart - 1 + i][0], ...goalSheetdata[onlineStart - 1 + i].slice(startCol - 1, startCol + 3)];
      onlineNumbers.push(strdata)
    }

    if (goalSheetdata[phoneStart - 1 + i][startCol - 1] > 0 || goalSheetdata[phoneStart - 1 + i][startCol] > 0) {
      let strdata = [goalSheetdata[phoneStart - 1 + i][0], ...goalSheetdata[phoneStart - 1 + i].slice(startCol - 1, startCol + 3)];
      phoneNumbers.push(strdata)
    }


    if (goalSheetdata[gtstart - 1 + i][startCol - 1] > 0 || goalSheetdata[gtstart - 1 + i][startCol] > 0) {
      let strdata = [goalSheetdata[gtstart - 1 + i][0], ...goalSheetdata[gtstart - 1 + i].slice(startCol - 1, startCol + 3)];
      gtNumbers.push(strdata)
    }

  }
  return [onlineNumbers, phoneNumbers, gtNumbers]

}


//========================================================
function mapClients(rowVals) {
  //========================================================
  /*
  returns an object of clients
  returns an object of client row pointers  
  */
  
  const onlineSection = "Online";
  const phoneSection = "Phone";
  const clientTotalSection = "MONTLY TOTAL SALES";

  var curVal = "";
  var clientMap = {};
  var sectionRows = {};

  var c = 0;

  for (var r = 0; r < rowVals.length; r++) {
    curVal = rowVals[r].trim();


    if (curVal == onlineSection + " Sales") {
      r = r + 1; //move one row forward
      curVal = rowVals[r].trim();

      let cl = 0;
      while (!rowVals[r].trim().includes("TOTAL")) {
        curVal = rowVals[r].trim();
        if (curVal != "") {
          cl = cl + 1;
          clientMap[curVal] = cl;
          if (cl == 1) { sectionRows["onlinestart"] = r + 1; }  //first row with a client under media
        }
        r = r + 1;

      }
      if (rowVals[r].trim().includes("TOTAL")) { sectionRows["onlinetotal"] = r + 1 }

    }


    if (curVal == phoneSection + " Sales") {
      r++;  //move forward one row

      while (!rowVals[r].trim().includes("TOTAL")) {
        curVal = rowVals[r].trim();
        if (clientMap[curVal] == 1) {
          sectionRows["phonestart"] = r + 1;  //if the curVal = the first client stored in clientMap, store the influencer start position
        }
        r++;
      }
      curVal = rowVals[r].trim();

      if (curVal.includes("TOTAL")) {
        sectionRows["phonetotal"] = r + 1;
      }
    }


    if (curVal == clientTotalSection) {
      r++;  //move forward one row
      while (!rowVals[r].trim().includes("TOTAL")) {
        curVal = rowVals[r].trim();
        if (clientMap[curVal] == 1) {
          sectionRows["gtstart"] = r + 1;  //if equals first client in clientMap (ordinal position 1), store grand total start position
        }
        r++;
      }
      curVal = rowVals[r].trim();

      if (curVal.includes("TOTAL")) {
        sectionRows["gttotal"] = r + 1;
      }
    }

    if (curVal.includes("Last update:")) {
      sectionRows["lastupdate"] = r + 1;
    }

  }

  return [clientMap, sectionRows];

}
//========================================================




//========================================================
function mapStaff(inSht) {
  //========================================================
  /*
  This function returns a map of the current goal sheet. 
  Map includes:
    their name and their first column position
  */
  // bypass positions 0 and 1 (columns 1 and 2)
  let bMap = {};
  for (let i = 2; i < inSht.length; i++) {
    if (inSht[i] != "") {
      bMap[inSht[i]] = i + 1;
    }
  }

  return bMap;
}
//========================================================



//========================================================
// return staff and clients from the sheets created by Airtable.

function getActiveStaff() {
  // returns object with key: staffname, value: email address
  var sheetName = "STAFF_LIST"
  var staffSht = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var lastRow = staffSht.getLastRow();
  var firstRow = 2;
  var aryStaff = staffSht.getRange(firstRow, 2, (lastRow - firstRow) + 1, 2).getValues();
  var staffList = {};
  aryStaff.forEach(function ([name, email]) {
    staffList[name] = email;
  });

  return staffList;
}

HTML Template

<!DOCTYPE html>
<html>

<head>
    <base target="_top">
    <title><?= reportName ?></title>
</head>

<body>
    <h1><?= reportName ?></h1>
    <p><span style="font-size: 2em;">Numbers for <?= staffORcompany ?></span><br>
  <?= reportDate ?></p>
  <?
    let sectionTitle = ["Online","Phone","Totals"];
    let numberData;
    sectionTitle.forEach((section,index) =>   {
    if (index == 0 ) { numberData = onlineData; }
    if (index == 1 ) { numberData = phoneData; }
    if (index == 2 ) { numberData = totalData; }
    ?>
    <hr><h2><?= section ?> Sales</h2>
    <?
      if (numberData.length == 0) {
    ?>
<h4 style="color: red;">No records for this section</h4>
    <? } else { ?>

    <table class="salesReport" width="70%">
        <thead>
            <tr style="background-color: red;color: white;padding: 5px 0px; font-weight: 700;">
                <th width="40%" style="text-align: left;">CLIENT</th>
                <th width="15%" style="text-align: center;">GOALS</th>
                <th width="15%" style="text-align: center;">ACTUALS</th>
                <th width="15%" style="text-align: center;">+/-</th>
                <th width="15%" style="text-align: center;">% of GOAL</th>
            </tr>
        </thead>
        <tbody>
      <? 
      let totalGoal = 0;
      let totalActual = 0;
      let plusMinus = 0;
      let perTotal = 0;
      numberData.forEach((d,i) => { 
        let color;
        if (i % 2 === 0 ) { color = "silver"} else {color = "light-grey"}
        totalGoal = totalGoal + Number(d[1]);
        totalActual = totalActual + Number(d[2]);
        ?>
            <tr style="background-color: <?= color ?>;">
                <td style="padding:5px 5px 5px 5px;"><?= d[0] ?></td>
                <td style="padding:5px 5px 5px 5px;text-align: center;"><?= d[1] ?></td>
                <td style="padding:5px 5px 5px 5px;text-align: center;"><?= d[2] ?></td>
                <td style="padding:5px 5px 5px 5px;text-align: center;"><?= d[3] ?></td>
                <td style="padding:5px 5px 5px 5px;text-align: center;"><?= d[4] ?></td>
            </tr>
      <? })
        plusMinus = totalActual - totalGoal;
        if (totalGoal != 0) {
          perTotal = ((totalActual/totalGoal) * 100).toFixed(0);
        }
       ?>
        </tbody>
    <tfoot>
            <tr style="background-color: red; color: white; font-weight: 700;">
                <td>TOTALS</td>
                <td style="text-align: center;"><?= totalGoal ?></td>
                <td style="text-align: center;"><?= totalActual ?></td>
                <td style="text-align: center;"><?= plusMinus ?></td>
                <td style="text-align: center;"><?= perTotal ?>%</td>
      </tr>
        </tfoot>
    </table>
    <hr>

  <? }   }) ?>

</body>
</html>

 

Posted in Programming and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *