Gists

This is a sample script for rearranging the columns on Google Spreadsheet using Google Apps Script.

Sample script

In this sample script, the columns are rearranged with an array including the rearranged column indexes.

function rearrangeColumns(sheet, ar) {
  var obj = ar.reduce(function(ar, e, i) {
    return ar.concat({ from: e + 1, to: i + 1 });
  }, []);
  obj.sort(function(a, b) {
    return a.to < b.to ? -1 : 1;
  });
  obj.forEach(function(o) {
    if (o.from != o.to) sheet.moveColumns(sheet.getRange(1, o.from), o.to);
    obj.forEach(function(e, i) {
      if (e.from < o.from) obj[i].from += 1;
    });
  });
}

// Please run this function.
function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rearrangedColumnIndexes = [4, 3, 1, 0, 2];
  rearrangeColumns(sheet, rearrangedColumnIndexes);
}

• rearrangedColumnIndexes is the indexes of rearranged columns.

Gists

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 “Benchmark: Loop for Array Processing using Google Apps Script”.² At February 7, 2020, the V8 runtime got to be able to be used in my account. By this, it is considered that it is possibly changed the result of benchmark without V8 ². So I measured about this. In this report, the process cost of “loop” for the array processing using GAS has been investigated with V8 runtime.

Gists

In my environment, at February 7, 2020, when I opened the script editor of Google Apps Script, the following notification could be seen.

By this, I could notice that finally, the V8 Runtime has already been added to Google Apps Script. I think that this will be also the great news for a lot of users including me.

Overview

DateFinder is a GAS library for searching the date objects from the cell range on the sheet in the Spreadsheet and retrieving the searched range as the RangeList object using Google Apps Script (GAS).

Description

There is the Class TextFinder for searching the text from cells of the Spreadsheet using the Google Apps Script. But in this case, the date object in the cell is used as the string. Namely, the values for searching are used as the same with the values retrieved by getDisplayValues(). So for example, when there are the date objects in the cells with the various formats, the date cannot be searched by the Class TextFinder. So I created this library. When this library is used, the date objects in the cells can be retrieved by the date object and/or the range between 2 dates.

• v1.2.5 (January 29, 2020)

  1. An option for selecting whether the top directory is created was added.
     • $ goodls -u [URL] --notcreatetopdirectory or $ goodls -u [URL] -ntd
     • When this option is NOT used (default situation), when a folder including sub-folders is downloaded, the top folder which is downloaded is created as the top directory under the working directory. When this option is used, the top directory is not created and all files and sub-folders under the top folder are downloaded under the working directory.
     • This feature request was implemented.

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

Overview

EncodeApp is a GAS library for retrieving the encoding set (charset) and doing URL encode with the specific encoding set using Google Apps Script (GAS).

You can see the detail information here https://github.com/tanaikech/EncodeApp

Gists

This is a sample script for achieving the URL encode with Shift-JIS using Google Apps Script.

Unfortunately, there are no methods for directly achieving above in the methods of Google Apps Script. So it is required to prepare it as the script. In order to use Shift-JIS of the character set at Google Apps Script, it is required to use it as the binary data. Because, when the value of Shift-JIS is retrieved as the string by Google Apps Script, the character set is automatically changed to UTF-8. Please be careful this.

Libraries of gdoctableapp for golang, Node.js and python were updated to v1.1.0

• go-gdoctableapp

  • v1.1.0 (January 22, 2020)
    1. 2 new methods were added. From this version, the texts can be replaced by images. The direct link and local file can be used as the image.

• node-gdoctableapp

  • v1.1.0 (January 22, 2020)
    1. New method was added. From this version, the texts can be replaced by images. The direct link and local file can be used as the image.

• gdoctableapppy

Libraries of gdoctableapp for golang, Node.js and python were updated to v1.0.5

• go-gdoctableapp
• node-gdoctableapp
• gdoctableapppy

Update History

• v1.0.5 (January 21, 2020)

  • When the inline objects and tables are put in the table. An error occurred. This bug was removed by this update.

• I got the pull request at here.

Gists

Overview

This is a sample script for deleting the last empty page which has only one paragraph including no values which is "" in the Google Document using Google Apps Script.

Description

As a sample situation, it supposes that there is the following Google Document.

In this sample, the last page of Google Document has only one paragraph including no values which is "". When I try to delete only last page without modifying the previous page, I noticed that this cannot be achieved with only Google Document service. For example, it try to delete the paragraph of the last page using the following scripts,