Comprehensive guide: Export Google Sheets as PDF, Excel, CSV, or other formats using Apps Script
In this tutorial, I will show you how to export a Google Sheets spreadsheet into other file formats using Apps Script. This tutorial is for you if you've ever wanted to use Apps Script to automatically convert a Google Sheets spreadsheet to:
A PDF file
A PDF file in landscape orientation
A CSV or TSV file
An Excel file
A HTML web page
In addition, you'll learn how to customize the exported file. Please note that most of these custom settings will only apply to PDF exports.
Prerequisites
This tutorial assumes that you're familiar with Google Sheets and Apps Script. If you are new to coding or if you're not familiar with Apps Script, please consider reading my tutorial series on learning to code using Google Sheets and Apps Script.
How to export a Google Sheets spreadsheet into other file formats?
Before jumping into details, let me explain the high-level approach that we'll use to convert Google Sheets to other file formats. Open any Google Sheets spreadsheet. The URL of that spreadsheet will look something like:
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/edit#gid=<GID>
Note
In the above URL, <SPREADSHEETID>
and <GID>
are the ID of your spreadsheet and the ID of the sheet within your spreadsheet respectively.
Now, delete everything after "/edit" in the URL and you'll be left with a URL like the one below.
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/edit
Note
Remember to replace <SPREADSHEETID>
in the URL with the ID of your spreadsheet as you follow along this tutorial.
Finally, replace "edit" in this URL with "export" and open this URL in your browser. When you open this URL, your spreadsheet will be downloaded as an Excel (.xlsx) file.
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export
If you append "?format=pdf" to the URL, your spreadsheet will download as a PDF.
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf
You can customize this PDF further by appending additional URL parameters. For example, you can set the orientation of the page to landscape.
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false
Note
In the URL below, you have two parameters: format and orientation. Each parameter is assigned a value. The parameter format
is assigned the value pdf
and the parameter portrait
is assigned the value false
. Please note that there is a question mark (?
) in the URL right before the parameters are specified. Also, note that there is an ampersand (&
) separating each parameter.
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false
This tutorial will cover a number of parameters that you can use to customize the exported file. You can use one or more of these parameters to ensure the exported file meets your requirements. For example, suppose you want to export your spreadsheet as a PDF file in landscape orientation, without gridlines, using the B5 page size, your URL will look like this:
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&gridlines=false&size=b5
In the above URL, we're using four parameters:
format=pdf
: Set the exported file format to PDFportrait=false
: Sets the orientation to landscape (setting portrait=true will result in portrait orientation)gridlines=false
: Do not show gridlinessize=b5
: Set the page size to "B5"
So far we've learned how to create a URL to export your Google Sheets spreadsheet. However, you have to manually open this URL in your browser to download the exported file. The next step is to automate this using Apps Script.
Export a Google Sheet using Apps Script
The function getFileAsBlob()
takes the URL as input and returns the file as a blob.
What is a blob?
A blob is a data interchange format in Apps Script. It is a mechanism to store and transmit data across Apps Script APIs. For example, suppose you want to export your Google Sheets spreadsheet as a PDF file and then attach it to an email you send using the MailApp
API. You'll create a blob using the getFileAsBlob()
function and then attach this blob to the email. The MailApp
API knows how to receive and process the PDF since it knows how to read and use the blob format in which the PDF is delivered to it.
function getFileAsBlob(exportUrl) {
let response = UrlFetchApp.fetch(exportUrl, {
muteHttpExceptions: true,
headers: {
Authorization: 'Bearer ' + ScriptApp.getOAuthToken(),
},
});
return response.getBlob();
}
Let's test this function by exporting the spreadsheet as a PDF file and logging its content type and file size. The content type tells us the type of content in the file. For a PDF file, we'd expect its content type to be application/pdf
. The size of the file will be in bytes so we will divide by 1000000 to convert it to MB.
function testExportSheetAsPDF() {
let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");
Logger.log("Content type: " + blob.getContentType());
Logger.log("File size in MB: " + blob.getBytes().length / 1000000);
}
Note
If you get an "Invalid argument" error, please check if you've replaced <SPREADSHEETID>
with the ID of your spreadsheet.
When you run the testExportSheetAsPDF()
function, you should see the exported file's content type and file size printed to the execution log.
If this worked for you then you're almost done! All you have to do is use the blob to do something with the file you exported. For example, you can save the file to Google Drive or send it as an email attachment.
Name your exported file
You can name the exported file using the setName()
method of the blob object. If you do not set its name, the exported file will have a default name like "export.pdf".
let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");
blob.setName("Monthly sales report");
Save the exported file to Google Drive
Once you export the Google Sheets spreadsheet as a blob, you can save it to Google Drive by using the DriveApp.createFile(blob)
method.
function exportSheetAsPDFToDrive() {
let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");
let file = DriveApp.createFile(blob);
Logger.log(file.getUrl());
}
The function exportSheetAsPDFToDrive()
will save the blob as a file in Google Drive and will print the URL of the saved file to the execution log.
Send the exported file as an email attachment
A common use case for exporting a spreadsheet as a PDF file is emailing it to someone else. Once you export your file as a blob, it is very simple to send it as an email attachment.
function sendExportedSheetAsPDFAttachment() {
let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?format=pdf&portrait=false&size=b5&gridlines=false");
var message = {
to: "youremail@example.com",
subject: "Monthly sales report",
body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
name: "Bob",
attachments: [blob.setName("Monthly sales report")]
}
MailApp.sendEmail(message);
}
The recipient of your email will receive the exported file as an attachment.
Custom settings for exporting your Google Sheets spreadsheet using Apps Script
Up until this point in this tutorial, I showed you how to:
Create a URL to export a Google Sheets spreadsheet and test it manually
Automate exporting a Google Sheets spreadsheet using Apps Script
Send the exported file as an email attachment or save the exported file to Google Drive
Next I will show you how to customize your exported file. You can configure:
File format: Specify how you want to export your Google Sheets spreadsheet. As a PDF, CSV, TSV, Excel file (XLS or XLSX), Open document spreadsheet, or HTML.
Orientation: Specify whether the exported file should be in portrait or landscape orientation.
Size: Specify the page size for the exported file (e.g. B5).
Scale: Specify how to scale your spreadsheet in the exported file. The default is normal (100%) but you can choose to fit to width, height or the page.
Sheet names: Specify if you want the names of sheets to be displayed in the exported file.
Specific sheet: Specify a specific sheet to be exported. If your spreadsheet has multiple sheets, you can choose to export just one sheet.
Notes: Specify if notes in your sheet should be displayed in the exported file. These will be exported as footnotes.
Title: Specify if the title of your spreadsheet should be displayed in the exported file.
Gridlines: Specify if gridlines should be displayed in the exported file.
Page numbers: Specify if page numbers should be displayed in the exported file.
Repeat frozen rows: Specify if frozen rows should be repeated on each page in the exported file.
Repeat frozen columns: Specify if frozen columns should be repeated on each page in the exported file.
Specific range: Specify a range to export within a sheet.
Margins: Specify the left, right, top and bottom margins.
Do you know other ways to customize the export?
If you're aware of other parameters that are supported, I'd appreciate it if you could let me know via the contact form below. Thanks so much!
The options to customize the exported file are documented in tables that have the following structure:
Parameter | Value | Description |
---|---|---|
param | val1 | Some description that explains what setting param to val1 will do to the exported file. |
param | val2 | Some description that explains what setting param to val2 will do to the exported file. |
Here param
is the name of the URL parameter and val1
and val2
values that you can set param to. The description explains what will happen if you set param to val1
or val2
.
Suppose you want to set param to val1
, here is how you'd use it:
https://docs.google.com/spreadsheets/d/<SPREADSHEETID>/export?param=val1
Note
Each section below, and its corresponding table document all the values that are supported by a single parameter. The sections and tables collectively document the known parameters that are supported. I say "known parameters" since there isn't any official documentation for any of these parameters. You don't have to use all the parameters. You only need to use the ones that are applicable to your use case. However, for each parameter that you do use, you must pick one of the values that it supports. You cannot use the same parameter more than once and you can't use multiple values for each parameter.
File format
The URL parameter format
is used to specify the type of file you want your spreadsheet exported as. If you do not specify this parameter, your spreadsheet will be exported as an Excel file with extension .xlsx.
Parameter | Value | Description |
---|---|---|
format | Export the spreadsheet as a PDF file. | |
format | csv | Export the spreadsheet as a CSV (comma separated values) file. |
format | xls | Export the spreadsheet as an Excel file in the XLS format. |
format | xlsx | Default Export the spreadsheet as an Excel file in the XLSX format. |
format | tsv | Export the spreadsheet as a TSV (tab separated values) file. |
format | ods | Export the spreadsheet as an ODS (open document spreadsheet) file. |
format | zip | Export the spreadsheet as a HTML file that is zipped. |
Page orientation
The URL parameter portrait
is used to specify the orientation of the page in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Parameter | Value | Description |
---|---|---|
portrait | true | Default Exports the spreadsheet in portrait orientation. |
portrait | false | Exports the spreadsheet in landscape orientation. |
Size
The URL parameter size
is used to specify the size of the page in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Parameter | Value | Description |
---|---|---|
size | letter | Default Sets the page size to 8.5" X 11" |
size | tabloid | Sets the page size to 11" X 17" |
size | legal | Sets the page size to 8.5" X 14" |
size | statement | Sets the page size to 5.5" X 8.5" |
size | executive | Sets the page size to 7.25" X 10.5" |
size | folio | Sets the page size to 8.5" X 13" |
size | a3 | Sets the page size to 11.69" X 16.54" |
size | a4 | Sets the page size to 8.27" X 11.69" |
size | a5 | Sets the page size to 5.83" X 8.27" |
size | b4 | Sets the page size to 9.84" X 13.9" |
size | b5 | Sets the page size to 6.93" X 9.84" |
Scale
The URL parameter scale
is used to specify if and how the page should be scaled in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Parameter | Value | Description |
---|---|---|
scale | 1 | Default Normal scaling (100%) |
scale | 2 | Fit to width |
scale | 3 | Fit to height |
scale | 4 | Fit to page |
Sheet names
The URL parameter sheetnames
is used to specify if sheet names should be displayed in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Parameter | Value | Description |
---|---|---|
sheetnames | true | Display sheet names in the header region of the exported file. |
sheetnames | false | Default Do not display sheet names in the exported file. |
Specific sheet
If your Google Sheets spreadsheet has multiple sheets, you can choose to export just one sheet by specifying its ID. The URL parameter gid
is used to specify the ID of the sheet to export. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF, CSV, TSV, ODS, or XLSX file.
Default — By default, all the sheets in the spreadsheet will be exported.
Note: While you can export a single sheet as an .XLSX file, this parameter isn't supported when exporting the file as an .XLS file.
How to get the ID of a sheet in a Google Sheets spreadsheet?
You can get the ID of a sheet in your Google Sheets spreadsheet from its URL or by using Apps Script.
How to get the ID of a sheet using its URL?
Open the sheet by selecting it and look for the URL parameter called gid in the URL. The ID of the sheet is the value assigned to that URL parameter. In the URL below, the ID of the sheet is 1648375567.
https://docs.google.com/spreadsheets/d/1hw_jpKyAT7SYty-4K-uOt-h7X2HMUzLq6WADVLMJmJQ/edit#gid=1648375567
How to get the ID of a sheet using Apps Script?
You can get the ID of a sheet using the getSheetId()
method of its object. If you know the name of the sheet, you can get its object using the getSheetByName() method of the spreadsheet object. Then you can use the getSheetId()
method to get its ID.
/**
* Return the sheet's ID given its name.
* @param {string} sheetName The name of the sheet.
* @return The ID of the sheet.
*/
function getSheetIdFromName(sheetName) {
let sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
if(sheet)
return sheet.getSheetId();
return null;
}
Parameter | Value | Description |
---|---|---|
gid | <sheet ID> | Only export the sheet specified by <sheet ID>. Other sheets will not be exported. |
Notes
The URL parameter printnotes
is used to specify if notes should be displayed in the exported file. These will be displayed on a separate page at the end of the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
What are notes in Google Sheets?
A note is some text that you can add to a cell by selecting Insert —> Note from the toolbar.
Parameter | Value | Description |
---|---|---|
printnotes | true | Default Display notes in the exported file. |
printnotes | false | Do not display notes in the exported file. |
Title
The URL parameter title
is used to specify if the title of the spreadsheet should be displayed in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Parameter | Value | Description |
---|---|---|
title | true | Display the spreadsheet's title in the exported file. |
title | false | Default Do not display the spreadsheet's title in the exported file. |
Gridlines
The URL parameter gridlines
is used to specify if gridlines should be displayed in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Parameter | Value | Description |
---|---|---|
gridlines | true | Default Display gridlines in the exported file. |
gridlines | false | Do not display gridlines in the exported file. |
Page numbers
The URL parameter pagenum
is used to specify if page numbers should be displayed in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Default — By default, page numbers will not be displayed in the exported file.
Note
From my testing, the only value the parameter pagenum
supports is CENTER
. This doesn't mean the page number will be displayed in the center of the page — it will be displayed in the bottom right hand corner. If you do not want page numbers to be displayed, do not include this parameter in the URL since by default page numbers are not displayed.
Parameter | Value | Description |
---|---|---|
pagenum | CENTER | Display page numbers. |
Repeat frozen rows
The URL parameter fzr
is used to specify if frozen rows should be repeated on every page of the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Default — By default, frozen rows will be repeated on each page of the exported file.
Parameter | Value | Description |
---|---|---|
fzr | true | Default Repeat frozen rows on every page of the exported file. |
fzr | false | Do not repeat frozen rows on every page of the exported file. |
Repeat frozen columns
The URL parameter fzc
is used to specify if frozen columns should be repeated on every page of the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Default — By default, frozen columns will be repeated on each page of the exported file.
Parameter | Value | Description |
---|---|---|
fzc | true | Default Repeat frozen columns on every page of the exported file. |
fzc | false | Do not repeat frozen columns on every page of the exported file. |
Range
The URL parameters r1
, r2
, c1
, and c2
are used to export a specific range within a sheet. You must specify all four parameters, and you must also specify the sheet that contains the range using the gid
parameter.
Note
You must specify the specific sheet (using the gid
parameter) containing the range you want to export. If you don't specify the sheet, the entire spreadsheet will be exported.
Parameter | Value | Description |
---|---|---|
r1 | Integer | The starting row index (please note that the first row has an index of 0). |
r2 | Integer | The ending row index, which must be greater than r1. All rows from r1 up to, but not including, r2 will be exported. |
c1 | Integer | The starting column index (please note that the first row has an index of 0) |
c2 | Integer | The ending column index, which must be greater than c1. All columns from c1 up to, but not including, c2 will be exported. |
Margins
The URL parameters left_margin
, right_margin
, top_margin
, and bottom_margin
are used to customize margins in the exported file. This parameter is only relevant when you export a Google Sheets spreadsheet as a PDF.
Note
You must specify all four margins. Otherwise, the export will not work.
Parameter | Value | Description |
---|---|---|
left_margin | A number (e.g. 0, 0.5, 2, etc.) | The left margin in inches. |
right_margin | A number (e.g. 0, 0.5, 2, etc.) | The right margin in inches. |
top_margin | A number (e.g. 0, 0.5, 2, etc.) | The top margin in inches. |
bottom_margin | A number (e.g. 0, 0.5, 2, etc.) | The bottom margin in inches. |
Conclusion
In this tutorial, I showed you how to convert your Google Sheets spreadsheet to other file formats using Apps Script. I also showed you how to customize the exported file.
Hope you found this tutorial useful.