sheetTabToPdfBlob(sheet, opts) exports one tab to a PDF blob via the spreadsheet export URL and that tab's gid. It authorizes with ScriptApp.getOAuthToken(). Use this when you want a weekly report PDF of a single sheet, not a one-row invoice template.
When to use
- Emailing or filing a tab as a PDF on a schedule
- A report tab you already format for print
- Any script that needs PDF bytes without opening the browser download dialog
How to use this snippet
/**
* Export a single tab to a PDF blob.
* opts: landscape, portrait (default true), fitToWidth (default true),
* gridlines (default false), margins: {top,bottom,left,right} in inches
* (or margin for all four, default 0.5), size (e.g. "letter"), filename.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {{landscape?: boolean, portrait?: boolean, fitToWidth?: boolean, gridlines?: boolean, margin?: number, margins?: {top?: number, bottom?: number, left?: number, right?: number}, size?: string, filename?: string}} [opts]
* @return {GoogleAppsScript.Base.Blob}
*/
function sheetTabToPdfBlob(sheet, opts) {
opts = opts || {};
var ss = sheet.getParent();
var portrait = opts.landscape ? false : (opts.portrait !== false);
var fitw = opts.fitToWidth !== false;
var gridlines = !!opts.gridlines;
var m = opts.margins || {};
var def = opts.margin != null ? opts.margin : 0.5;
var top = m.top != null ? m.top : def;
var bottom = m.bottom != null ? m.bottom : def;
var left = m.left != null ? m.left : def;
var right = m.right != null ? m.right : def;
var url = 'https://docs.google.com/spreadsheets/d/' + ss.getId()
+ '/export?format=pdf'
+ '&gid=' + sheet.getSheetId()
+ '&portrait=' + portrait
+ '&fitw=' + fitw
+ '&gridlines=' + gridlines
+ '&sheetnames=false&printtitle=false&pagenumbers=false&fzr=false'
+ '&top_margin=' + top
+ '&bottom_margin=' + bottom
+ '&left_margin=' + left
+ '&right_margin=' + right;
if (opts.size) url += '&size=' + encodeURIComponent(opts.size);
var resp = UrlFetchApp.fetch(url, {
headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
muteHttpExceptions: true
});
if (resp.getResponseCode() !== 200) {
throw new Error('sheetTabToPdfBlob: export failed (HTTP ' + resp.getResponseCode() + ')');
}
var name = opts.filename || (sheet.getName() + '.pdf');
return resp.getBlob().setName(name);
}
Example
function weeklyPdf() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Weekly');
var blob = sheetTabToPdfBlob(sheet, {
landscape: true,
fitToWidth: true,
gridlines: false,
margins: { top: 0.5, bottom: 0.5, left: 0.5, right: 0.5 },
filename: 'Weekly.pdf'
});
DriveApp.createFile(blob);
}
Notes
- The export URL includes
gid, so only that tab is in the PDF, not the whole workbook. - First run in the editor so the project gets Spreadsheet and
https://www.googleapis.com/auth/spreadsheets(or Drive) scopes; the token is only as powerful as the project already is. - Call
SpreadsheetApp.flush()first if a script just wrote the values the PDF should show. - For a PDF of one filled row filed into a client folder, see Generate a PDF from a sheet row and file it in Drive. This helper is for exporting a tab as it stands.
Happy Coding!
