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

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

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.

The options to customize the exported file are documented in tables that have the following structure:

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.

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.

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.

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.

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.

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.

https://docs.google.com/spreadsheets/d/1hw_jpKyAT7SYty-4K-uOt-h7X2HMUzLq6WADVLMJmJQ/edit#gid=1648375567

/**
* 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;
}

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.

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.

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.

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.

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.

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.

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.