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
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, has been moved to:
https://thegoogleautomator.substack.com
The Google Automator is where I provide weekly code snippets and productivity tips for Google Apps Scripts and associated products and tools.
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>