Google Apps Script定时/单元格触发多工作表转PDF故障排查

Hey there! Let's break down the issues with your scripts and get them working properly for both triggers.

1. Fixing the Timed Trigger Error ("Bad value" on line 4) #

Your savePDFs script has two key syntax/logic errors that are causing the timed trigger to fail:

• Broken date formatting: The line where you create the date variable has typos in the function name and parameter structure. You wrote Utilities.formatDate8new Date(). "GMT+2" ""hh:mm dd/MM/yyyy") which is invalid.
• Undefined sheet variable: When using optSheetId, you tried to call sheet.getSheetId() but never defined what sheet is. Since optSheetId is already the sheet's ID (gid), we can use it directly.

Here's the corrected savePDFs function:

function savePDFs(optSSId, optSheetId) {
  // Open the target spreadsheet (use provided ID or active sheet)
  var ss = optSSId ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the parent folder of the spreadsheet
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();
  
  // Build the PDF export URL with correct parameters
  var url_ext = 'export?exportFormat=pdf&format=pdf' +
    // Export specific sheet if ID is provided, else export entire spreadsheet
    (optSheetId ? ('&gid=' + optSheetId) : ('&id=' + ss.getId())) +
    '&size=letter' +
    '&portrait=true' +
    '&fitw=true' +
    '&sheetnames=false&printtitle=false&pagenumbers=false' +
    '&gridlines=false' +
    '&fzr=false';
  
  var options = {
    headers: {
      'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
    }
  };
  
  // Fixed date formatting (corrected function name and parameter order)
  var date = Utilities.formatDate(new Date(), "GMT+2", "hh:mm dd/MM/yyyy");
  
  // Fetch the PDF and save it to the target folder
  var response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/" + url_ext, options);
  var blob = response.getBlob().setName(ss.getName() + " " + date + '.pdf');
  folder.createFile(blob);
}

2. Fixing the Edit Trigger (onEdit not working) #

The native onEdit function is a simple trigger, which has strict limitations—it can't call services that require authorization (like UrlFetchApp or DriveApp, which your savePDFs uses). That's why it's failing silently.

Instead, we'll create a custom function and set up an installable edit trigger for it:

Step 1: Add this custom trigger function

function onEditTrigger(e) {
  const targetSheet = 'Sheet1';
  const targetCell = 'A1';
  
  const editedSheet = e.range.getSheet();
  const editedCellNotation = e.range.getA1Notation();
  
  // Check if the edited cell is exactly Sheet1!A1
  if (editedSheet.getName() === targetSheet && editedCellNotation === targetCell) {
    savePDFs();
  }
}

Step 2: Create the installable trigger

1. Open your Google Apps Script editor
2. Click the clock-shaped Triggers icon on the left sidebar
3. Click Add trigger
4. Configure the trigger like this:
   • Choose which function to run: onEditTrigger
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On edit
5. Click Save and grant the necessary permissions when prompted

3. Setting Up the 8-Hour Timed Trigger #

Now that savePDFs is fixed, setting up the timed trigger is straightforward:

1. Go back to the Triggers page
2. Click Add trigger
3. Configure:
   • Function to run: savePDFs
   • Event source: Time-driven
   • Time type: Timer
   • Select Every 8 hours
4. Click Save

Both triggers should now work as expected—your spreadsheet will export to PDF every 8 hours, and whenever you edit Sheet1's A1 cell.

内容的提问来源于stack exchange，提问作者Annarita