I am trying to run an amazing script shared by Lauren Script for Google sheets (auto email function) and have an issue. I'm fairly new on stackoverflow so let me know if i need to ask this in another way if I'm breaking any norms? I have setup and successfully edited this script to do what i need it to do. I have a team that will be using this on mobile devices. I tried it and it did not work! can you help or point me in the right direction?
This is for a task list, when someone then selects a checkbox it sends any added notes and details to team leaders (and as per the script then unchecks the box). The checkbox is great as there is one per row. But open to any suggestions
Sorry for the lack of clarity, and thanks @TheMaster for helping out here. Code is below, but sounds like i am trying to do the impossible? Thought i would be able to do this with google sheets but it seems it is not possible on sheets android ap. I am not very advanced and might need to abandon the project! Any suggestions maybe even to do this in a different method?
function EmailNotification(e) {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Deliveries'); //source sheet
var columnb = sheet.getRange('J:J'); //Column with check boxes
var columnbvalue = columnb.getValues();
var notifysheet = ss.getSheetByName('Responses'); //destination sheet
var data = [];
var rownum =[];
//Condition check in B:B (or check box column); If true copy the same row to data array
for (i=0; i<columnbvalue.length;i++) {
if (columnbvalue[i] == 'true') {
var columnb2 = sheet.getRange('J2:J');
columnb2.setValue('false');
// What you want to say in the pop up alert
var response = ui.alert('Hold Up!
Are you sure you want to send an email to team leaders for this delivery?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
data.push.apply(data,sheet.getRange(i+1,1,1,20).getValues());
//Copy matched ROW numbers to rownum
rownum.push(i);
//Copy data array to destination sheet
notifysheet.getRange(notifysheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
var activeRow = notifysheet.getLastRow();
var suppliername = notifysheet.getRange(activeRow, 1).getDisplayValue(); // The number is the column number in the destination "responses" sheet that you want to include in the email
var owner = notifysheet.getRange(activeRow, 3).getDisplayValue();
var recievedby = notifysheet.getRange(activeRow, 8).getDisplayValue();
var instructions = notifysheet.getRange(activeRow, 5).getDisplayValue();
var status = notifysheet.getRange(activeRow, 7).getDisplayValue();
var notes = notifysheet.getRange(activeRow, 9).getDisplayValue();
var email = notifysheet.getRange(activeRow, 4).getDisplayValue();
var subject = 'Delivery completed!'
//Body of the email message, using HTML and the variables from above
var message =
'<br><br><div style="margin-left:40px;">Hello '+ owner +'</div>'
+'<br><br><div style="margin-left:40px;">A delivery you requested has been fulfilled</div>'
+'<br><br><div style="margin-left:40px;"><h3 style="text-decoration: underline;">Recieved Name: '+ recievedby +'</h3></div>'
+'<div style="margin-left:40px;">Supplier: '+ suppliername +'</div>'
+'<div style="margin-left:40px;">Special instructions you added: '+ instructions +'</div>'
+'<div style="margin-left:40px;">Status: '+ status +'</div>'
+'<div style="margin-left:40px;">Notes: '+ notes +'</div>'
+ '<br><br><div style="margin-left:40px;">ヽ(?■_■)ノI Did It ??</div>';
MailApp.sendEmail(
email,
subject,
"",
{
htmlBody: message,
name: 'Shop Alerts', //The name you want to email coming from
});
}
}
}
}
See Question&Answers more detail:os