Google Sheets' File > Download > PDF menu works fine for a one-off export, but exportSheetToPdf automates the exact same export - correctly scoped to a single tab - so it can run unattended as part of a larger reporting workflow.
Generating that PDF by hand every reporting period means opening the spreadsheet, navigating the export dialog, choosing the right page size and gridline settings, and remembering to select just the one tab that matters rather than the whole workbook.
This example builds the same export URL that the Sheets UI uses internally, authenticates the request with the script's own OAuth token via ScriptApp.getOAuthToken, and saves the resulting PDF blob straight into a specific Drive folder.
By the end you'll have a repeatable, unattended PDF export of the Monthly Report tab, complete with a filename that includes the export date so successive runs never overwrite each other by accident.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Location | Purpose |
|---|---|---|
| Monthly Report | Sheet | The specific tab being exported, identified by its sheet ID |
| Export URL parameters | Script constant | Controls page size, orientation, fit-to-width, and gridlines |
| Destination folder ID | Script constant | The Drive folder where the generated PDF is saved |
What it does
The function constructs an export URL pointing at the specific sheet's gid, appends formatting parameters for letter-size, portrait, fit-to-width, and hidden gridlines, then fetches that URL with an OAuth bearer token before saving the resulting PDF blob into a named Drive folder.
- gid=sheet.getSheetId() ensures only the Monthly Report tab is exported, not the whole spreadsheet
- fitw=true scales the content to fit the page width automatically
- The saved filename includes the export date so repeated runs don't collide
Prerequisites
ScriptApp.getOAuthToken() requires the script's manifest to include the appropriate Drive and Spreadsheets scopes, which Apps Script will prompt for automatically the first time this function runs and requests authorization.
- A sheet named 'Monthly Report' inside the active spreadsheet
- A destination Drive folder, referenced by its folder ID, that the script account can write to
- Appropriate OAuth scopes granted during the authorization prompt
- Awareness that public sharing settings on the spreadsheet can affect whether the export URL is reachable without authentication for other use cases
Walkthrough
ss.getUrl().replace(/edit$/, '') strips the trailing 'edit' segment off the spreadsheet's normal URL, which is necessary because the export endpoint lives at the same base URL but with 'export' appended instead of 'edit'.
The Authorization header is set to 'Bearer ' plus the OAuth token returned by ScriptApp.getOAuthToken(), which authenticates the UrlFetchApp request as the script's own identity rather than requiring the sheet to be publicly shared.
Edge cases
If the Monthly Report tab is deleted and recreated with the same name, its underlying sheetId changes, so any hard-coded reference to the old ID elsewhere in a workflow would silently point at the wrong (or a nonexistent) tab.
- Extremely wide sheets may still overflow a single page even with fitw=true, depending on how many columns are visible
- Hidden rows or columns on the source sheet are excluded from the export, matching what a viewer would see in the UI
- A spreadsheet with restrictive sharing settings can still be exported this way since the OAuth token authenticates as the script owner regardless of link-sharing settings
Testing
Run the function once and open the resulting PDF in the target Drive folder to confirm page size, orientation, and gridline visibility all match expectations before relying on this for a real report.
- Check that only the Monthly Report tab appears in the PDF, not the entire spreadsheet
- Verify the filename's date suffix updates correctly on a second run the next day
- Confirm the OAuth authorization prompt only appears once, on first run, not on every subsequent execution
Hardening
A destination folder that fills up with dozens of same-named PDFs over time gets hard to navigate, so pairing the date-stamped filename with a periodic cleanup or folder-per-month structure keeps things manageable long term.
- Move older exports into dated subfolders automatically instead of leaving everything in one flat folder
- Wrap the UrlFetchApp.fetch call in a try/catch to handle transient network errors gracefully
- Verify response.getResponseCode() is 200 before treating the blob as a valid PDF, since a failed request can sometimes return an HTML error page instead
Variations
The same export URL technique works for entire multi-sheet workbooks by omitting the gid parameter, or for a specific cell range by adding range parameters instead of exporting a full tab.
- Omit the gid parameter to export the entire spreadsheet as a single multi-tab PDF
- Add range-specific parameters to export just a highlighted region instead of the whole tab
- Feed the exported blob directly into MailApp.sendEmail as an attachment instead of saving it to Drive, as shown in the email-pdf-attachment example
Full code: exportSheetToPdf()
The function reconstructs the exact export URL that Sheets' own download menu uses internally, then authenticates programmatically instead of relying on a person clicking through the UI.
function exportSheetToPdf() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Monthly Report');
var url = ss.getUrl().replace(/edit$/, '');
var exportUrl = url + 'export?format=pdf' +
'&gid=' + sheet.getSheetId() +
'&size=letter&portrait=true&fitw=true' +
'&gridlines=false&printtitle=false';
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(exportUrl, {
headers: { Authorization: 'Bearer ' + token }
});
var pdfBlob = response.getBlob().setName('Monthly Report - ' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd') + '.pdf');
var folder = DriveApp.getFolderById('1FolderIdForExports00000000000');
folder.createFile(pdfBlob);
}- Line 4: Strips the trailing 'edit' segment off the spreadsheet URL to get the base export endpoint.
- Line 6: Begins building the export URL with the PDF format parameter.
- Line 7: Scopes the export to exactly one tab using that sheet's unique sheetId as the gid parameter.
- Line 11: Retrieves an OAuth token that authenticates the request as the script's own identity.
- Line 12: Sends the authenticated request to the export endpoint and receives the PDF response.
- Line 16: Names the resulting PDF blob with a date suffix so repeated exports don't overwrite each other.
- Line 19: Saves the finished PDF blob into the designated Drive folder.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before automating PDF exports
- 1Monthly Report sheet ID (gid) confirmed correct
- 2Destination Drive folder ID verified and writable by the script
- 3OAuth scopes granted during the first authorization prompt
- 4Filename date format confirmed to avoid collisions on repeated runs
- 5Page size and orientation parameters matched to the report's actual layout
- 6Considered a cleanup strategy for the destination folder over time
Frequently asked questions
Apps Script doesn't expose a direct 'export sheet as PDF' method on SpreadsheetApp; reconstructing the export URL and fetching it with UrlFetchApp is the standard workaround for getting single-tab PDF control that matches what the UI's download menu produces.
No - the OAuth bearer token authenticates the request as the script's own account, so this works even on a spreadsheet with the most restrictive private sharing settings, as long as that account has view access.
Yes, to a degree - the export URL supports additional parameters like top_margin, bottom_margin, and horizontal_alignment; check the parameters Sheets itself uses by inspecting the URL generated from the manual File > Download > PDF dialog.
Nothing breaks, since the export is scoped by sheetId (gid), which stays constant even if the tab's display name changes; only look-ups that reference the sheet by name, like getSheetByName, would need updating.
Yes, though very large sheets can produce a PDF with many pages depending on the page size and fit settings, and the export request itself may take longer to complete, so consider running it via a time-driven trigger rather than waiting on it interactively.
Adding multiple gid parameters isn't supported directly; instead, either export each tab separately and merge the PDFs afterward, or omit the gid parameter entirely to export the whole spreadsheet and then extract just the pages you need.