Gists

This sample script is for measuring the execution time of built-in functions for Google Spreadsheet. Unfortunately, there are not measurement tools for retrieving the execution time of built-in functions. So I thought of about a workaround.

Flow :

1. Import a value to a cell. The value is anything good, because this is used as a trigger. Please do this by yourself.
   • Custom functions cannot use setValue(). So I used onEdit().
2. func1() imports a formula that you want to measure the execution time by the script launched by the trigger.
3. At func2(), after set the formula, the measurement is started. The confirmation when built-in function was completed is carried out using loop.
   • By measuring the cost per one call for getValue(), it was found that that was about 0.0003 s. So I thought that this can be used.
4. The result of measurement can be seen at Stackdriver as milliseconds.

Sample script :

function func1(range, formula) {
  range.setFormula(formula);
}

function func2(range) {
  var d = range.getValue();
  while (r == d) {
    var r = range.getValue();
  }
}

function onEdit() {
  var formula = '### Built-in function ###'; // Set the built-in function you want to measure.

  var label = "Execution time for built-in functions.";
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var range = ss.getRange(cell.getRow(), cell.getColumn());
  func1(range, formula);
  console.time(label);
  func2(range);
  console.timeEnd(label);
}

Note :

• When built-in functions with very long time is measured, an error may occur at getValue().
  • In my environment, the built-in function for 10 seconds worked fine.

Gists

This sample script is for straightening elements in 2 dimensional array using Google Apps Script (GAS). When applications using Spreadsheet are developed by GAS, it usually uses 2 dimensional array by setValues(). And the lengths of each element are required to be the same. On the other hand, data used for the applications might not be the same length for each element in 2 dimensional array. This sample script can be used under such situation.

ggsrun was updated to v.1.3.2

• v1.3.2 (October 20, 2017)

  1. Updated ggsrun’s Install manual (README.md). Since I thought that the manual became too complicated, I separated it to the simple version and the detail version. And also , recently, since Google’s specification was updated, about how to deploy API executable and enable APIs for ggsrun’s Install manual were updated.
  2. From this version, scripts in a project can be rearranged. The rearrangement can be done by interactively on your terminal and/or a configuration file. The usage is here
     • For rearranging scripts, there is one important point. When scripts in a project is rearranged, version history of scripts is reset once. So if you don’t want to reset the version history, before rearranging, please copy the project. By copying project, the project before rearranging is saved.

go-rearrange and gorearrange were updated to v.1.0.2

From this version, data included multi-bytes characters can be used. At Linux, it works fine. At Windows DOS, rearranging and selecting data can be done. But the displayed data is shifted. Although this may be a bug of termbox-go, I don’t know the reason. I’m sorry. On the other hand, data with only single-byte characters works fine. About MAC, I don’t have it. If someone can confirm and tell me it, I’m glad.

go-rearrange and gorearrange were updated to v.1.0.1

• As one of outputs, indexmode (bool) was added. If this is true, the rearranged result is output as the change of index for the source data. For example, if the source data and rearranged data are ["a", "b", "c"] and ["c", "b", "a"], respectively. The output will become [2, 1, 0].

By this, the specification for creating applications will expand.

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

Overview

This is a CLI tool to interactively rearrange a text data on a terminal.

Description

Since I couldn’t find CLI tools for manually rearranging text data, I created this CLI tool.

For this, at first, I created a Golang library go-rearrange.

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

Gists

onEdit(e) which is used for the Edit event on Spreadsheet has the old value as e.oldValue. The specifications for this are as follows.

1. When an user edited a single “A1” cell, e of onEdit(e) shows hoge for e.oldValue and fuga for e.value.
2. When an user edited the “A1:A2” multiple cells, e.oldValue and e.value of onEdit(e) are not shown anything.
3. When an user copied and pasted from other cell, e.oldValue and e.value of onEdit(e) are not shown anything.

This sample script was created to retrieve both the edited values and the old values for the range of edited cells. This is the modified e.oldValue.

OnedriveApp was updated to v1.1.0.

From this version, retrieving access token and refresh token became more easy.

GitHub of OnedriveApp

Gists

This sample script is for uploading CSV file as Spreadsheet and modifying permissions using Golang.

I think that the detail information of google-api-go-client is a bit little. The sample scripts are so little. It retrieves most information from only godoc and GitHub. So I publish such sample scripts here. If this is useful for you, I’m glad.

Important points :

1. Give mimeType of file that it wants to upload to options of Media(r io.Reader, options ...googleapi.MediaOption).
2. In order to give options, use googleapi.ContentType().
3. Give mimeType of file that it wants to convert, when it uploads it to Google Drive, to file of Create(file *File).
4. In order to give file, use &drive.File{}.
5. For installing permissions, use &drive.Permission{}. Each parameter is the same to them for Python.

This sample script uses Quickstart. So in order to use this sample script, at first, please do Step 1 and Step 2 of the Quickstart.

Gists

This is a sample script for selecting files in Google Drive using HTML select box for Google Apps Script.

Feature

Feature of this sample.

• It is a simple and space saving.
• When the folder is selected, the files in the folder are shown.
• When the file is selected, the ID of file is retrieved. Users can use this ID at GAS.
• When a folder is opened, all files in the folder are cached. By this, the second access of the folder is faster.
• It doesn’t retrieve all files in Google Drive at once, so the read of files from Google Drive becomes the minimum necessary.

I will use this for applications that users need to select files on Google Drive.