I updated the report of “Benchmark: Loop for Array Processing using Google Apps Script”. In this update, “reduce” was added. If this report is useful for your situation, I’m glad.

You can check this at https://gist.github.com/tanaikech/848aeafaac1ec676900bb78e3ce220b6.

Overview

This is a Google Apps Script for notifying with email when Netatmo was down.

Description

I’m measuring the surrounding environment using Netatmo. There were little that my Netatmo was down so far. But recently, my Netatmo is sometimes down. It is considered that the reason is due to the thermal runaway, because the recent Japan is very hot. When Netatmo was down, users can know it by logging in using the browser and/or running the mobile application. But I couldn’t find the applications and methods for automatically noticing that the Netatmo was down. So I thought that this can be achieved using Netatmo API and Google Apps Script. And I created this. If this is also useful for other Netatmo’s users, I’m glad.

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.