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"
}]
});};
*/
}
@luc-ass
Copy link

luc-ass commented Mar 3, 2021

Has anyone found out how to enable Filename, Sheetname, Date and Time in the header/footer? I've not been able to find a file in the source code that shows the options...

@nussisworld
Copy link

nussisworld commented Mar 3, 2021

Has anyone found out how to enable Filename, Sheetname, Date and Time in the header/footer? I've not been able to find a file in the source code that shows the options...

I have created a header in my sheet to be printed. There you will find the "PDF NAME" field. To show the current date there I use "= today ()". So that the PDF gets this file name, I use the following code:

// Set the output filename var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('Bestellformular'); var value = sheet.getRange('C1').getValue(); var pdfName = value;

@luc-ass
Copy link

luc-ass commented Mar 3, 2021

That's certainly one way to do it and pointed me to naming my file the right way. Perhaps to be a little more precise: I am willing to do some reverse engineering to find missing options to this api. The thing I am missing is a starting point. Any ideas which file to start with?

@nussisworld
Copy link

nussisworld commented Mar 3, 2021

That's certainly one way to do it and pointed me to naming my file the right way. Perhaps to be a little more precise: I am willing to do some reverse engineering to find missing options to this api. The thing I am missing is a starting point. Any ideas which file to start with?

Do you need a working demo script?

@luc-ass
Copy link

luc-ass commented Mar 3, 2021

I have a working demo, I am just trying to find more options like these

"fitw=true&"+
"gridlines=false&"+
"printtitle=false&"+
"sheetnames=false&"+

@itsazzad
Copy link

itsazzad commented Mar 5, 2021

What are the export range options for XLSX?
The PDF export range options do not seem to be working for XLSX.

@JerryGarcia
Copy link

JerryGarcia commented Apr 15, 2021

I'm just curious how you learned how to do this originally.

@lyehengfoo
Copy link

lyehengfoo commented Apr 16, 2021

This post/article and the additional alignment parameters in @samjco reply, has helped me a great deal to export a Google sheet into PDF. Thank you for this great sharing!

@kguiste95
Copy link

kguiste95 commented Apr 16, 2021

How does one learn about these about the different elements and options within the export url?
For example, 'format=pdf'... Is there some documentation for learning the components of a export url?

@andrewroberts
Copy link

andrewroberts commented Apr 16, 2021

@kguiste95
Copy link

kguiste95 commented Apr 16, 2021

This is curious, I'm relatively new to programming, I'm focusing on the google services. Google has a vast amount of resources and documentation, however it's obviously not exhaustive.

Surely there must be some rationale around structuring the urls. They work because the systems have been developed for them to work right? So, how they work should be documented somewhere?

@mrddt-tech
Copy link

mrddt-tech commented May 2, 2021

Could anyone give me a link to full documentation of this google API (that exports Sheet as PDF)? Thank you!

@plajko
Copy link

plajko commented May 4, 2021

I used this endpoint for exporting sheets as pdf, but it stopped working for me with error:

BAD_REQUEST: 400. That’s an error. The server cannot process the request because it is malformed. It should not be retried. That’s all we know.

Any idea?

@jonahmolinski
Copy link

jonahmolinski commented May 4, 2021

@plajko Looks like there is now a redirect happening. Make sure your redirected URL request has the access token header and it will start working again.

@plajko
Copy link

plajko commented May 4, 2021

@jonahmolinski thanks, I saw that redirect when opened in browser, but with the access token in Authorization header it fails before getting the http 3xx.

@plajko
Copy link

plajko commented May 4, 2021

@jonahmolinski Sorry, my bad :) the http client I was using was following redirects automatically (without forwarding the headers), now works like a charm! Thanks!

@ricketyca
Copy link

ricketyca commented May 18, 2021

Some time ago I had used this helpful information above to generate html code to download a PDF file from a sheet. I plugged the appropriate codes into an href which I displayed on a web page on a google site. I could not get the range feature to work. I discovered that when I appended the href html, the "&ic" was being converted to "%E2%81%A3" and therefore any attempt to activate the href (I was using a button) failed. Things were a bit rushed so I did not bother as it was not critical.

I have now discovered a simple solution. If instead of "&ic" I instead use the coded form, (following the & with "amp;" and then the "ic") and it now works OK in my situation.

The same 'conversion' was happening to '&range' and inserting the 'amp;' solved it there too. For interest's sake this only applies to the href download and not when you use it in a GET

@ricketyca
Copy link

ricketyca commented May 19, 2021

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

You may find this can work for you. Instead of a script using the GET I have a page with a button where I put the link. Then it downloads. The script sample is getting the blob of the PDF and explicitly loading it into the Drive folder. I realise this may not be suitable for you but wanted you to know a way. hth

@bihaequipinc
Copy link

bihaequipinc commented Jul 8, 2021

Hi,

The code is work for me.
But how to export a few sheets?
Meaning a few gid ?

@samjco
Copy link

samjco commented Jul 8, 2021

So Google doesn't have a full-blown API of how to do everything with Gsheets and PDF. And I think it kinda strange that we are left to figuring things out... So I am now looking into phpOffice, wondering if data could be passed into this environment and then convert to pdf
https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file/#pdf
examples:
https://stackoverflow.com/a/55356683
https://stackoverflow.com/a/39355051

OR

Something like this will work using: http://fpdf.org/

So the idea is to make a button in Gsheets that will produce an output of a dynamic CSV link. Then add FPDF to your server to ingest that data and convert it on the fly and produce a PDF that will show in the browser. Here is a sample code:
http://fpdf.org/en/script/script102.php

example output:
http://fpdf.org/en/script/ex102.pdf

@bihaequipinc
Copy link

bihaequipinc commented Jul 9, 2021

I have my own template with google sheet. Like impossible to pass my template to phpOffice. Hmm do you have any ideas on how to export few sheets or few tab using appsscript ?

@bihaequipinc
Copy link

bihaequipinc commented Jul 9, 2021

So Google doesn't have a full-blown API of how to do everything with Gsheets and PDF. And I think it kinda strange that we are left to figuring things out... So I am now looking into phpOffice, wondering if data could be passed into this environment and then convert to pdf
https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file/#pdf
examples:
https://stackoverflow.com/a/55356683
https://stackoverflow.com/a/39355051

OR

Something like this will work using: http://fpdf.org/

So the idea is to make a button in Gsheets that will produce an output of a dynamic CSV link. Then add FPDF to your server to ingest that data and convert it on the fly and produce a PDF that will show in the browser. Here is a sample code:
http://fpdf.org/en/script/script102.php

example output:
http://fpdf.org/en/script/ex102.pdf

I have my own template with google sheet. Like impossible to pass my template to phpOffice. Hmm do you have any ideas on how to export few sheets or few tab using appsscript ?
Untitled

@Yagisanatode
Copy link

Yagisanatode commented Mar 16, 2022

Has anyone found out how to enable Filename, Sheetname, Date and Time in the header/footer? I've not been able to find a file in the source code that shows the options...

I did find that 'printdate' resulted in an error instead of being ignored like other queries. This might suggest that it is a valid query, but it did not respond to boolean, number or date format, so I don't know what the expected values should be.

@bihaequipinc
Copy link

bihaequipinc commented Mar 16, 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.

@robertcragg
Copy link

robertcragg commented Jul 19, 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!

@bihaequipinc
Copy link

bihaequipinc commented Jul 19, 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!

what you are doing is get the date/time to show ?

@robertcragg
Copy link

robertcragg commented Jul 19, 2022

@bihaequipinc I haven't been able to get it to show. I thought in your previous post you were saying you figured it out, but apologies if I misunderstood. No parameters I have tried have worked successfully unfortunately, but I would love to figure it out!

@bihaequipinc
Copy link

bihaequipinc commented Jul 19, 2022

@bihaequipinc I haven't been able to get it to show. I thought in your previous post you were saying you figured it out, but apologies if I misunderstood. No parameters I have tried have worked successfully unfortunately, but I would love to figure it out!

hehe, yes you misunderstood. I think the code that I found were export multiples tab from google sheet.

@robertcragg
Copy link

robertcragg commented Jul 19, 2022

@bihaequipinc I see. Well thank you anyways.

PS: If anyone else in this thread has figured out the parameter to get current date/time to export on a PDF through App Script I would love to know what it is!

@ricketyca
Copy link

ricketyca commented Jul 19, 2022

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