Gists

This is a sample script for retrieving the last coordinate of the specific row and column. When the methods of getLastRow() and getLastColumn() of Class Range for Spreadsheet are used, the last coordinates of the vertical and horizontal data range can be retrieved. When users want to retrieve the last coordinates of each row and column, there are no methods. So I created this script. I think that there are several scripts for this situation. So please think of this as one of them. If this was useful for you, I’m glad.

This is a script for sending an email when users got comments at Stackoverflow. I had wished I could get an email when I got a comment at Stackoverflow. Although I investigated about this, it seems that I can get the email every 3 hours. For this situation, I wished I could get it much earlier. I thought that this can be achieved using GAS and Stack Exchange API. So I created this script. The features of this script are as follows.

Gists

Updated on February 5, 2024

This is for adding the query parameters to the URL. These scripts can be also used for Javascript. When I created an endpoint with some query parameters, I had used the scripts of various patterns every time. Today, I prepared this sample script to unify them. If this is also useful for you, I’m glad.

Sample script (With V8 runtime):

String.prototype.addQuery = function (obj) {
  return (this == "" ? "" : `${this}?`) + Object.entries(obj).flatMap(([k, v]) => Array.isArray(v) ? v.map(e => `${k}=${encodeURIComponent(e)}`) : `${k}=${encodeURIComponent(v)}`).join("&");
}


function myFunction1() {
  const url = "https://sampleUrl";
  const query = {
    query1: ["value1A", "value1B", "value1C"],
    query2: "value2A, value2B",
    query3: "value3A/value3B",
  };
  const endpoint = url.addQuery(query);
  console.log(endpoint); // https://sampleUrl?query1=value1A&query1=value1B&query1=value1C&query2=value2A%2C%20value2B&query3=value3A%2Fvalue3B
}

// In this case, only the query parameter is exported. This value can be used for requesting with Form data.
function myFunction2() {
  const url = "";
  const query = {
    query1: ["value1A", "value1B", "value1C"],
    query2: "value2A, value2B",
    query3: "value3A/value3B",
  };
  const endpoint = url.addQuery(query);
  console.log(endpoint); // query1=value1A&query1=value1B&query1=value1C&query2=value2A%2C%20value2B&query3=value3A%2Fvalue3B
}

• https://jsfiddle.net/f92arp5m/

Sample script (Without V8 runtime):

String.prototype.addQuery = function (obj) {
  return this + Object.keys(obj).reduce(function (p, e, i) {
    return p + (i == 0 ? "?" : "&") +
      (Array.isArray(obj[e]) ? obj[e].reduce(function (str, f, j) {
        return str + e + "=" + encodeURIComponent(f) + (j != obj[e].length - 1 ? "&" : "")
      }, "") : e + "=" + encodeURIComponent(obj[e]));
  }, "");
}


function myFunction() {
  var url = "https://sampleUrl";
  var query = {
    query1: ["value1A", "value1B", "value1C"],
    query2: "value2A, value2B",
    query3: "value3A/value3B",
  };
  var endpoint = url.addQuery(query);
  Logger.log(endpoint);
}

• https://jsfiddle.net/jka8u3wx/

Result:

Both sample scripts return the following URL including the query parameters.

Gists

Benchmark: Conditional Branch using Google Apps Script

Introduction

Please be careful! This result can be only used for Google Apps Script.

There are a limit executing time for Google Apps Script (GAS). That is 6 minutes.¹ So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs. Recently, I have already published some reports about the process cost using GAS.^2-6 From these reports, it has found that GAS shows much different process cost from other languages. So it is important to investigate the process cost for various scenes. In this report, the process cost of “conditional branch” using GAS has been investigated.

ManifestsApp was updated to v1.0.3.

• v1.0.3 (July 11, 2018)

  • By Google’s update, “sheets” was added to manifests for installing the configuration of Macro. By this, this library was updated.
    • You can see the added methods (getSheets(), setSheets()) at Usage.
    • If you set “sheets”, please put the value of “sheets” as the resource like below sample.
      • {"macros": [{"menuName": "QuickRowSum", "functionName": "calculateRowSum"}]}
      • Don’t put {"sheets": {"macros": [{"menuName": "QuickRowSum", "functionName": "calculateRowSum"}]}}

You can check this at https://github.com/tanaikech/ManifestsApp.

Gists

Introduction

Here I would like to introduce about the limitation of images for inserting to Spreadsheet using Google Apps Script (GAS). When you want to insert the images to Spreadsheet using GAS, insertImage() of class Sheet is usually used for this situation. At this time, an error sometimes occurs. This indicates that there is the limitation for inserting images to Spreadsheet. So I investigated the limitation.

As a result, it was found that the limitation depends on the image area (pixels^2) rather than the file size of it. The maximum area of image which can be inserted was 1,048,576 pixels^2.

Here, CLI tools, libraries, Add-ons, Reports, Benchmarks and Sample Scripts for taking advantage of Google Apps Script which are publishing in my blog, Gists and GitHub are summarized. If these are useful for you, I’m glad.

You can check this at https://github.com/tanaikech/taking-advantage-of-google-apps-script.

Gists

There are no prepared methods for retrieving the number of lines in the Google Document. So I thought this workaround. If the end of each line can be detected, the number of lines can be retrieved. So I tried to add the end markers of each line using OCR.

At Google Documents, when a sentence is over the page width, the sentence automatically has the line break. But the line break has no \r\n or \n. When users give the line break by the enter key, the line break has \r\n or \n. By this, the text data retrieved from the document has only the line breaks which were given by users. I thought that OCR may be able to be used for this situation. The flow is as follows.

Gists

Benchmark: Search for Array Processing using Google Apps Script

Introduction

Please be careful! This result can be only used for Google Apps Script.

There are a limit executing time for Google Apps Script (GAS). That is 6 minutes.¹ So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs. Recently, I have reported about the process cost of the loop for the array processing.² Also I have reported “Improved Algorithms for Summation of Array Elements” as a method for reducing the process cost.³ From these reports, it has found that GAS shows much different process cost from other languages. So it is important to investigate the process cost for various scenes. In this report, the process cost of “searching strings in an array” for the array processing using GAS has been investigated.

Gists

Overview

This is a sample script which can achieve the resumable conversion from the large CSV-file to several spreadsheets by splitting the CSV file using Google Apps Script (GAS).

Description

Is there a situation that you want to convert a CSV file with the large size (> 50 MB) to Spreadsheet? When such large CSV file is converted to Spreadsheet, you will experience the error. The reason is the size and/or also it may be due to the total cells (> 2,000,000 cells) of CSV file. In this case, you will think that when the file is split, each file can be converted to Spreadsheet. But the blob which can be used at GAS is less than 50 MB (52,428,800 bytes). Ref1 So when you have a CSV file with more than 50 MB, it is not possible to split it. In this case, you can split such large file by files.get of Drive API. files.get of Drive API has a function of Partial download. Ref2 Using this, the file can be split. By the way, in my environment, when a CSV file with the size of 100 MB is used for this sample script, when the file is split by 10 MB, about 65 seconds was required to convert a chunk to Spreadsheet. In this case, when the CSV file is completely converted, it is considered that it will be over the limitation time (6 min) for executing GAS. So in this post, I would like to introduce a sample script which can achieve the resumable conversion from the large CSV-file to several spreadsheets.