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>

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

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

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 PDF

  • portrait=false: Sets the orientation to landscape (setting portrait=true will result in portrait orientation)

  • gridlines=false: Do not show gridlines

  • size=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.

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);
}

When you run the testExportSheetAsPDF() function, you should see the exported file's content type and file size printed to the execution log.

A screenshot of the script's 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.

A screenshot of an email.

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.

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

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

pdf

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.

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.

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.

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.

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.

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.

Sign up to be notified when I publish new content

By signing up you agree to the Privacy Policy & Terms.