Gists

This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script.

PDF can have the PDF Form for inputting the values in the PDF by the user. Ref Recently, I had a situation that required me to retrieve and put the values to the PDF Form using Google Apps Script. In order to achieve this, I created a Class object with Google Apps Script. That is as follows.

• v1.2.2 (July 27, 2023)

  Checked the array of to, cc, and bcc for the sendEmails method.

GitHub of OnedriveApp

Gists

This is a sample script for changing the order of pages in a PDF file using Google Apps Script.

Sample script

Before you run this script, please set the variables in the function main.

/**
 * ### Description
 * Changing order of pages in a PDF file.
 *
 * @param {Object} fileId is file ID of PDF file. newOrderOfpages is new order of pages. About "ignoreSkippedPages", if this is false, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has 5 pages of 3, 2, 1, 4, 5. If this is true, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has only 2 pages of 3 and 2.
 * @return {void}
 */
async function changeOrderOfPDFPages_({
  fileId,
  newOrderOfpages,
  ignoreSkippedPages,
}) {
  // Load pdf-lib
  const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Load pdf-lib
  const setTimeout = function (f, t) {
    Utilities.sleep(t);
    return f();
  };

  const blob = DriveApp.getFileById(fileId).getBlob();
  const pdfData = await PDFLib.PDFDocument.load(
    new Uint8Array(blob.getBytes())
  );
  const numberOfPages = pdfData.getPageCount();
  const maxPage = Math.max(...newOrderOfpages);
  if (numberOfPages < maxPage || numberOfPages < newOrderOfpages.length) {
    throw new Error(
      "Maximum page in the order of pages is over than the maximum page of the original PDF file."
    );
  }
  let skippedPages = [];
  if (!ignoreSkippedPages && numberOfPages > newOrderOfpages.length) {
    skippedPages = [...Array(numberOfPages)]
      .map((_, i) => i + 1)
      .filter((e) => !newOrderOfpages.includes(e));
  }
  const pdfDoc = await PDFLib.PDFDocument.create();
  const pages = await pdfDoc.copyPages(
    pdfData,
    [...Array(numberOfPages)].map((_, i) => i)
  );
  [...newOrderOfpages, ...skippedPages].forEach((e) =>
    pdfDoc.addPage(pages[e - 1])
  );
  const bytes = await pdfDoc.save();
  return Utilities.newBlob(
    [...new Int8Array(bytes)],
    MimeType.PDF,
    "sample.pdf"
  );
}

function main() {
  const fileId = "###"; // Please set a file ID of your a PDF file or a file ID of Google Docs files (Document, Spreadsheet, Slide).
  const newOrderOfpages = [3, 1, 2, 5, 4]; // Please set new order of the pages in a PDF file. In this sample, the order of pages of the original PDF file is changed to 3, 1, 2, 5, 4.
  const ignoreSkippedPages = false; // If this is false, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has 5 pages of 3, 2, 1, 4, 5. If this is true, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has only 2 pages of 3 and 2.

  changeOrderOfPDFPages_({ fileId, newOrderOfpages, ignoreSkippedPages }).then(
    (blob) => {
      DriveApp.createFile(blob.setName("sample.pdf"));
    }
  );
}

When this script is run, a new PDF file is created with the new order of pages.

• v1.2.1 (July 26, 2023)

  A bug of “Send Email messages” was removed.

GitHub of OnedriveApp

Gists

Here, I would like to introduce a report for understanding the flow of the request to Web Apps created by Google Apps Script. There might be a case that various applications using the Web Apps are created and the Web Apps are used as the webhook. In that case, it is considered that when you have understood the flow of requests to the Web Apps, your goal might be able to be smoothly achieved. In this report, I would like to introduce the information about it.

Gists

This is a sample script for managing the metadata of PDF data using Google Apps Script.

There might be a case in that you want to retrieve and update the metadata of PDF data using Google Apps Script. In this post, I would like to introduce achieving this.

Class ManagePdfMetadata

This is a Class ManagePdfMetadata. This Class is used for managing the metadata of PDF files using Google Apps Script. And, in this Class, a Javascript library of pdf-lib is used for managing the PDF metadata. This Javascript library is loaded in this Class.

Gists

This is a sample script for checking the overwrapped cells of multiple ranges on Google Spreadsheet using Google Apps Script.

When applications are developed, there might be a case that it is required to confirm whether 2 ranges on Google Spreadsheet are overwrapped. In this post, I would like to introduce a sample script for achieving this.

Method: getOverwrappedCells

The following script is a method of getOverwrappedCells. This is the main script of this post. This method returns the information about the overwrapped cells by inputting an array including the Class Range object. For example, as the default response, when each cell in “range1” and “range2” is overwrapped, true is returned. When { responseType: "list" } is used, the cell coordinates of the overwrapped cells are returned as an array.

Gists

This is a sample script for automatically refreshing the basic filter on Google Spreadsheet using Google Apps Script.

Description

A sample situation is as follows.

In this sample, the basic filter is set to columns “B” and “D”.

• Column “B”: When the checkbox is checked, the row is hidden.
• Column “D”: When the cell value is multiples of 3, the row is hidden. In this case, the custom function =MOD(E2,3)<>0 is used.

For example, under the condition that the basic filter is set to columns “B” and “D”, even when a checkbox of “B3” is checked, unfortunately, the basic filter is not automatically refreshed. In this case, it is required to manually refresh it.

Overview

This is a Google Apps Script library for efficiently managing the time-driven triggers for executing Google Apps Script using Google Apps Script.

Description

Google Apps Script can execute with not only the manual operation but also several triggers. The time-driven trigger is one of them, and this is one of a lot of important functions. When the time-driven trigger is used, Google Apps Script can be automatically executed at the time you set without launching the user’s PC.

Gists

This is a sample script for exporting the specific pages from a PDF as a new PDF using Google Apps Script.

In this sample script, pdf-lib is used. In the current stage, it seems that this Javascript can be directly used with Google Apps Script.

Sample script

async function myFunction() {
  // Retrieve PDF data.
  const fileId = "###"; // Please set a file ID of your a PDF file or a file ID of Google Docs files (Document, Spreadsheet, Slide).
  const pageNumbers = [2, 4]; // In this sample, 2 and 4 pages are exported as a PDF.

  const blob = DriveApp.getFileById(fileId).getBlob();

  // Merge PDFs.
  const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Load pdf-lib
  const setTimeout = function (f, t) {
    Utilities.sleep(t);
    return f();
  };
  const pdfDoc = await PDFLib.PDFDocument.create();
  const pdfData = await PDFLib.PDFDocument.load(
    new Uint8Array(blob.getBytes())
  );
  const pages = await pdfDoc.copyPages(
    pdfData,
    [...Array(pdfData.getPageCount())].map((_, i) => i)
  );
  pages.forEach((page, i) => {
    if (pageNumbers.includes(i + 1)) {
      pdfDoc.addPage(page);
    }
  });
  const bytes = await pdfDoc.save();

  // Create a PDF file.
  DriveApp.createFile(
    Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, "sample.pdf")
  );
}

• When this script is run, the specific pages (In this sample, 2 and 4 pages.) in pageNumbers of a PDF file of fileId are exported as a new PDF file.