Created
July 1, 2022 09:44
-
-
Save Ishmam156/c63dafddb6053d3483a6454c0b1a17c2 to your computer and use it in GitHub Desktop.
JavaScript Code to send email from spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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