Gists

This is a sample script for Google Apps Script (GAS). This script retrieves all named ranges in Spreadsheet. The names and range of the retrieved named ranges are output as the keys and the values of JSON object, respectively. The sample output is {"name1": "Sheet1!A1:B2", "name2": "Sheet2!B1:C2",,,}. The name of named range has to be only one in the spreadsheet. This was used.

Sheets.Spreadsheets.get() of Sheets API can retrieve all named ranges. But the retrieved range is the grid range. So in this sample script, the grid range was converted to a1Notation. The main part of this sample script is here.

Overview

This is a library for running the concurrent processing using only native Google Apps Script (GAS).

Description

Have you ever thought about the concurrent processing using only native Google Apps Script (GAS)? So far, I had run the concurrent processing using golang, javascript and python. But the script cannot be used by the trigger event, because these are not native GAS. Recently, it was found that the fetchAll method added by the Google’s update at January 19, 2018 is worked by the asynchronous processing. By this, the concurrent processing using the native GAS got to be able to be achieved. This library makes users work the concurrent processing of functions using the fetchAll method and the execution API. This can drastically reduce the process cost in the script. And also this can be used under the trigger event. So it is considered that it will be useful for both the limit executing time of 6 minutes for GAS and the limit total executing time of 1 hour/day for the trigger event.

Gists

By Google’s update at January 19, 2018, fetchAll method was added to the UrlFetch service. When I saw the usage, I couldn’t find the detail information about the actual running state. So I investigated about it.

As the result, it was found that the fetchAll method is worked by the asynchronous processing. The returned data is reordered by the order of requests. By this, it was also found that if you want to retrieve the data from the several URL, the process cost of UrlFetchApp.fetchAll() is much lower than that of UrlFetchApp.fetch() using for loop.

Gists

Benchmark: Loop 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. I have already reported “Improved Algorithms for Summation of Array Elements” as a method for reducing the process cost.² In this report, the process cost of “loop” for the array processing using GAS has been investigated.

Gists

Introduction

There are event objects at Google Apps Script. Typically, users which use Spreadsheet often use onEdit(event). Here, I would like to introduce the process costs for the event objects using this onEdit(event).

When onEdit(event) is used for the spreadsheet, event of onEdit(event) has the following structure.

{
  "authMode": {},
  "range": {
    "columnStart": 1,
    "rowStart": 1,
    "rowEnd": 1,
    "columnEnd": 1
  },
  "source": {},
  "oldValue": "old sample text",
  "user": {
    "nickname": "sampleName",
    "email": "sample email"
  },
  "value": "sample text"
}

In this structure, for example, the range of active cell is "range": {"columnStart": 1, "rowStart": 1, "rowEnd": 1, "columnEnd": 1 }. Namely, it’s “A1”. Users can use the range of active cell using this event object. In this report, I have investigated the process cost for retrieving the range of active cell as a sample.

Overview

This is a script for converting strings from NFD (Normalization Form Decomposition) to NFC (Normalization Form Composition) using Google Apps Script.

Description

Here, I would like to introduce a script for the unicode normalization using Google Apps Script. There are the characters with ゙ which is the voiced dot and the characters with ゚ which is the semi-voiced dot in Japanese language. When these are used for some applications, there are 2 kinds of usages for the character. For example, when for は (\u306f) HA with the voiced dot, there are ば and ば. These unicodes are \u3070 and \u306f\u3099. Namely, there are the case which displayed 1 character as 2 characters. In most cases, the characters like \u3070 are used. This called NFC (Normalization Form Composition). But we sometimes meet the characters like \u306f\u3099. This called NFD (Normalization Form Decomposition). When the document including such characters which are displayed as 2 characters is converted to PDF file, each character is separated like は ゙. So users often want to convert the characters constructed by 2 characters to the single characters. Recently, String.prototype.normalize was added at ES2015. But ES2015 cannot be used at Google Apps Script yet. And although I had looked for the scripts like this for GAS, unfortunately, I couldn’t find. So I created this script.

Gists

When I launched Sublime Text, I noticed that the error occurred. The error is as follows.

ImportError: No module named 'yaml'

I confirmed that this error occurs when the plugin of Material Theme is read. And the error started to occur after Material Theme was updated, recently.

In this report, I would like to introduce the method for removing this error. The flow is as follows.

1. Download a file including library for yaml (PyYAML) from https://pypi.python.org/pypi/PyYAML
   • In my environment, I downloaded PyYAML-3.12.win-amd64-py3.5.exe.
2. Unzip the downloaded file.
   • You can see a directory of yaml.
3. Add the directory of yaml to python3.3.zip.
   • python3.3.zip is in the directory which installed Sublime Text.

By above flow, the error can be removed. If the error of ImportError occurs for other modules, you can try to do this method. I think that although my Sublime Text is Sublime Text3 build 3143 x64, this method may be able to be used for Sublime Text2.

RearrangeScripts was updated to v2.1.1

• v2.1.1 (Match 2, 2018)

  • Added a notification to turn on the switch for using Google Apps Script API.
    • This was reported to issuetracker. https://issuetracker.google.com/issues/74048155
    • Acknowledgements: Alan Wells

• Add-on
• GitHub

Overview

This is a CLI tool to retrieve data from a personal weather station of Netatmo.

Description

I have a personal weather station of Netatmo. I check the data of my local environment using it. In most case, I have used my browser to retrieve the data so far. About retrieving data using curl, I have created it before. Recently, I thought that I wanted to create this as a CLI too. So I created this. This tool can retrieve not only the data of own Netatmo, but also the data of specific area using Netatmo APIs. By this, I got to be able to retrieve easily the data of various places. This tool has the following features.

Gists

In the case appending values to cell by inserting rows, when sheets.spreadsheets.values.append is used, the values are appended to the next empty row of the last row. If you want to append values to between cells with values by inserting row, you can achieve it using sheets.spreadsheets.batchUpdate.

When you use this, please use your access token.

Endpoint :

POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate

Request body :

In this request body, it appends the data of “sample1, sample2, sample3” to “A1:A3” of the sheetId of “1234567890”. Before appends the data, it supposes that there are some values at “A1:A3”.