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

andrewroberts commented Apr 16, 2021 via email

@kguiste95
Copy link

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

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

@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

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

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

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

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

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

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

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

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

@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 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

@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 via email

@brandleesee
Copy link

Wish to ask whether there is a way to generate a pdf with different internal values.
That is, a spreadsheet that each time it is refreshed, all the data is generated afresh.
The intention is that if the link is clicked twice then two pdfs are generated with different data.
Is this achievable, please?
Thank you in advance.

@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?

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