2

I have got following partly working Google script code

function onEdit(e) {
  if (e.range.getA1Notation() == 'B8') {
    if (/^\w+$/.test(e.value)) {        
      eval(e.value)();
      e.range.clear({ formatOnly: false, contentsOnly: true });
    }
  }
}

When changing content of cell B8 to the name of the function it is supposed to run the function.

It works well if I trigger this function:

function ClearAll() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var sheet2 = SpreadsheetApp.getActive().getSheetByName('Sheet2');  
  sheet.getRange('H3:I').clearContent();
  sheet2.getRange('B5').clearContent();
}

But unfortunately it doesn't trigger following function:

function SheetToPDF() {
  SpreadsheetApp.flush();

  //DriveApp

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet3');

  var url = ss.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below parameters are optional...
  '&size=A4' + //paper size
  '&portrait=true' + //orientation, false for landscape
  '&fitw=true' + //fit to width, false for actual size
  '&sheetnames=false&printtitle=false&pagenumbers=true' + //hide optional headers and footers, show page numbers
  '&gridlines=false' + //hide gridlines
  '&fzr=true' + //do not repeat row headers (frozen rows) on each page
  '&gid=' + sheet.getSheetId(); //the sheet's Id

  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url + url_ext, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

  var setupsheet = SpreadsheetApp.getActive().getSheetByName('Sheet2');     //gets a list where to look for a filename
  var filename = setupsheet.getRange("B2").getValues();       //gets name of the file from cell B2

  var blob = response.getBlob().setName(filename + '.pdf');

  //from here you should be able to use and manipulate the blob to send and email or create a file per usual.
  //In this example, I save the pdf to drive

  var specified_folder = DriveApp.getFolderById("FOLDERID");    //specify folder ID
  specified_folder.createFile(blob);     //saves the PDF to Drive
} 

I use custom menu to trigger the function but I need to trigger the function from mobile Android app as well. So far from what I know there is only one working solution how to trigger functions from mobile app and that's the onEdit() function.

So the question is what I need to do to make the onEdit trigger SheetToPDF function?

1

The simple onEdit trigger cannot do things like DriveApp.getFolderById because it runs without authorization.

You can create an installable trigger that will also run on every edit, but with the same privileges as the user creating it (i.e., you).

  • Thanks! Anyway I don´t see a way how to trigger the function on edit of specific cell to a specific value. I have added function test() { var sheet = SpreadsheetApp.getActive(); ScriptApp.newTrigger("SheetToPDF") .forSpreadsheet(sheet) .onEdit() .create(); } and it results in printing PDF every single time when any edit is done. – Roman Mar 10 '17 at 14:39
  • 1
    The function will be triggered on every edit; it is then up to you to examine the active range to decide if it meets your criteria. Use the event object – user135384 Mar 10 '17 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.