I want to send a confirmation email to managers based on row data in google sheets. Manager should approve the email by clicking on a link/button in email. This link/button should change the appropriate cell in my Gsheet. (I have a column "manager_approved" that is set to false by default, after clicking the link in email, it should change the corresponding cell in this column to true.)
So far I haven't found a way how to create such a link/button. Managers don't have access to this GSheet.
I will be very grateful full for every advice. I'm new to JS and GS so I'm not even sure if this is even possible.
Unfortunately, creating a button is not possible through Gmail API unless you created your web app using Gmail Add-On service. I can provide a sample code which sends emails based on cell value on Google Sheet.
NOTE: This is a sample code ONLY for Google Sheets/Mail Service.
To further understand this code, you can read through this documentation for Sheets Service.
Sample code:
function sendConfirmationEmail(e) {
var managers = ['Manager 1', 'Manager 2', 'Manager 3', 'Manager 4']; //list of manager names for the dropdown
var emailAdd = ['manager1@gmail.com', 'manager2@gmail.com', 'manager3@gmail.com','manager4@gmail.com'];
var source = e.source;
var sheet = source.getActiveSheet();
var sheetName = sheet.getSheetName();
var range = e.range;
var col = range.getColumn();
var value = e.value;
if (sheetName == 'Sheet1' && col == 3) { //col3 being a dropdown menu list of all manager names
index = managers.indexOf(value);
if (index > -1){
MailApp.sendEmail(emailAdd[index],'Email Confirmation','Confirm this email by clicking here'
)
}
}
}
But, this needs further modification if you would need to add an imageButton using CardService by setting a setOnClickAction(action). Furthermore than that, I suggest reading through the following documentation.
Documentations:
https://developers.google.com/apps-script/reference/card-service/image-button
https://developers.google.com/apps-script/reference/card-service/card-service
https://developers.google.com/apps-script/add-ons/cats-quickstart
You can create a google form with email and accept/reject options, then give the link in an email to managers and ask them to respond via the form. The Google spreadsheet automatically retrieves the answer. Finally, within a formula you can retrieve the answer in any formula.