Creating Spreadsheet with Custom Header and Footer using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

In order to print and export as PDF file, this is a sample script for converting Spreadsheet to Spreadsheet which has the custom header and footer.

In this sample script, DocsServiceApp, which is Google Apps Script library, is used. And, in this case, the Spreadsheet with the custom header and footer is created as new Spreadsheet.

Before you use this script, please install DocsServiceApp and enable Drive API at Advanced Google services.

Sample script:

Please put this script to the container-bound script of Spreadsheet you want to use.

function myFunction() {
  // Source Spreadsheet.
  const srcSS = SpreadsheetApp.getActiveSpreadsheet();
  const copiedSheets = srcSS.getSheets().map((s) => {
    const srcName = s.getSheetName();
    const cs = s.copyTo(srcSS).setName("temp_" + srcName);
    const cr = cs.getDataRange();
    cr.copyTo(cr, { contentsOnly: true });
    return cs;
  });

  // Destination Spreadsheet as temporal.
  const object = {
    title: "newSpreadsheet",
    header: { l: "left header", c: "center header", r: "right header" },
    footer: { l: "left footer", c: "center footer", r: "right footer" },
  };
  const dstSpreadsheetId = DocsServiceApp.createNewSpreadsheetWithCustomHeaderFooter(
    object
  );
  const dstSS = SpreadsheetApp.openById(dstSpreadsheetId);
  const topSheet = dstSS.getSheets()[0].setName(Utilities.getUuid());
  copiedSheets.forEach((s) => {
    const srcName = s.getSheetName().slice(5);
    const src = s.copyTo(dstSS);
    const dst = topSheet.copyTo(dstSS).setName(srcName);
    src.getDataRange().copyTo(dst.getDataRange());
    srcSS.deleteSheet(s);
    dstSS.deleteSheet(src);
  });
  dstSS.deleteSheet(dstSS.getSheets()[0]);
}
  • When you run this script, new Spreadsheet is created to the root folder. And when you open the created Spreadsheet, you can see the custom header and footer. Ref

IMPORTANT

  • When you use this sample script, I would like to recommend to use a sample Spreadsheet.
  • In the current stage, the custom header and footer cannot be inserted using Spreadsheet service and Sheets API. And it seems that when Google Spreadsheet with the custom header and footer is exported as a PDF data, unfortunately, the custom header and footer cannot be included. When the Spreadsheet is manually exported as a PDF file, the custom header and footer can be included. When sheetnames=true&printtitle=true is added to the query parameter, these can be included in the PDF data. So I thought that in the current stage, the PDF data including the custom header and footer might not be able to be created using a script.

 Share!