I appreciate the time you are taking to read/answer my case, basically, I have a function to export a certain range as a PDF report, this report is the result of a daily inspection I'm assigned at work, the function below is a function I adapted from some code I found, it exports the range "F1:O" of the sheet named "Report Generator (Automatic)", saves it to a folder on Drive and then mails the file and a message to the emails I specify (I will make it to get the emails from a certain range later):
function ExportAsPDF(range,shTabName) {
var blob,exportUrl,name,options,response,sheetTabId,ss,ssID,url_base,range;
range = range? range: "F1:O";//Set the default to whatever you want
shTabName = "Report Generator (Automatic)";//Replace the name with the sheet tab name for your situation
ss = SpreadsheetApp.getActiveSpreadsheet();//This assumes that the Apps Script project is bound to a G-Sheet
ssID = ss.getId();
sh = ss.getSheetByName(shTabName);
sheetTabId = sh.getSheetId();
url_base = ss.getUrl().replace(/edit$/,'');
name = sh.getRange("E1").getValue();
name = name + "- Supporting Report Evidence"
//Logger.log('url_base: ' + url_base)
exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +
'&gid=' + sheetTabId + '&id=' + ssID +
'&range=' + range +
//'&range=NamedRange +
'&size=A4' + // paper size//A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
'&portrait=false' + // orientation, false for landscape
'&fitw=true' + // fit to width, false for actual size
'&sheetnames=false&printtitle=True&pagenumbers=CENTER' + //hide optional headers and footers
'&gridlines=false' + // hide gridlines
'&fzr=false' + // do not repeat row headers (frozen rows) on each page
'&top_margin=0.15' +
'&bottom_margin=0.15' +
'&left_margin=0.15' +
'&right_margin=0.15' +
'&horizontal_alignment=CENTER' + //LEFT/CENTER/RIGHT
'&vertical_alignment=MIDDLE'+ //TOP/MIDDLE/BOTTOM'
'&fzr=False'; // do not repeat row headers (frozen rows) on each page
//Logger.log('exportUrl: ' + exportUrl)
options = {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
}
}
options.muteHttpExceptions = true;//Make sure this is always set
response = UrlFetchApp.fetch(exportUrl, options);
//Logger.log(response.getResponseCode())
if (response.getResponseCode() !== 200) {
console.log("Error exporting Sheet to PDF! Response Code: " + response.getResponseCode());
return;
}
blob = response.getBlob();
blob.setName(name + '.pdf')
var specified_folder = DriveApp.getFolderById("FolderID"); //specify folder ID
var savedPDFfile= specified_folder.createFile(blob); //saves the PDF to Drive
var recipient='recipient@mail.com';
var subject=SpreadsheetApp.getActiveSpreadsheet().getRangeByName("'Report Generator (Automatic)'!E1").getValue().toString();
var body="Hello,\n\nPlease find attached the test document.\n\nThank you,\n My name";
var myemail = Session.getEffectiveUser().getEmail();
MailApp.sendEmail(recipient,subject,body,{
name: myemail,
cc: 'CCmail@mail.com',
attachments: [savedPDFfile.getAs(MimeType.PDF)]})
};
I created a trigger with this "Createtriggers" function to enable "ExportAsPDF" to run on edit as well, doesn't seem to do anything if I delete/insert it, I saw a thread about installable triggers but it was not very specific and last time I tried to deploy my file as an API executable it would just not allow me to run it anymore and there's a "quota" of the number of apps I can deploy (I think it was 12), anyway, this is the trigger I put on the same script:
function createTriggers() {
ScriptApp.newTrigger('ExportAsPDF')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
The following functions were tested individually on the script (it's just one single script for a single file on Sheets), this is why they are all named "Onedit(e)", the output of all of them was an error of undefined range or "can't read undefined" error, so I simply deactivated each function with "//" because they don't really work for me:
//function onEdit(e){
//var dropDownCell = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("'Report Generator (Automatic)'!B1").getValue().toString()
//if (dropDownCell == 'ExportAsPDF'){
//return ExportAsPDF("F1:O","Report Generator (Automatic)")}
//if (dropDownCell == 'ExportAsPDF'){
// dropDownCell.range().clear()}
//}; //*******function does not work
//function onEdit(e){
//const rg = e.range;
//if(rg.getA1Notation() === "B1" && rg.isChecked() && rg.getSheet().getName() === "Sheet1"){
//ExportAsPDF("F1:O","Report Generator (Automatic)");
//rg.uncheck();
//}
//} //*******function does not work (changed the "Sheet1" parameter but doesn't do anything)
//function onEdit(e) {
//if(e.range.getSheet().getName()=='Report Generator (Automatic)') {
//if(e.range.getA1Notation()=='B1' && e.value=="TRUE") {
//return ExportAsPDF("F1:O","Report Generator (Automatic)")}
//};
//} //*******function does not work
However, what I am required to do is taking pictures of some issues and then inform the corresponding department of them, which is done through the email and report I have generated, and it would be extremely practical and convenient for me to do it through my phone, this is why I have tried to adapt my "ExportAsPDF" function in such a way that it can be triggered on the Google Sheets mobile app by using a checkbox when it's True it should run the "ExportAsPDF" function and then reset as "False", this "OnEdit" function "combines" two separate functions, one is to trigger "ExportAsPDF" with the checkbox located in cell B1, and the second one is a multi-select dropdown function for a column named "Root cause" on other sheets from the spreadsheet:
function onEdit(e) {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('Report Generator (Automatic)');
var activeCell = sh.getActiveCell();
var range = range ? range : "F1:O";
var shTabName = "Report Generator (Automatic)";
if(activeCell.getColumn() == 2 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=='Report Generator (Automatic)') {
if(!e.value) {
activeCell.setValue('FALSE');
}
if(e.value='TRUE') {
activeCell.setValue('FALSE'); //the function works until here
eval(ExportAsPDF)(range,shTabName); //then it "jumps" this step
}
}
//START OF MULTISELECTION FOR ROOT CAUSES FUNCTION
var oldValue;
var newValue; //this function runs smoothly all over the other sheets (on cells with dropdown selection)
if(activeCell.getColumn() == 14 && activeCell.getRow() >= 8) {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue("");
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+','+'\n'+newValue); //Multiple selection list for root cause gets new value
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}
My question is basically how can I get my "ExportAsPDF" to be triggered from a checkbox located in cell B1 from my "Onedit" function so I am able to export my reports using my phone (I have an android)? I have not deployed this as an API executable (not sure how to do so neither if I did it properly the last time, I assume I didn't), I have already set my "Onedit" and "ExportAsPDF" functions triggers as "onedit" on the trigger editor as well.
