Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save Ishmam156/c63dafddb6053d3483a6454c0b1a17c2 to your computer and use it in GitHub Desktop.
Save Ishmam156/c63dafddb6053d3483a6454c0b1a17c2 to your computer and use it in GitHub Desktop.
JavaScript Code to send email from spreadsheet
monthObject = {
1: 'Jan',
2: 'Feb',
3: 'Mar',
4: 'Apr',
5: 'May',
6: 'Jun',
7: 'Jul',
8: 'Aug',
9: 'Sep',
10: 'Oct',
11: 'Nov',
12: 'Dec'
}
function roundup(value, decimalPlaces){
const power = 10 ** decimalPlaces
return Math.ceil(value * power) / power
}
function convertToPercent(value) {
convertedNumber = value * 100
return convertedNumber.toFixed(0)
}
function getData() {
var values = SpreadsheetApp.getActive().getSheetByName("Insert Tab Name Here").getRange("Insert Name Range Here").getValues();
console.log(values)
var timestamp = values[0][1]
var date = new Date(timestamp);
values[0][1] = ""+date.getDate()+
"-"+(monthObject[date.getMonth()+1])+
"-"+date.getFullYear()
var monthstamp = values[0][4]
var date = new Date(monthstamp);
values[0][4] = ""+(monthObject[date.getMonth()+1])+
"-"+date.getFullYear()
values[1][1] = "$" + roundup(parseFloat(values[1][1]), 1);
values[1][2] = "$" + roundup(parseFloat(values[1][2]), 1);
values[1][3] = (convertToPercent(roundup(parseFloat(values[1][3]), 2))) + "%";
values[1][4] = "$" + roundup(parseFloat(values[1][4]), 1);
if (values[1][3].includes('-')) {
values[1].push('green')
} else {
values[1].push('red')
}
values[2][1] = roundup(parseFloat(values[2][1]), 1);
values[2][2] = roundup(parseFloat(values[2][2]), 1);
values[2][3] = convertToPercent(roundup(parseFloat(values[2][3]), 2)) + "%";
values[2][4] = roundup(parseFloat(values[2][4]), 1);
if (values[2][3].includes('-')) {
values[2].push('red')
} else {
values[2].push('green')
}
values[3][1] = "$" + roundup(parseFloat(values[3][1]), 1);
values[3][2] = "$" + roundup(parseFloat(values[3][2]), 1);
values[3][3] = convertToPercent(roundup(parseFloat(values[3][3]), 2)) + "%";
values[3][4] = "$" + roundup(parseFloat(values[3][4]), 1);
if (values[3][3].includes('-')) {
values[3].push('red')
} else {
values[3].push('green')
}
return values;
}
function getEmailHtml(data) {
var htmlTemplate = HtmlService.createTemplateFromFile("template.html");
htmlTemplate.data = data
var htmlBody = htmlTemplate.evaluate().getContent();
return htmlBody;
}
function getEmailText() {
var text = "";
return text;
}
function sendEmail() {
var data = getData();
var emailBody = getEmailText();
var htmlBody = getEmailHtml(data);
MailApp.sendEmail("Insert the email address to sent to here", `Insert Subject Name here - ${data[0][1]}`, emailBody, {
cc: 'Add email address here separated by comma',
htmlBody
}
);
console.log('sent')
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment