Gists

In the current stage, Google Spreadsheet can use rich texts in cells. The rich texts can be also managed by Google Apps Script. But, I thought that creating a script for editing the existing rich text in the cell might be a bit complicated. Because, for example, in the current stage, when the text of the rich text of a cell is changed using a script, all text styles are cleared. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

Overview

This is a GAS library for supporting editing RichText in Google Spreadsheet using Google Apps Script.

Description

There is RichTextApp in my published libraries. RichTextApp can be used mainly for converting RichText to Google Documents and vice versa. This library RichTextAssistant will support editing the rich text in Google Spreadsheets using Google Apps Script. Google Spreadsheet can use rich text as the cell value using Google Apps Script. But, I thought that when I created a script for editing the existing rich text in the cell, it might be a bit complicated. Because, for example, in the current stage, when the text of rich text of a cell is changed using a script, all text styles are cleared. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. From this situation, when a script for supporting editing the rich text in a cell is published, it will be useful for a lot of users. So, I created it and published it as “RichTextAssistant” of a Google Apps Script library.

Gists

This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.

I have already published “File Picker using Google Apps Script and Javascript without 3rd party”. In this post, jsTree is used.

Usage

1. Install Google Apps Script library.

In this script, “FilesApp” of my Google Apps Script library is used. So, please install it. You can see how to install it at here.

Gists

When a value is searched from the 1-dimensional array and a 2-dimensional array, after V8 runtime could be used, I use JSON object, Set object, and Map Object. But, I had never measured the process cost of this situation. In this post, I would like to introduce the process cost for searching a value using a JSON object, Set object, and Map object converted from the 1-dimensional array and 2-dimensional array.

Gists

This is a sample script for importing Microsoft Excel (XLSX) data to Google Spreadsheet using a custom function with Google Apps Script.

Usage

1. Install SheetJS library.

Please copy the script of the SheetJS library from https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js, and paste the script to the script editor of Google Spreadsheet, and save the script.

In this case, I would like to recommend the following flow.

Gists

Updated on July 8, 2023

In the current stage, unfortunately, the built-in methods of Google Apps Script cannot directly retrieve the values from the XLSX data. From this situation, I have created DocsServiceApp. When this Google Apps Script library is used, the values are directly retrieved from XLSX data by parsing XML data of XLSX data.

Here, as another approach, I would like to introduce a sample script for directly retrieving the values from XLSX data using SheetJS with Google Apps Script. In the current stage, Google Apps Script can run with V8 runtime. By this, it seems that SheetJS can be used.

Gists

In Google Spreadsheet, when a single quote is added to the top letter of the cell value, the cell is used as the text value. About detecting this, I have already reported in this post in my blog. In this post, I would like to introduce a sample script for removing the single quote at the top character of the cell value.

Gists

This is a sample script for splitting the binary data with search data using Google Apps Script.

Sample script

/**
 * Split byteArray by a search data.
 * @param {Array} baseData Input byteArray of base data.
 * @param {Array} searchData Input byteArray of search data using split.
 * @return {Array} An array including byteArray.
 */
function splitByteArrayBySearchData_(baseData, searchData) {
  if (!Array.isArray(baseData) || !Array.isArray(searchData)) {
    throw new Error("Please give byte array.");
  }
  const search = searchData.join("");
  const bLen = searchData.length;
  const res = [];
  let idx = 0;
  do {
    idx = baseData.findIndex(
      (_, i, a) => [...Array(bLen)].map((_, j) => a[j + i]).join("") == search
    );
    if (idx != -1) {
      res.push(baseData.splice(0, idx));
      baseData.splice(0, bLen);
    } else {
      res.push(baseData.splice(0));
    }
  } while (idx != -1);
  return res;
}

// Please run this function.
function main() {
  const sampleString = "abc123def123ghi123jkl";
  const splitValue = "123";

  const res1 = splitByteArrayBySearchData(
    ...[sampleString, splitValue].map((e) => Utilities.newBlob(e).getBytes())
  );
  const res2 = res1.map((e) => Utilities.newBlob(e).getDataAsString());

  console.log(res1); // [[97,98,99],[100,101,102],[103,104,105],[106,107,108]]
  console.log(res2); // ["abc","def","ghi","jkl"]
}

• When main() is run, the sample input values of "abc123def123ghi123jkl" is split by "123". And, [[97,98,99],[100,101,102],[103,104,105],[106,107,108]] is obtained. In this case, when [[97,98,99],[100,101,102],[103,104,105],[106,107,108]] is converted to the string for confirming the result value, it becomes ["abc","def","ghi","jkl"].

Gists

This is a sample script for exporting Google Docs files (Spreadsheets, Documents, and so on) in PDF format with batch requests using Google Apps Script.

I have published a report “Efficient File Management using Batch Requests with Google Apps Script”. In this report, I mentioned how to use the batch requests using Google Apps Script.

In this post, I would like to introduce the method for retrieving binary data using this method.

BatchRequest was updated to v1.2.1.

• v1.2.1 (March 8, 2023)

  1. An option of exportDataAsBlob was added to the request object to the method of EDo(). Ref When this option is used, the response values from the batch requests are returned as Blob. By this, for example, when you export Google Spreadsheet as PDF data using the batch requests, the PDF data can be retrieved as Blob.

Sample script using exportDataAsBlob

In this sample, the Spreadsheet and Document files are exported as PDF format using the batch requests. The exported PDF data is created as a PDF file to the root folder. When I answered this thread on Stackoverflow, when this option is added to this library, I thought that it might be useful for users.