Skip to content

Instantly share code, notes, and snippets.

@fischerbach

fischerbach/Code.gs Secret

Created Mar 19, 2021
Embed
What would you like to do?
// Enter sheet name where emails are stored
var EMAILS_SHEET_NAME = "emails";
var REPORT_SHEET_NAME = "report";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// This function glues spreadsheet and Apps Script project
// Run this function before everything else.
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
function sendEmails() {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key")); //// Alternatively, you can hard code spreadsheet's id here
var sheet = doc.getSheetByName(EMAILS_SHEET_NAME);
var columns = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
if(columns.indexOf("Email") == -1 ){
throw new Error("Email column not found.");
}
if(columns.indexOf("Last message") == -1 ){
throw new Error("'Last message' column not found.");
}
//Loading data
var data = {};
var _data = sheet.getRange(2,1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
for (i in columns) {
data[columns[i]] = _data.map(element => element[i]);
}
for(var i = 0; i < data['Email'].length; i++) {
var today = Utilities.formatDate(new Date(), "UTC", "yyyy-MM-dd");
if (parseInt(Utilities.formatDate(data['Last message'][i], "UTC", "yyyyMMdd")) < parseInt(today.replace(/([-])+/g, ""))) {
// sheet.getRange(i+2, columns.indexOf("Last message")+1).setValue(new Date());
var report = generateReport(data['City'][i], today);
MailApp.sendEmail({
to: data['Email'][i],
subject: "Sales in " + data['City'][i] + " - " + today,
htmlBody: report.body,
inlineImages: report.images
});
SpreadsheetApp.flush();
}
}
}
function generateReport(city='London', day='1990-01-20') {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(REPORT_SHEET_NAME);
var incomes = {
'Berlin': Math.round(sheet.getRange('D2').getValue()),
'London': Math.round(sheet.getRange('J2').getValue()),
'Warsaw': Math.round(sheet.getRange('P2').getValue())
}
var charts = {
'Berlin': sheet.getCharts()[0],
'London': sheet.getCharts()[1],
'Warsaw': sheet.getCharts()[2]
}
var total_revenue = incomes[city];
var chart = charts[city];
var emailImages = {
"chart": chart.getAs("image/png")
};
var reportBody = "Sales in " + city + " - " + day + "<br />";
reportBody += "The daily revenue was " + total_revenue;
reportBody += "<p align='center'><img src='cid:chart'></p>";
return {
"body": reportBody,
"images": emailImages
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment