Skip to content

Instantly share code, notes, and snippets.

@Spencer-Easton
Last active August 9, 2024 07:45
Show Gist options
  • Save Spencer-Easton/78f9867a691e549c9c70 to your computer and use it in GitHub Desktop.
Save Spencer-Easton/78f9867a691e549c9c70 to your computer and use it in GitHub Desktop.
Example on how to export a Google sheet to various formats, includes most PDF options
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"
}]
});};
*/
}
@techdoneforyou
Copy link

techdoneforyou commented Nov 4, 2022 via email

@basetoad
Copy link

basetoad commented Nov 4, 2022

You may need to provide an explanation of how the sheet is 'refreshed' that causes it to generate new value, and how you are intending to generate the PDF.

For example, if you have an 'onOpen' script in the sheet, it will not run if you have a timed (or external) script that extracts a PDF because the onOpen will not run at that point.

I hope that may help a little.

@brandleesee
Copy link

@basetoad

You may need to provide an explanation of how the sheet is 'refreshed' that causes it to generate new value

Every time the spreadsheet is accessed or refreshed the values of many cells are regenerated by means of the following code:

=ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(settings!$E$2:$E$14, RANDBETWEEN(1, 13))), 1, 1)

how you are intending to generate the PDF

The intention is to have students click on the link to generate a pdf to then print and work academic exercises.

Each pdf contains a set of exercises which change each time the spreadsheet is refreshed by means of variables (these are the earlier mentioned cells operated using the code above).


@techdoneforyou

SpreadsheetApp.flush()

Is there a way to incorporate this in a link?
That is, I send a clickable link to my students who are presented with a pdf ready for printing.
For example,
https://docs.google.com/spreadsheets/d_____________/export?format=pdf&portrait=true&size=7&gid=____________&range=A1:D16

Thank you both for helping me out.

@basetoad
Copy link

basetoad commented Nov 4, 2022 via email

@JasonPan
Copy link

JasonPan commented Nov 23, 2022

Thanks for your message @Yagisanatode , but i believe i have found the answer . but forgot what's the code is. i will share here once i remember.

I have been racking my brain trying to figure this out myself. I would be forever in your debt if you could post this information once you find it. I have tried several different combinations to try and get the date/time footer to show, but no luck. Thanks in advance for any assistance you can provide!

@Yagisanatode @robertcragg I did some digging across the interwebs and worked it out, hope this helps someone out there:

  • printdate=true/false
  • printtime=true/false
  • timestamp=[0, X) where x is a non-negative number - not sure what the maximum value allowed is (I'll leave this as homework for the next contributor 😉). Required if either printdate / printtime are set to true. Value represents the "1900 Spreadsheet Date System" timestamp, based in UTC.

Some additional remarks on the timestamp

  • Note that the timestamp here is the weird Sheets / Excel format (based on the historical spreadsheet "epoch")
  • Google Sheets docs here suggest that the earliest date possible is (1/1/1900), but you'll observe that if you write the formula =DATE(0,1,-1) in Google Sheets, you'll get 30/12/1899. Similarly, if you format a cell with the value 0 via menu option Format > Number > Date, you'll see 30/12/1899. This matches exactly with what the export API returns.
  • For those curious, some digging reveals its probably historically related to this, some commentary available here
  • Some JavaScript code to help with the conversions (painful, but necessary for me working with Apps Script)
function SpreadsheetTimestampToJSDate(timestamp) {
  return new Date(Math.round((timestamp - 25569)*86400*1000));
}

function JSDateToSpreadsheetTimestamp(date) {
  return (date.valueOf() / (86400*1000)) + 25569;
}
  • Note that I haven't been able to find a timezone option; by default the date is rendered in UTC. So if you want local time, you'll need to add the offset - e.g. for GMT+11:00, use SpreadsheetTimestampToJSDate(new Date(new Date().valueOf() + 11 * 3600000))

Thanks for tuning in, bye!

@mertdotcc
Copy link

Does anyone know how to export a specific range in a spreadsheet as a PNG and not PDF? Many thanks in advance!

@nl8io
Copy link

nl8io commented Dec 9, 2022

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

@github0013
Copy link

@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

Screen Shot 2022-12-09 at 16 09 56

Is it possible?

@Noahyt
Copy link

Noahyt commented Feb 28, 2023

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).

@Noahyt
Copy link

Noahyt commented Feb 28, 2023

Figured it out -- includenotes=false

@jr-grenoble
Copy link

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 ?

@1xtr
Copy link

1xtr commented Apr 21, 2023

pagenum can be LEFT or RIGHT too

@jasondanielssq
Copy link

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!

@basetoad
Copy link

basetoad commented May 2, 2023

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"

@techdoneforyou
Copy link

techdoneforyou commented May 3, 2023 via email

@jasondanielssq
Copy link

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

@goyo99
Copy link

goyo99 commented Dec 17, 2023

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.

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