0

I have tried working with the script created by Mogsdad, Dr Queso, Gilbert W, Brian Buford, and others. From here: Convert all sheets to PDF with Google Apps Script

I want to:

  1. generate individual .PDF files named after each sheet name.
  2. Exclude certain files from being turned into PDF (i.e. "DB")
  3. if possible generate some sheet-to-pdf as Landscape and others as Portrait.
function savePDF() {
    SpreadsheetApp.flush();

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    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=false' + //hide optional
    '&gridlines=false' + //false = hide gridlines
    '&fzr=false' + //do not repeat row headers (frozen rows) on each page
    '&gid='; //leave ID empty for now, this will be populated in the FOR loop

    var token = ScriptApp.getOAuthToken();

    //make an empty array to hold your fetched blobs
    var blobs = [];

    //.fetch is called for each sheet, the response is stored in var blobs[]
    for(var i = 0; i < sheets.length; i++) {
        var sheetname = sheets[i].getName();

        //if the sheet is one that you don't want to process,
        //continue' tells the for loop to skip this iteration of the loop
        if(sheetname == "DB")
            continue;

        //grab the blob for the sheet
        var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
            headers: {
                'Authorization': 'Bearer ' +  token
            }
        });

        //convert the response to a blob and store in our array
        blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
        var array_blob = response.getBlob().setName(sheets[i].getName() + '.pdf');
    }
}

Sadly neither the above nor any other solution seems to produce multiple .PDF files. Just one giant file akin to the entire workbook/spreadsheet.

Freezergeezer
  • 15
  • 4
  • Have you debugged the URLs produced? `var finalUrl = url + url_ext + ; console.log({message: "URL for sheet " + sheetname, url: finalUrl}); ...` You can then check Stackdriver to see the individual URLs that are getting created, and test them in your browser. – tehhowch Mar 26 '19 at 20:38
  • hmm I can't get the 2nd line of your code to work for some reason. – Freezergeezer Mar 26 '19 at 22:11
  • `` is not valid syntax. You're meant to substitute the actual code for obtaining the sheet ID, which you have written already. And you may be conflating Stackdriver's `console.log` and `Logger.log` - they are different. https://developers.google.com/apps-script/guides/logging – tehhowch Mar 26 '19 at 22:41
  • Something broke completely. Code remains the same but now the script runs into an error and won't produce the original "big" pdf either. "returned code 500." – Freezergeezer Mar 27 '19 at 19:22
  • @Freezergeezer Can you add your current script to your question? – Tanaike Apr 07 '19 at 02:25
  • @Tanaike The current script is the original pasted code. I don't see any typos, is it possible Google made changes that affect it? – Freezergeezer Apr 19 '19 at 10:25
  • @Freezergeezer Thank you for replying. From your comment of ``Something broke completely. Code remains the same but now the script runs into an error and won't produce the original "big" pdf either. "returned code 500."``, I thought that you modified your script. How about this? I would like to know about your latest script. – Tanaike Apr 20 '19 at 02:36

0 Answers0