Google Sheets to pdf - combine to one pdf instead of splitting into multiple pdfs-CodePudding

I found a script here that does everything I want however for some reason it is splitting the document into several pdf's. I would like to keep everything as it is however just create one single pdf.

Edit: My Google sheets have 5 worksheets and each worksheet at the moment becomes a seperate pdf so I end up with 5 different pdfs. I would like it to just come out as 1 pdf with 5 pages inside.

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');  // 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=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(url   url_ext, options);
    var valor = sheet.getRange('D5').getValue();  // Modified
    var blob = response.getBlob().setName(valor   '.pdf');
    folder.createFile(blob);
  }
}

CodePudding user response：

The reason you are getting 5 PDF files is that, you are iterating between each page of the sheet and creating a PDF from there; instead of calling the entire sheet. I ran a test with the following code, and it exported the Google sheet as a 1 PDF.

Note: I made some changes to the first part of the code since I didn't have access to the other functions been call.

I remove this part:

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
        '&size=letter'      // paper size

And modify it like this:

// I didn't have access to the optSSId or optSheetId so for my test I added the URL and the active sheet link to the apps script
  // you can keep this part as you had it before
  var url = "https://docs.google.com/spreadsheets/d/<spreadsheet_ID>/";
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 

  // From the URL_ext I remove    '&gid='   sheet.getSheetId()   since we want the complete sheet
  var url_ext = '/export?exportFormat=pdf&format=pdf'   //export as pdf
    '&size=letter'      // paper size

So basically you form this URL:

https://docs.google.com/spreadsheets/d/<SPREADSHEETID>//export?exportFormat=pdf&format=pdf&size=letter&portrait=true&fitw=true&sheetnames=false&printtitle=false&pagenumbers=false&gridlines=false&fzr=false

Update:

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 url = "https://docs.google.com/spreadsheets/d/<Sheet_ID>/";
  var folders = DriveApp.getFolderById("<Folder_ID>");  // Modified
  //var folder = folders.hasNext() ? folders.next() : parents.next();  // Modified


  // I didn't have access to the optSSId or optSheetId so my test I added the URL and the active sheet link to the apps script
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
    '&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(url   url_ext, options);
  // var valor = sheet.getRange('D5').getValue();  // Modified
  var blob = response.getBlob().setName("test"   '.pdf');
  folders.createFile(blob);
}

Replace <Sheet_ID> with the ID of the Google Sheet and <Folder_ID> with the ID of the folder where the PDF file will be store.