Example on how to export a Google sheet to various formats, includes most PDF options

exportSpreadsheet.gs

function exportSpreadsheet() {
 
  //All requests must include id in the path and a format parameter
  //https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export
 
  //FORMATS WITH NO ADDITIONAL OPTIONS
  //format=xlsx       //excel
  //format=ods        //Open Document Spreadsheet
  //format=zip        //html zipped          
  
  //CSV,TSV OPTIONS***********
  //format=csv        // comma seperated values
  //             tsv        // tab seperated values
  //gid=sheetId             // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID
  
  // PDF OPTIONS****************
  //format=pdf     
  //size=0,1,2..10             paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5  
  //fzr=true/false             repeat row headers
  //portrait=true/false        false =  landscape
  //fitw=true/false            fit window or actual size
  //gridlines=true/false
  //printtitle=true/false
  //pagenum=CENTER/UNDEFINED      CENTER = show page numbers / UNDEFINED = do not show
  //attachment = true/false      dunno? Leave this as true
  //gid=sheetId                 Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. 
                               // Leave this off for all sheets. 
  // EXPORT RANGE OPTIONS FOR PDF
  //need all the below to export a range
  //gid=sheetId                must be included. The first sheet will be 0. others will have a uniqe ID
  //ir=false                   seems to be always false
  //ic=false                   same as ir
  //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
  //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
  //r2=End Row number
  //c2=End Column number
 
  var ssID = "12g8-tcRwFkcL7El...XdQAzSR7v8-geIR6r-IY";
  var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export"+
                                                        "?format=pdf&"+
                                                        "size=0&"+
                                                        "fzr=true&"+
                                                        "portrait=false&"+
                                                        "fitw=true&"+
                                                        "gridlines=false&"+
                                                        "printtitle=true&"+
                                                        "sheetnames=true&"+
                                                        "pagenum=CENTER&"+
                                                        "attachment=true";
                                                        
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  var response = UrlFetchApp.fetch(url, params).getBlob();
  // save to drive
  DriveApp.createFile(response);
  
  //or send as email
  /*
  MailApp.sendEmail(email, subject, body, {
        attachments: [{
            fileName: "TPS REPORT" + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });};
  */
  
}

I couln't find official support documentation around the filesize limitations for blob. Anyone knows (related to GAS)?

@mertdotcc

export a specific range

  //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
  //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
  //r2=End Row number
  //c2=End Column number

a spreadsheet as a PNG

Is it possible?

does anyone know a key for including/excluding notes? They seem to be included by default (opposite of what happens when you ctrl+P on google sheets).

Figured it out -- includenotes=false

Very helpful ! There's a GAS sample that provides similar info, albeit without explanations.

Would anyone know how custom page breaks are set via this API ?

pagenum can be LEFT or RIGHT too

Can anyone help point out where I can manage the PDF file name? Ideally, I'd like to incorporate the value of a cell into the file name. Don't seem to be able to find where through some trial and error. This may not be the right place. Any help is appreciated!

I have not found one. In case it helps, it seems as though the filename is a combination of Spreadsheet (Book) name, and Sheet name that is bring printed so conceptually "Spreadsheet - Sheet.pdf"

Line 53 var response = UrlFetchApp.fetch(url, params).getBlob().setName("foobar.pdf"); Steve Decker Tech Done For You

…

On Tue, May 2, 2023 at 5:37 PM jasondanielssq ***@***.***> wrote: @jasondanielssq commented on this gist. ________________________________ Can anyone help point out where I can manage the PDF file name? Ideally, I'd like to incorporate the value of a cell into the file name. Don't seem to be able to find where through some trial and error. This may not be the right place. Any help is appreciated! — Reply to this email directly, view it on GitHub or unsubscribe. You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS or Android.

@techdoneforyou - Thank you very much! That's what I needed!

Great script Spencer - just curious if you had time to reverse engineering Custom Page breaks in Google Sheets and how to export the sheets to PDFs using Custom Page Breaks. Many thanks.