使用Google Apps Script将Google Sheet导出为PDF，并添加自定义页眉或页脚

Question

我想知道如何在使用Google Apps Script从Google表格导出的PDF中添加自定义页眉或页脚。我想在页面中央添加一个页脚，上面写着“我的公司专有和机密信息”。

这是正常情况下从表格导出PDF时可用的功能（即文件»下载为»PDF»页眉和页脚»编辑自定义字段），但我不知道如何使用URL参数复制它。我还尝试过设置'&sheetnames=true&printtitle=true'，但这会将工作表名称和电子表格名称放在页眉中而非页脚中。

是否有其他可以控制这些自定义字段的URL参数？

  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

目标页脚：

- Colin Fennern

---

我还没有看到那个选项，目前我找到的是这些：https://gist.github.com/andrewroberts/c37d45619d5661cab078be2a3f2fd2bb - Andrew Roberts

Answer 1

我认为解决方法如下：

1. 创建一个新文档
2. 插入您的页眉
3. 复制您电子表格的内容
4. 插入您的页脚
5. 将新文件导出为PDF
6. 删除新文件

- Dmitry Kostyuk

Answer 2

问题和解决方法：

很遗憾，在当前阶段，没有直接实现您目标的方法。但是，我想到了一个解决方法，即在导出的PDF中添加页眉和页脚，可能能够实现您的目标。在这个答案中，我想提出这个解决方法。该解决方法的流程如下：

1. 将电子表格导出为PDF。
2. 在导出的PDF中添加页眉和页脚。

为了实现这一点，我们使用了pdf-lib。示例脚本如下：

示例脚本：

/**
 * ### 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));
}

测试：

运行此脚本后，将在根文件夹中创建以下PDF文件。您可以看到页眉和页脚。

例如，当您只想添加页脚时，请使用以下的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" },
  }
}

注意：

• 在此示例脚本中，pdf-lib库被加载到脚本中。但是，在这种情况下，您也可以通过将从https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js检索到的脚本库复制并粘贴到Google Apps Script的脚本编辑器中来使用pdf-lib库。在这种情况下，加载它的过程成本可以降低。

参考资料：

• pdf-lib
• 使用Google Apps Script向导出的PDF添加页眉和页脚（作者：我）

- Tanaike