Skip to content

Instantly share code, notes, and snippets.

@andrewroberts
Forked from Spencer-Easton/exportSpreadsheet.gs
Last active July 2, 2024 08:44
Show Gist options
  • Save andrewroberts/c37d45619d5661cab078be2a3f2fd2bb to your computer and use it in GitHub Desktop.
Save andrewroberts/c37d45619d5661cab078be2a3f2fd2bb to your computer and use it in GitHub Desktop.
Example on how to export a Google sheet to various formats, includes most PDF options. Updated from comments in [Spencer Easton's Gist](https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70)
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
//fzc=true/false repeat column 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.
//printnotes=false Set to false if you don't want to export the notes embedded in a sheet
//top_margin=[number] Margins - you need to put all four in order fir it to works, and they have to be to
//left_margin=[number] 2DP. So 0.00 for zero margin.
//right_margin=[number]
//bottom_margin=[number]
//horizontal_alignment=CENTER Horizontal Alignment: LEFT/CENTER/RIGHT
//vertical_alignment=TOP Vertical Alignment: TOP/MIDDLE/BOTTOM
//scale=1/2/3/4 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
//pageorder=1/2 1= Down, then over / 2= Over, then down
//sheetnames=true/false
//range=[NamedRange] Named ranges supported - see below
// EXPORT RANGE OPTIONS FOR PDF
// Use these options or ...
//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
// ... just use A1 notation
// &range=Y1:AZ25&...
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"
}]
});};
*/
}
@oemersari
Copy link

How can i set custom scale like scale=110%

@andrewroberts
Copy link
Author

I'm afraid these are all the config values I have found. Try "customscale=...", you never know ...

@IagoOteroG
Copy link

There some way to make a page breaker? how should I use "Range"? Thanks!

@saad-gh
Copy link

saad-gh commented Feb 19, 2021

How did you find these parameters?

@andrewroberts
Copy link
Author

andrewroberts commented Feb 20, 2021 via email

@kpaddock
Copy link

Update on the range parameter: the comment above "//need all the below to export a range" is not necessarily correct. Supplying a range in A1 notation works fine, for example: &range=Y1:AZ25&...

@andrewroberts
Copy link
Author

andrewroberts commented Sep 5, 2022

Update on the range parameter: the comment above "//need all the below to export a range" is not necessarily correct. Supplying a range in A1 notation works fine, for example: &range=Y1:AZ25&...

Thanks @kpaddock. Gist updated.

@kpaddock
Copy link

When creating a PDF from Google sheet with the above gs code, Unicode characters like U+1F7E5 (anything U+FFFF or greater) do not display. Anyone know how to fix this?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment