- Create a new Google Sheets file
- Go to Extensions → Apps Script
- Remove all the code and copy-paste the following instead
- Save your project and go back to your sheet
- Use sendUser(), onOpen(), like classic Google Sheets Formula
The 2 new functions in Google Sheets:
The code to paste in Apps Script inside Google Sheets:
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('send email').addItem('Approve', 'approver').addToUi()
}
function approver() {
const ui = SpreadsheetApp.getUi();
const row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
Logger.log(row);
const data = SpreadsheetApp.getActiveSheet().getRange(row, 1, 1, 3).getValues()[0];
const user = {
first: data[0]
, last: data[1]
, email: data[2]
, row: row
};
Logger.log(user);
const res = ui.alert('Send to ' + user.first + '(' + user.email + ')?', ui.ButtonSet.YES_NO);
if (res == ui.Button.YES) {
sendUser(user);
}
Logger.log(res);
}
function sendUser(user) {
//let message = 'Hello World
';
const temp = HtmlService.createTemplateFromFile('temp');
temp.user = user;
const message = temp.evaluate().getContent();
MailApp.sendEmail({
to: user.email
, subject: 'Tester'
, htmlBody: message
});
SpreadsheetApp.getActiveSheet().getRange(user.row, 4).setValue('sent');
}
