Issue and workaround:
Unfortunately, in the current stage, there are no methods for directly achieving your goal. But, I thought that as a workaround, when the header and footer are added to the exported PDF, your goal might be able to be achieved. In this answer, I would like to propose this workaround. The flow of this workaround is as follows.
- Export Spreadsheet as PDF.
- Add the header and footer to the exported PDF.
In order to achieve this, pdf-lib is used. The sample script is as follows.
Sample script:
/**
* ### 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) {
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 myFunction() {
const object = {
header: {
left: { height: 20, alignment: "Center", text: "sample text h1" },
center: { height: 20, alignment: "Center", text: "sample text h2" },
right: { height: 20, alignment: "Center", text: "sample text h3" },
},
footer: {
left: { height: 20, alignment: "Center", text: "sample text f1" },
center: { height: 20, alignment: "Center", text: "sample text f2" },
right: { height: 20, alignment: "Center", text: "sample text f3" },
},
}
const sourceSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = sourceSpreadSheet.getSheetByName("Sheet1");
// --- The below script is from your showing script.
var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadSheet.getId()
+ '/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
+ '&size=letter' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&fitw=true' // fit to page width, false for actual size
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenum=CENTER&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&gid=' + sourceSheet.getSheetId() // the sheet's Id
+ '&top_margin=0.45&left_margin=0.25&right_margin=0.25&bottom_margin=0.5'; // margins
// ---
const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
insertHeaderFooter_(blob, object)
.then(blob => DriveApp.createFile(blob))
.catch(err => console.log(err));
}
Testing:
When this script is run, the following PDF is created in the root folder. You can see the header and footer.
![enter image description here]()
For example, when you want to add only footer, please use the following object.
const object = {
footer: {
left: { height: 20, alignment: "Center", text: "sample text f1" },
center: { height: 20, alignment: "Center", text: "sample text f2" },
right: { height: 20, alignment: "Center", text: "sample text f3" },
}
}
Note:
- In this sample script, the pdf-lib library is loaded in the script. But, of course, in this case, you can also use the pdf-lib library by copying and pasting the script library retrieved from
https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js in the script editor of Google Apps Script. In this case, the process cost for loading it can be reduced.
References:
