Importing Microsoft Excel to Google Spreadsheet using Custom Function with Google Apps Script

Gists

Importing Microsoft Excel to Google Spreadsheet using Custom Function with Google Apps Script

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.

Directly Retrieving Values from XLSX data using SheetJS with Google Apps Script

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.

Split Binary Data with Search Data using Google Apps Script

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"].

Exporting Google Docs Files in PDF format with Batch Requests using Google Apps Script

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.

Updated: GAS Library - BatchRequest

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.

Resumable Download of File from Google Drive using Drive API with Python

Gists

This is a sample script for achieving the resumable download of a file from Google Drive using Dive API with Python.

There might be a case in that you want to achieve the resumable download of a file from Google Drive using Dive API with Python. For example, when a large file is downloaded, the downloading might be stopped in the middle of downloading. At that time, you might want to resume the download. In this post, I would like to introduce the sample script of python.

Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script

Gists

This is a sample script for retrieving the total file sizes in the specific folder of Google Drive using Google Apps Script.

There is a case where you want to retrieve the total file sizes in the specific folder of Google Drive using Google Apps Script. In this post, I would like to introduce a sample script for achieving this.

Sample script

Before you use this script, please enable Drive API at Advanced Google services. And please install FilesApp of a Google Apps Script library.

Using Until Expiration Time of Access Token Retrieved By googleapis for Python

Gists

When Google APIs are used with googleapis for Python, the client is obtained as follows.

creds = service_account.Credentials.from_service_account_file(service_account_credential_file, scopes=scopes)
service = build("drive", "v3", credentials=creds)

In this case, when the script is run, the access token is retrieved every time. But, the expiration time of the retrieved access token is 1 hour. Here, there might be the case that you want to use the access token until the expiration time. It is considered that effectively using the access token will lead to SDGs. In this post, I would like to introduce a sample script for using the access token until the expiration time.

Google OAuth Verification & Application Privacy Policy

Registered Application Name: Workspace & Gemini AI Orchestration Engine

Application Purpose & Core Functionality:

This web page serves as the official homepage and privacy compliance interface for the application "Workspace & Gemini AI Orchestration Engine". This specialized developer utility is designed to research, benchmark, and optimize advanced integrations between Google Workspace services, the Google Apps Script API, and Gemini AI models (via Google Vertex AI / Gemini API endpoints).

The application facilitates automated multi-agent scaffolding, programmatic script deployment, project resource management, and structural analysis of Google Apps Script projects. It allows developers and autonomous AI agents (operating via Model Context Protocol / MCP) to securely evaluate execution performance, implement high-performance batch requests, and test agent-to-agent (A2A) workflows within a controlled and structured environment.

Google User Data Policy Compliance Statements:

1. Data Access & Specific Usage

Our application explicitly requests access to specific Google user accounts through OAuth scopes required strictly for interacting with the Google Apps Script API and Google Workspace endpoints. This access is utilized solely to execute user-initiated or agent-orchestrated programmatic operations—such as creating, modifying, deploying, or benchmarking script projects and executing automated workflows. No background automated extraction occurs without explicit session initiation.

2. Data Storage & Zero-Retention Policy

Adhering to a strict Zero-Retention Model, this application does not store, log, or persist any personal data, OAuth tokens, script source codes, or Google account configurations on any external server, database, or persistent storage medium. All data processing and API responses are handled entirely in-memory or securely on the client side within the active session context, ensuring complete cryptographic transient isolation.

3. Data Sharing & Third-Party Non-Disclosure

We maintain absolute data privacy. No data accessed via Google OAuth scopes is shared, sold, rented, or transferred to third-party entities, advertising networks, or data brokers. All data transmissions are strictly point-to-point, encrypted in transit using industry-standard protocols, and limited entirely to the direct channel between the execution environment and Google's official API gateways.

For inquiries regarding this developer application, technical benchmarks, or verification compliance, please refer to the official documentation and repositories linked on this homepage (tanaikech.github.io).