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:
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.
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.
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//edit#gid=
In the above URL, and 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//editRemember to replace 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//exportIf you append "?format=pdf" to the URL, your spreadsheet will download as a PDF.
https://docs.google.com/spreadsheets/d//export?format=pdfYou 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//export?format=pdf&portrait=falseIn 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//export?format=pdf&portrait=falseThis 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//export?format=pdf&portrait=false&gridlines=false&size=b5In the above URL, we're using four parameters:
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.
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//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); >If you get an "Invalid argument" error, please check if you've replaced 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.
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//export?format=pdf&portrait=false&size=b5&gridlines=false"); blob.setName("Monthly sales report");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//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.
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//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.
Up until this point in this tutorial, I showed you how to:
Next I will show you how to customize your exported file. You can configure:
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:
Some description that explains what setting param to val1 will do to the exported file.
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//export?param=val1Each 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.
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.
