On edit not running script - Javascript

BenC
April 7, 2023
208 views
1 vote
2 Answers

I want to automatically produce a PDF of my invoices. The worksheet (master) where I generate the invoices has several tabs which I do not want to print. My thoughts were to create a single tab sheet and print from that. To do this I set up a sheet (PDF Print) which uses IMPORTRANGE to bring in the invoice details. I then run a script from the master sheet which puts the current invoice number into A64 of the PDF print sheet.

Using the script below (running in the PDF print sheet) I was hoping to start the PDF print process. The script works if run manually but not by the OnEdit trigger). I wondered if the problem was that OnEdit only works when the cell contents has been changed manually.

My script is:

function onEdit(e) {

  if (e.range.getA1Notation() == 'A64') {
    savePDFs();
  }
}

function savePDFs(optSSId, optSheetId) {
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/, '');
  var parents = DriveApp.getFileById(ss.getId()).getParents();

  var folders = DriveApp.getFoldersByName('Invoices PDF'); // Modified
  var folder = folders.hasNext() ? folders.next() : parents.next(); // Modified

  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue;
    var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
      +
      '&gid=' + sheet.getSheetId() //the sheet's Id
      // following parameters are optional...
      +
      '&size=A4' // paper size
      +
      '&portrait=true' // orientation, false for landscape
      +
      '&fitp=true' // fit to width, false for actual size
      +
      '&sheetnames=false&printtitle=false&pagenumbers=false' //hide optional headers and footers
      +
      '&gridlines=false' // hide gridlines
      +
      '&fzr=false'; // do not repeat row headers (frozen rows) on each page
    var options = {
      headers: {
        'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
      }
    }
    var response = UrlFetchApp.fetch(url + url_ext, options);
    var valor = sheet.getRange('c1').getValue(); // Modified
    var blob = response.getBlob().setName(valor + '.pdf');
    folder.createFile(blob);
    ClearPrintTrig()
  }
}

function ClearPrintTrig() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A64').activate();
  spreadsheet.getActiveRangeList().clear({
    contentsOnly: true,
    skipFilteredRows: true
  });
}

Answers

Chosen as BEST ANSWER
- BenC
- April 7, 2023 at 6:12 pm
- 0 votes
0
Many thanks to all who have looked and and commented on this. I need to run this automatically so that it does not get forgotten by any users so unfortunately any input to the PDF sheet would not be suitable. The timer option again would not work as we would end up with many pdf prints that are not required and it would be easy to lose the ones we did want amongst them. I think I'll just have to accept it can't be be done. Thanks again for taking the time to try and help.

(Edit)

- GiselleValladares
- April 7, 2023 at 5:34 pm
- 0 votes
0
I’m writing this answer as a community wiki since the solution was provided by @Kos, @Onit and @Benoit Drogou in the comments section.

There are 3 possible workarounds:

1.Time triggers.

A time trigger, so a request is made every certain amount of time, it can be every few minutes, half an hour, a day, etc.

You can see an example of a simple time trigger here:
```
function createTimeDrivenTriggers() {
  // Trigger every 6 hours.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyHours(6)
      .create();
```
To manually create an installable trigger in the script editor, follow these steps:
1. Open your Apps Script project.
2. At the left, click Triggers .
3. At the bottom right, click Add Trigger.
4. Select and configure the type of trigger you want to create.
5. Click Save
2. Assign app script to run from the Spreadsheet.

Add an image to the Google Sheet, and assign the function to the image by following the steps:
1. Add an image to the Google Sheet.
2. Click the image, and select the 3 dots .
3. Select Assign script.
1. Add the name of the function.
3. Add a custom menu.

You can add a custom menu to Google Sheets that allows you to run the script manually from that bar by adding this code to your script.
```
function onOpen() {
  var ui = SpreadsheetApp.getUi();
 // the name 'menuItem1' needs to be replaced with the name of the function. 
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator()
     .addToUi();
```
When the users click on the menu, the script will execute.
Login or Signup to reply.