This is my first venture into the world of Google Scripts, and I must say, it's quite exciting!
After some searching and tweaking, I managed to get a Sheets function up and running. It's designed to send an email notification about a status update on a specific row, triggered by a menu selection. However, I'd like to take it a step further by having it also append text to a column labeled "Email Sent" (which happens to be column O).
Below is the current script:
function getColIndexByName(colName) {
var sheet = SpreadsheetApp.getActiveSheet();
var numColumns = sheet.getLastColumn();
var row = sheet.getRange(1, 1, 1, numColumns).getValues();
for (i in row[0]) {
var name = row[0][i];
if (name == colName) {
return parseInt(i) + 1;
}
}
return -1;
}
function emailStatusUpdates() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = sheet.getActiveRange().getRowIndex();
var userEmail = sheet.getRange(row, getColIndexByName("email")).getValue();
var subject = "Helpdesk Ticket #" + row;
var body = "We've updated the status of your ticket.\n\nStatus: " + sheet.getRange(row, getColIndexByName("Status")).getValue();
body += "\n\nNotes: " + sheet.getRange(row, getColIndexByName("Notes")).getValue();
body += "\n\nResolution: " + sheet.getRange(row, getColIndexByName("Resolution")).getValue();
MailApp.sendEmail(userEmail, subject, body, {name:"Help Desk"});
}
function onOpen() {
var subMenus = [{name:"Send Status Email", functionName: "emailStatusUpdates"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("Help Desk Menu", subMenus);
}