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.

Gists

This is a sample script for converting various images to PNG Format and JPEG format using Google Apps Script.

The flow of this sample script is as follows.

1. Convert the file to PNG format by the thumbnail link.
2. Convert PNG format to JPEG format if outputFormat is “JPEG”.
3. Create the image data in the JPEG format in the root folder as a file.

Sample script

Please set your file ID and output format.

Gists

This is a sample script for copying a Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script.

When you want to copy a Google Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script, this could be achieved by using “get” and “create” methods of Sheets API before. The sample script is as follows.

Gists

This is a sample script for notifying the new release of Google APIs and Google Apps Script with an email using Google Apps Script.

Recently, I published a sample script of “Retrieving Release Notes of Google Apps Script and Google APIs from RSS using Google Apps Script”. After this was published, I got an email that it wants to automatically notice the new release of Google APIs and Google Apps Script with an email. From this, I prepared a sample script as follows.

Gists

This is a sample script for managing the row height and the column width of a table on Google Slides using Google Apps Script.

In the current stage, Google Slides service (SlidesApp) cannot manage the row height and the column width of the table on Google Slides, while the table width and height can be managed. But, fortunately, when Google Slides API is used, this can be achieved.

In this post, I would like to introduce a sample script for managing the row height and the column width of a table on Google Slides using Google Apps Script.

Gists

This is a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script.

Description

A1Notation is used in the cells on Google Spreadsheet.

As the 1st sample, it supposes that a formula of =A1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =A2 and =B1, respectively. This is the relative reference.

Gists

This is a sample script for comparing the file contents of files on Google Drive using Google Apps Script.

Sample script

Before you use this script, please enable Drive API at Advanced Google services. And also, please set the file IDs you want to check whether the file contents of the files are the same.

function checkFiles_(f, checks = ["md5Checksum", "sha1Checksum", "sha256Checksum"]) {
  files = f.map(id => DriveApp.getFileById(id));
  const fields = [...checks, "id"].join(",");
  const o = files.reduce((o, f) => {
    const mimeType = f.getMimeType();
    if (["FOLDR", "SHORTCUT"].some(m => mimeType == MimeType[m])) {
      throw new Error("Folders cannot be checked.");
    }
    let obj;
    if (mimeType.includes("application/vnd.google-apps")) {
      const name = f.getName();
      f.setName("temp");
      Utilities.sleep(2000);
      obj = Drive.Files.insert({ title: "temp", mimeType: MimeType.PDF }, f.getBlob(), { supportsAllDrives: true, fields });
      f.setName(name);
      Drive.Files.remove(obj.id); // If an error occurs, please use DriveApp.getFileById(obj.id).setTrashed(true);
    } else {
      obj = Drive.Files.get(f.getId(), { supportsAllDrives: true, fields });
    }
    checks.forEach(e => o[e] = o[e] ? [...o[e], obj[e]] : [obj[e]]);
    return o;
  }, {});
  return Object.values(o).every(e => [...new Set(e)].length == 1);
}

// Please run this function.
function main() {
  const file1 = "###fileId1###"; // Please set your file ID of file 1.
  const file2 = "###fileId2###"; // Please set your file ID of file 2.

  const check = checkFiles_([file1, file2]);
  const res = `"${file1}" and "${file2}" are${
    check ? " " : " not "
  }the same data.`;
  console.log(res);
}

• When this script is run, when the file contents of your inputted files are the same, true is returned.