I am currently exploring ways to enhance collaboration in project management, especially when dealing with numerous stakeholders (Although there are free tools available, integrating new procedures into a public organization's projects can be quite challenging).
Check out the sample file I created here: Google Sheet for Test
The goal is to enable authors to select the menu item "Question" > "Send Question" whenever they input a new question. This action should trigger an automatic email sent to the specified Recipient email address (located in column C), provided that the Question cell is filled (condition checked in "I2").
However, the current script is not functioning as intended.
Please note: I am in the process of learning JavaScript.
Here is the code :
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Question')
.addItem('Send Question', 'sendQuestion')
.addSeparator()
.addItem('Send Answer', 'sendAnswer')
.addToUi();
}
function sendQuestion() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Questions");
var range = sheet.getRange("A22:L2");
var emailvalid = range.getCell(1,7).getValue();
var question = range.getCell(1,5).getValue();
var answer = range.getCell(1,6).getValue();
var qvalid = range.getCell(1,8).getValue();
var ansvalid = range.getCell(1,10).getValue();
if(qvalid == "Nope"){
Browser.msgBox("Please, write a question !");
}
else {
return false};
if(emailvalid == "Nope"){
Browser.msgBox("Please, input a valid email address !");
}
else {
return false};
var from = range.getCell(1,1).getValue();
var recipient = range.getCell(1,2).getValue();
var emailTo = range.getCell(1,3).getValue();
var subject = "Project Management | Your received a new question" + question;
var link = "https://docs.google.com/spreadsheets/d/1QUbw0WNju55h5pk3l8QqVCYa_jSCzrIzMjf0N4v-z8c/edit?usp=sharing"
var options = {}
options.htmlBody = "Hi" + recipient +"," + "<br />"+ "<br />" + "You received a new question from " + from + "<br />" + "<br />" + "You can answer directly into the table here: "<"br />"+"<br />" + link + "<br />" + "<br />"+ "Thank you";
GmailApp.sendEmail(emailTo, subject, " ", options);
}
function emailSent(){
var ui = SpreadsheetApp.getUi();
var sent = sendQuestion("Send Email ?",ui.ButtonSet.YES_NO);
if(sent == ui.Button.YES) {
ui.alert("Your Message has been sent.");
} else {
ui.alert("Email canceled");
}
}