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.

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