Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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"
}]
});};
*/
}
@anshu1987

This comment has been minimized.

Copy link

anshu1987 commented May 19, 2017

Hi ,
The above code will download the file in google drive only , is there any way i can download file to my local computer drive.

Thanks
Anshu

@jaeyongjaykim

This comment has been minimized.

Copy link

jaeyongjaykim commented Sep 26, 2017

Thank you for the working code.
Is there a way to change name of the exported file?

Or, is it possible to grab the original file name (spreadsheet name) before exporting?
Thank you.

@adambrother

This comment has been minimized.

Copy link

adambrother commented Mar 15, 2018

Hi,

this script is fantastic!

I'm beginner to scripting and trying to modify it in order to export a specific Named Range.

But don't know how to do that.

Do you have a variant of your script doing this?

Thanks in advance

@cwlind

This comment has been minimized.

Copy link

cwlind commented Jun 18, 2018

Still works great after a few years of use. After some brute-force trial and error, I found another pdf export option. "Printnotes" is true by default, so if you don't want to export the notes embedded in a sheet, you can add the option: &printnotes=false

@KKellyC

This comment has been minimized.

Copy link

KKellyC commented Jun 20, 2018

Another option for &range, found it supports named ranges!

&gid=sheetId&range=NamedRange using your actual named range

@MrLando1

This comment has been minimized.

Copy link

MrLando1 commented Jun 29, 2018

Hi, apologies in advance but I'm a noob to scripting...

Great bit of code.... I can get it to work and have started tweaking to my requirements..

How do:
a). Save the pdf with a specific name (rather than export.pdf which is what currently occurs?
b). Save to a specific google drive folder?

thanks in advance

UPDATE:
Found out how to set the name... amend the line:

var response = UrlFetchApp.fetch(url, params).getBlob()

...by appending this to the end: .setname("myName")

e,g, var response = UrlFetchApp.fetch(url, params).getBlob().setName("myName")

@JohnSpilker

This comment has been minimized.

Copy link

JohnSpilker commented Jul 9, 2018

Thank you. This is fantastic.

@RenaudHoule

This comment has been minimized.

Copy link

RenaudHoule commented Aug 8, 2018

You can also set the margins using

&top_margin=0.3&left_margin=0.3&right_margin=0.3&bottom_margin=0.3

you need to put them all four in order to works!

@v-python

This comment has been minimized.

Copy link

v-python commented Aug 13, 2018

I'm confused about the environment this script is intended to be used in: Does it run on the web and export to Google Drive? Or does it run on a local browser, and download to the computer and upload back to Google Drive (double network traffic)?

What is the ScriptApp.getOauthToken call? Does the script have to be authorized somehow?

Where would I put this script to use it? In the spreadsheet, or separate from the spreadsheet? If in the spreadsheet, wouldn't there be a way to get the ssID without hard-coding it? If separate from the spreadsheet, where? And do you need a separate copy of the script for each spreadsheet you want to export, because of the hard-coded ssID?

@nadampski

This comment has been minimized.

Copy link

nadampski commented Oct 11, 2018

MrLando1 - you can specify the output folder like this:
// save to drive
var dir = DriveApp.getFolderById("your_id_here");
var file = dir.createFile(response);

@Caltor

This comment has been minimized.

Copy link

Caltor commented Jan 2, 2019

Is there a query parameter to NOT print Notes?

@rmOrtiz

This comment has been minimized.

Copy link

rmOrtiz commented Jan 17, 2019

Is there a query parameter to NOT print Notes?

There is a comment above from 'cwlind'. says that &printnotes=false would help since is true by deafault. I hope that helps.

@ricl0250

This comment has been minimized.

Copy link

ricl0250 commented Jan 29, 2019

is there a query parameter to centre horizontally the printed columns?

@ricl0250

This comment has been minimized.

Copy link

ricl0250 commented Jan 29, 2019

found it
&horizontal_alignment=CENTER // Alignment - horizontal: LEFT/CENTER/RIGHT
&vertical_alignment=TOP // Alignment - vertical: TOP/MIDDLE/BOTTOM

@klexl

This comment has been minimized.

Copy link

klexl commented Mar 1, 2019

hi, does anybody know if there is a query parameter to include the settings of the custom page breaks to the url?

@Gillinghammer

This comment has been minimized.

Copy link

Gillinghammer commented Mar 13, 2019

hi, does anybody know if there is a query parameter to include the settings of the custom page breaks to the url?

This page has been extremely helpful! Agreed having the ability to change the custom page breaks is the only thing missing. Anyone know?

@brianscodery

This comment has been minimized.

Copy link

brianscodery commented Mar 29, 2019

Great summary, thanks!

One comment, though, I'm pretty sure line 21 should be:

//fitw=true/false fit width or actual size

@Krivozhnya

This comment has been minimized.

Copy link

Krivozhnya commented Apr 6, 2019

This is great. Is there parameter for custom size, height and width?

@dkeys222

This comment has been minimized.

Copy link

dkeys222 commented Jun 5, 2019

I see there is a way to Fit to width using ( fitw=true ) but is there a way to Fit to page meaning I need to Fit to Width and Height?

@contributorpw

This comment has been minimized.

Copy link

contributorpw commented Jun 6, 2019

@dkeys222,

It's depended of the current page orientation.

@jerezapo

This comment has been minimized.

Copy link

jerezapo commented Jun 22, 2019

I'm struggling with getting this to export only a single sheet. I can do it other ways, using https://spreadsheets.google.com/feeds/download/spreadsheets/Export?... Is there way to add the gid or sheetname?
Update:
got it! Need to add the gid as such: &gid=
example: export?format=pdf&gid=1234..

@rmOrtiz

This comment has been minimized.

Copy link

rmOrtiz commented Jun 24, 2019

@jerezapo you are right! that is the correct way to specify the sheet name.

Thanks for this awesome page! it is very helpful.

Now, I have a question, does anybody knows if is possible to print in pdf but with "custom headers"?

@contributorpw

This comment has been minimized.

Copy link

contributorpw commented Jun 24, 2019

@luciandornescu

This comment has been minimized.

Copy link

luciandornescu commented Sep 2, 2019

Hello, Is there a way to export only 2 of 5 sheets in one pdf?

@coppit

This comment has been minimized.

Copy link

coppit commented Sep 11, 2019

@oshliaer,

I think the question is if one has a lot of rows, fit by width will split the rows across multiple pages. I want to fit by height as well so that everything gets narrower/smaller, and only uses one page. There's a "Fit to Height" option in the print dialog for this. Has anyone figured out the URL equivalent?

@coppit

This comment has been minimized.

Copy link

coppit commented Sep 11, 2019

Found it here: https://stackoverflow.com/a/46312255 ! scale=3 for height fit, scale=4 for page fit.

@contributorpw

This comment has been minimized.

Copy link

contributorpw commented Oct 1, 2019

@coppit, thank you!

@jeffkahsen

This comment has been minimized.

Copy link

jeffkahsen commented Dec 3, 2019

hi, does anybody know if there is a query parameter to include the settings of the custom page breaks to the url?

Would love to find an answer to this as well. I can't find a way to programmatically add page breaks on my export. Given they've recently made custom page breaks available in the UI, I figure it has to be available via scripts... Anyone able to figure that out yet?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.