Google script error: An unexpected error occurred from openById

Question

I' making automation system that google spreadsheet convert it to PDF file and save on my google drive. I've known this script works successfully on my project page, but doesn't work on trigger. how could I resolve this problem?

the error message had occurred at 5:39 line. here

  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();

Please help me!

function savePDFs( optSSId, optSheetId ) {

  // If a sheet ID was provided, open that sheet, otherwise assume script is
  // sheet-bound, and open the active spreadsheet.
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();

  // Get folder containing spreadsheet, for later export
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
      + (optSheetId ? ('&gid=' + sheet.getSheetId()) : ('&id=' + ss.getId()))

      // following parameters are optional...
      + '&size=letter'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=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("https://docs.google.com/spreadsheets/" + url_ext, options);
  var blob = response.getBlob().setName(ss.getName() + '.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
  folder.createFile(blob);

}

If the script is bound, just use var ss = SpreadsheetApp.getActiveSpreadsheet(). If not use var ss = SpreadsheetApp.openById(optSSId) — TheMaster
– TheMaster, Commented Sep 1, 2020 at 3:07
What trigger is calling your function? How are you running the function from the the Google Apps Script editor? — Wicket
– Wicket, Commented Sep 1, 2020 at 3:07
@Rubén I added a triger that it contains the code with a time reservation on google apps script project page. — 김범석
– 김범석, Commented Sep 1, 2020 at 3:13
Is that trigger a time-driven trigger / on change trigger / on form submit trigger....? — Wicket
– Wicket, Commented Sep 1, 2020 at 3:16
@TheMaster Thank you so much. It was helpful. It works well!! — 김범석
– 김범석, Commented Sep 1, 2020 at 3:28

TheMaster · Accepted Answer · 2020-09-01 03:33:50Z

1

If the id passed to openById() is not correct(sometimes without reason),

An unexpected error occurred from openById

is thrown, when using SpreadsheetApp.openById().

If the script is bound, you could just use var ss = SpreadsheetApp.getActiveSpreadsheet().

answered Sep 1, 2020 at 3:33

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wicket · Accepted Answer · 2020-09-01 03:28:35Z

0

In Google Apps Scripts triggers pass an event object to the function that is being called by them.

If your trigger is calling directly the savePDFs function, then the event object is assigned to optSSId. As this is not a string, this might be the cause of the problem.

Instead of calling savePDFs directly, set your trigger to call another function, then make this fuction call savePDFs without passing any variable. I.E.:

function respondToTimeDrivenTrigger(e){
  savePDFs();
}

answered Sep 1, 2020 at 3:28

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Collectives™ on Stack Overflow

Google script error: An unexpected error occurred from openById

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related