I'm trying to call a function by checking a checkbox. The goal is to be able to use the script from a mobile or a tablet. When I execute the code from the console, it is working fine. But when I try to execute it by checking the box, nothing happens. Can someone help me to figure out why the script is not called when I check the checkbox? Thanks
function onEdit() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var range = sheet.getActiveRange()
if (range.isChecked()) {
if (range.getA1Notation() == "A1") {
sendSaveDringDringYesNo();
} else {
range.uncheck()
}
}
}
function createFolder(folderID, folderName){
var parentFolder = DriveApp.getFolderById(folderID);
var subFolders = parentFolder.getFolders();
var doesntExists = true;
var newFolder = '';
// Check if folder already exists.
while(subFolders.hasNext()){
var folder = subFolders.next();
//If the name exists return the id of the folder
if(folder.getName() === folderName){
doesntExists = false;
newFolder = folder;
return newFolder.getId();
};
};
//If the name doesn't exists, then create a new folder
if(doesntExists = true){
//If the file doesn't exists
newFolder = parentFolder.createFolder(folderName);
return newFolder.getId();
};
};
function start(){
//Add your own folder ID here:
var FOLDER_ID = sheet.getRange("idFolder").getValue();
//Add the name of your folder here:
var NEW_FOLDER_NAME = sheet.getRange("fnewfolderName").getValue();
var myFolderID = createFolder(FOLDER_ID, NEW_FOLDER_NAME);
Logger.log(myFolderID);
};
//=============== Save the invoice file in the folder after sending the Contrat de dépôt-vente and created the folder
function checkSheetInvoice() {
var folderName= contract_sheet.getRange("fnewfolderName").getValue()
var folder=DriveApp.getFoldersByName(folderName)
var folderID = folder.next().getId(); // Folder id to save in a folder.
var pdfName = contract_sheet.getRange("pdfname").getValue();
var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var folder = DriveApp.getFolderById(folderID);
//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(pdfName, folder))
var destSheet = destSpreadsheet.getSheets()[1];
//repace cell values with text (to avoid broken references)
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(sourcevalues);
//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i ) {
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}
}
//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);
//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}
//============================ using this to save the general file in the client's folder
function copyDocument() { ///copy of the Contrat de dépôt-vente
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get current active spreadsheet.
//var sstocopy = DriveApp.getFileById(ss.getId()); // Get spreadsheet with DriveApp.
var contract_sheet = ss.getActiveSheet(); // Get current active sheet.
var sheet_name = sheet.getRange("pdfname").getValue(); // Get the value of cell h10, used to name the new spreadsheet.
var folderName= sheet.getRange("fnewfolderName").getValue()
var folder=DriveApp.getFoldersByName(folderName)
var folderID = folder.next().getId(); // Folder id to save in a folder.
var folder = DriveApp.getFolderById(folderID); // Get the ID of the folder where you will place a copy of the spreadsheet.
//sstocopy.makeCopy(sheet_name,folder); // Make a copy of the spreadsheet in the destination folder.
// create the Contrat de dépôt-vente in the folder
var newFile = folder.createFile(response);
}
//==============================Send the pdf to the client
// Define your variables here
var recipient= SpreadsheetApp.getActiveSpreadsheet().getRange("emailenvoi").getValue();
var subject=SpreadsheetApp.getActiveSpreadsheet().getRange("pdfname").getValue();
var body="Bonjour,\n\nVeuillez trouver ci-joint votre fiche de commande DringDring.\n\nMerci,\nTeam XXXX";
var nameOfSender="Partenaire DringDring";
var d = new Date();
var curr_date = d.getDate();
var curr_month = d.getMonth() 1; //Months are zero based
var curr_year = d.getFullYear();
var theDate = curr_date "-" curr_month "-" curr_year;
// End of the stuff you need to edit
// Below, the sheet is converted to pdf in a blob object and that object
// is sent by email with the email-parameters above.
// Other stuff
var ss = SpreadsheetApp.getActiveSpreadsheet();
//var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
//var sheetId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
// Base URL
var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());
/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/
var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
'&size=A4' // paper size legal / letter / A4
'&portrait=true' // orientation, false for landscape
'&fitw=true&source=labnol' // fit to page width, false for actual size
'&sheetnames=false&printtitle=false' // hide optional headers and footers
'&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
'&fzr=false' // do not repeat row headers (frozen rows) on each page
'&gid='; // the sheet's Id
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url url_ext sheet.getSheetId(), {
headers : {
'Authorization' : 'Bearer ' token
}
}).getBlob().setName(subject ".pdf");
sheet_as_pdf_blob_document=response;
////////////////////////////////////////////////////////////////////////////////////////////Here we send the email
//for the Contract
function sendContract() {
var message = {
to: recipient,
subject: "Fiche de Commande DringDring - XXXX",
body: "Bonjour,\n\nVeuillez trouver ci-joint la fiche de commande DringDring.\n\nMeilleures salutations, \nXXXX Sion",
name: "Nom du Partenaire DringDring",
attachments: [sheet_as_pdf_blob_document]
}
MailApp.sendEmail(message);
}
///save and save the DringDring pdf order
function sendSaveDringDringYesNo(){
// Display a dialog box with a message and "Yes" and "No" buttons. The user
//can also close the
// dialog by clicking the close button in its title bar.
var ui = SpreadsheetApp.getUi();
var response = ui.alert(
'Please Confirm',
'Sauvegarder cette fiche de commande and envoyer le pdf',
ui.ButtonSet.YES_NO);
// Process the user's response.
if (response == ui.Button.YES) {
start() //create the folder
copyDocument() //copy the google file in the client's folder
sendContract()
}
if (response == ui.Button.NO) {
}
else {
}
}
CodePudding user response:
There are other restrictions so you may want to check them. Now, with the option to use an installable trigger instead, it's recommended as these triggers are more flexible than the simple triggers, as they can call services that require authorization.
CodePudding user response:
Ok, I find my solution! Here is the code. You need to install a onedit trigger for the onEditTrigger() function. Thanks again!
var cellEdited = e.range.getValue();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
if (cellEdited == "Envoyer") {
start() //create the folder
copyDocument() //copy the google file in the client's folder
sendContract()
copytransactionTodispatchClientExistant()
ss.getRange('A2').setValue(new Date());
ss.getRange('A1').setValue("Cliquer ici pour envoyer");
}
else {
}
}```