News

• At October 11, 2019, I published a Javascript library to to run the resumable upload for Google Drive. When this is used, the large file can be uploaded. You can also use this js library.

  • Resumable Upload of Multiple Files with Asynchronous Process for Google Drive

Overview

This is a sample script for uploading files with large size (> 50 MB) at Web Apps using Google Apps Script (GAS). The resumable upload method is used for uploading files. This script can be also applied to the script using gapi of javascript.

goodls was updated to v.1.0.2

• v1.0.2 (May 10, 2018)

  1. Files with large size has gotten to be able to be used.
     • In order to download files with large size (several gigabytes), files are saved by chunks.

The detail information and how to get this are https://github.com/tanaikech/goodls.

Gists

This sample script retrieves the difference elements between 2 dimensional arrays using Google Apps Script. In Google Apps Script, 2 dimensional arrays are often used at Google Docs and Google APIs. And from my recent report, it has already found that the process cost of filter() is the lowest in the other loop methods. So I use the script like this.

var ar1 = [["a1", "b1", "c1"], ["a2", "b2", "c2"], ["a3", "b3", "c3"], ["a4", "b4", "c4"], ["a5", "b5", "c5"]];
var ar2 = [["a2", "b2", "c2"], ["a5", "b5", "c5"], ["a1", "b2", "c3"]];

var res = ar1.filter(function(e) {return ar2.filter(function(f) {return e.toString() == f.toString()}).length == 0});

Logger.log(res); // [["a1","b1","c1"],["a3","b3","c3"],["a4","b4","c4"]]

For above script, when it changes from == 0 to > 0, the duplication elements can be retrieved as follows.

Overview

This is a library for running Batch Requests using Google Apps Script (GAS).

Description

When users use Google’s APIs, one quota is used for one API call. When the batch request is used, several APIs can be called by one quota, although there are some limitations in the batch request. For example, in GAS, Drive API can be used be DriveApp. In this case, the quota is not used for using Drive API. (When Drive of Advanced Google Services is used, the quota is used.) But this is Drive API v2. If users want to use Drive API v3, it is required to directly request each endpoint of Drive API v3. The batch request is much useful for this situation. However, it is a bit difficult for users to use the batch request. Because the batch request is requested by multipart/mixed. I thought that the script may become a bit complicated, because of the request of multipart/mixed using UrlFetchApp. And although I had been looking for the libraries for the batch request, I couldn’t find them. So I created this.

Gists

This is a sample script which works the same action with the CLEAN method of VBA. The CLEAN method of VBA removes the characters of 0-31, 127, 129, 141, 143, 144, 157. Although I had looked for such method for Google Apps Script, I couldn’t find it. So I created this. If this is useful for you, I’m glad.

function cleanForGAS(str) {
  if (typeof str == "string") {
    var escaped = escape(str.trim());
    for (var i = 0; i <= 31; i++) {
      var s = i.toString(16);
      var re = new RegExp("%" + (s.length == 1 ? "0" + s : s).toUpperCase(), "g");
      escaped = escaped.replace(re, "");
    }
    var remove = ["%7F", "%81", "%8D", "%8F", "%90", "%9D"];
    remove.forEach(function(e) {
      var re = new RegExp(e, "g");
      escaped = escaped.replace(re, "");
    });
    return unescape(escaped).trim();
  } else {
    return str;
  }
}

function main() {
    var res = cleanForGAS(str);
}

Overview

This is a report to take advantage of Web Apps with Google Apps Script (GAS).

Description

There is Web Apps as one of applications using Google Apps Script (GAS). I sometimes use this Web Apps. But I have only a little the information for the specification of Web Apps. So in order to take more advantage of Web Apps, I investigated and summarized about this. The aim of this report is to become one of the basic information for creating various applications using Web Apps with GAS.

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.