While it's possible to insert a button (Insert > Drawing) and assign a script to it, the resulting buttons float over the sheet in their own layer, not being bound to any cell. This wouldn't work for you since you want the action to be related to a particular row of the sheet.
Instead, I would use a custom item of the main menu, added by the function onOpen
below, every time the spreadsheet is open (so, after entering the script you'll need to close and open the spreadsheet for it to appear). The menu command "send email" sends email using the data from the active row (the one where the cursor is now). It doesn't matter where in the row the cursor is.
Otherwise the logic is pretty straightforward: get the handle of the active sheet, get the number of the active row, get values in the active row, use them to compose an email.
function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = SpreadsheetApp.getActiveRange().getRow();
var values = sheet.getRange(row, 1, 1, 10).getValues(); // or how many columns you want from that row; 10 seem to be enough for your data
var recipient = values[0][2]; // indices of JavaScript are 0-based.
var subject = values[0][3]; // 0th row of the acquired range, 3rd column
var body = values[0][6] + '\n========\n' + values[0][4];
MailApp.sendEmail(recipient, subject, body);
}
function onOpen() {
var menu = [{name: "Send Email", functionName: "sendEmail"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}
You can add options with
var options = {cc: 'someemail@gmail.com', replyTo: 'another@gmail.com'};
or
var options = {cc: values[0][7], replyTo: values[0][8]};
if you take them from the spreadsheet. Then the sending command will be
MailApp.sendEmail(recipient, subject, body, options);
Assuming you have in A1
(week 1, 39h) and in A2
(week 2, 45h).
Your total overtime (5h) will be in cell A3
with this formula:
=IF(A1>40;A1-40;0)+IF(A2>40;A2-40;0)
Best Answer
You can try
=switch
:Result :
Same result with
regexmatch
: