This is a sample script for adding header and footer to PDF using Google Apps Script.
In the current stage, when Google Spreadsheet is manually exported as a PDF file at “Print settings” on the UI of Spreadsheet, the custom header and footer can be added as shown in the following image.
But, unfortunately, in the current stage, this cannot be directly achieved by Google Apps Script. So, I created this sample script. This sample script uses pdf-lib of Javasscript library.
The usage of this script is as follows.
Usage
1. Prepare Google Spreadsheet
In order to test this script, please create a sample Google Spreadsheet.
2. Sample script
Please copy and paste the following script to the script editor of Google Spreadsheet.
/**
* ### Description
* Insert header and/or footer into PDF blob.
*
* @param {Object} pdfBlob Blob of PDF data for embedding objects.
* @param {Object} object Object including the values for inserting header and footer.
* @return {promise} PDF Blob.
*/
async function insertHeaderFooter_(pdfBlob, object) {
// Load pdf-lib
const cdnUrl = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
eval(
UrlFetchApp.fetch(cdnUrl)
.getContentText()
.replace(
/setTimeout\(.*?,.*?(\d*?)\)/g,
"Utilities.sleep($1);return t();"
)
);
if (!object || typeof object != "object") {
throw new Error("Please an object for embeddig the objects.");
}
const { header, footer } = object;
const pdfDoc = await PDFLib.PDFDocument.create();
const form = pdfDoc.getForm();
const pdfData = await PDFLib.PDFDocument.load(
new Uint8Array(pdfBlob.getBytes())
);
const numberOfPages = pdfData.getPageCount();
const pages = await pdfDoc.copyPages(
pdfData,
[...Array(numberOfPages)].map((_, i) => i)
);
const headers = header
? Object.entries(header).map(([k, v]) => [`header.${k}`, v])
: [];
const footers = footer
? Object.entries(footer).map(([k, v]) => [`footer.${k}`, v])
: [];
const sortOrder = ["LEFT", "CENTER", "RIGHT"];
[footers, headers].forEach((f, _, x) =>
f.sort((a, b) => {
const i1 = sortOrder.findIndex((e) => a[0].includes(e.toLowerCase()));
const i2 = sortOrder.findIndex((e) => b[0].includes(e.toLowerCase()));
const vlen = x.length;
return (i1 > -1 ? i1 : vlen) - (i2 > -1 ? i2 : vlen);
})
);
const alignObj = { center: "Center", left: "Left", right: "Right" };
for (let i = 0; i < numberOfPages; i++) {
const pageNumber = i + 1;
const page = pdfDoc.addPage(pages[i]);
const pageHeight = page.getHeight();
const pageWidth = page.getWidth();
if (headers.length > 0) {
const sizeWidthHead = pageWidth / headers.length;
for (let j = 0; j < headers.length; j++) {
const [k, v] = headers[j];
const o = {
borderWidth: v.borderWidth || 0,
x: j * sizeWidthHead,
y: pageHeight - ((v.yOffset || 0) + (v.height || 20)),
width: sizeWidthHead,
height: v.height || 30,
...v,
};
addHeaderFooterFields_({ page, form, pageNumber, k, v, o, alignObj });
}
}
if (footers.length > 0) {
const sizeWidthFoot = pageWidth / footers.length;
for (let j = 0; j < footers.length; j++) {
const [k, v] = footers[j];
const o = {
borderWidth: v.borderWidth || 0,
x: j * sizeWidthFoot,
y: v.yOffset || 0,
width: sizeWidthFoot,
height: v.height || 30,
...v,
};
addHeaderFooterFields_({ page, form, pageNumber, k, v, o, alignObj });
}
}
}
const bytes = await pdfDoc.save();
return Utilities.newBlob(
[...new Int8Array(bytes)],
MimeType.PDF,
`new_${pdfBlob.getName()}`
);
}
function addHeaderFooterFields_(object) {
const { page, form, pageNumber, k, v, o, alignObj } = object;
const fieldName = `${k}.${pageNumber}`;
const textBox = form.createTextField(fieldName);
if (v.text) {
textBox.setText(v.text);
}
if (v.alignment) {
textBox.setAlignment(
PDFLib.TextAlignment[alignObj[v.alignment.toLowerCase()]]
);
}
textBox.disableScrolling();
textBox.disableMultiline();
textBox.enableReadOnly();
["x", "y", "width", "text"].forEach((e) => delete v[e]);
textBox.addToPage(page, o);
}
// Please run this function.
function main() {
const object = {
header: {
left: { height: 30, alignment: "Center", text: "sample text h1" },
center: { height: 30, alignment: "Center", text: "sample text h2" },
right: { height: 30, alignment: "Center", text: "sample text h3" },
},
footer: {
left: { height: 30, alignment: "Center", text: "sample text f1" },
center: { height: 30, alignment: "Center", text: "sample text f2" },
right: { height: 30, alignment: "Center", text: "sample text f3" },
},
};
// Ref: https://gist.github.com/tanaikech/70503e0ea6998083fcb05c6d2a857107
String.prototype.addQuery = function (obj) {
return (
this +
"?" +
Object.entries(obj)
.flatMap(([k, v]) =>
Array.isArray(v)
? v.map((e) => `${k}=${encodeURIComponent(e)}`)
: `${k}=${encodeURIComponent(v)}`
)
.join("&")
);
};
const ss = SpreadsheetApp.getActiveSpreadsheet();
const query = {
format: "pdf",
size: "letter",
portrait: false,
horizontal_alignment: "CENTER",
vertical_alignment: "MIDDLE",
};
const url =
`https://docs.google.com/spreadsheets/d/${ss.getId()}/export`.addQuery(
query
);
const blob = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
}).getBlob();
insertHeaderFooter_(blob, object)
.then((blob) => DriveApp.createFile(blob))
.catch((err) => console.log(err));
}
3. Testing
When this script main() is run, the following result is obtained. A PDF file is created in the root folder. You can see the header and footer are added.
As another sample, for example, when you want to add only a header with a center value, please use the following object.
const object = {
header: {
center: { height: 30, alignment: "Center", text: "sample text h2" },
},
};
When you want to put the header slightly below, please include yOffset like yOffset: 20 in object.
Use custom font
When you want to add the header and footer using the custom font, you can see the document of the method of “insertHeaderFooter” of PDFApp.