-
-
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" | |
}] | |
});}; | |
*/ | |
} |
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
Additional options: https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70?permalink_comment_id=4379138#gistcomment-4379138