When a string without no strings is split by strings.Split(), the created slice is the same to the slice created by make(). The length of the slice doesn’t become zero.

Sample script :

package main

import (
    "fmt"
    "strings"
)

func main() {
    sample1a := strings.Split("", " ")
    fmt.Printf("%v, %v, '%v', %v, %+q\n", sample1a, len(sample1a), sample1a[0], len(sample1a[0]), sample1a[0])

    sample1b := make([]string, 1)
    fmt.Printf("%v, %v, '%v', %v, %+q\n", sample1b, len(sample1b), sample1b[0], len(sample1b[0]), sample1b[0])

    var sample2a []string
    fmt.Printf("%v, %v\n", sample2a, len(sample2a))

    sample2b := []string{}
    fmt.Printf("%v, %v\n", sample2b, len(sample2b))
}

Result :

strings.Split() : [], 1, '', 0, ""
make()          : [], 1, '', 0, ""
var                : [], 0
[]string{}      : [], 0

Description

There are a limit executing time for Google Apps Script (GAS). It’s 6 minutes. So users have to pay attention to the process cost of the script. GAS can use JavaScript 1.7. This means to be able to be used comprehension for GAS.

In this report, the process cost for the comprehension has been investigated. The normal for loop was used as the competitor. As a result, it was found that the comprehension can be used one of methods for reducing the process cost. For 1 dimensional array, the process cost of comprehension is about 60% lower than that of the normal one. For 2 dimensional array, the process cost of comprehension is about 50% lower than that of the normal one. Each data is the average for 10 times measurements.

This sample script is for embedding animation GIF in a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

There are some limitations.

1. Images of jpeg and png can be embedded in a cell using =IMAGE(). But when animation GIF is embedded using it, GIF is not played.
2. insertImage() can insert the animation GIF to sheet. But it is not imported to one cell. It floats on several cells.
3. In order to float the animation GIF on one cell, the size of GIF has to be retrieved. But the size of image cannot be retrieved at spreadsheet APIs.
4. =IMAGE() and insertImage() cannot be used by custom functions.

Solution

I thought a method to floating an animation GIF on one cell using insertImage(). By this, I thought that it will be easy to use as a sheet with GIF images.

This sample script performs OCR and imports resultant text to a cell using custom function on Spreadsheet.

Drive API has a function to do OCR. It was used for this sample.

I think that this method is one of various ideas.

Problem

When OCR is performed and imported the result to a cell on spreadsheet, there are some limitations. DriveApp, UrlFetchApp, setFormula() cannot be used for custom functions.

Solution

In order to avoid these limitations, I used Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.

This sample is for changing file name and reopening the file with new name. The flow is as follows.

1. A file (sample.py) is opened.
2. Rename the file from sample.py to newsample.py.
3. The opened file is replace to the file with new name.

os.rename(oldfilewithpath, newname)
view = self.view.window().find_open_file(oldfilewithpath)
if view:
    view.retarget(newname)

This sample retrieves all files in a folder with spreadsheet. When there are some folders in the folder with spreadsheet, this script can retrieve all files in all folders. This script has to be a container-bound script for spreadsheet.

Script :

function getFileList(){
  var folderlist = (function(folder, folderSt, results){
    var ar = [];
    var folders = folder.getFolders();
    while(folders.hasNext()) ar.push(folders.next());
    folderSt += folder.getId() + "#_aabbccddee_#";
    var array_folderSt = folderSt.split("#_aabbccddee_#");
    array_folderSt.pop()
    results.push(array_folderSt);
    ar.length == 0 && (folderSt = "");
    for (var i in ar) arguments.callee(ar[i], folderSt, results);
    return results;
  })(DriveApp.getFolderById(
    DriveApp.getFileById(
      SpreadsheetApp.getActiveSpreadsheet().getId()
    ).getParents().next().getId()
  ),"",[]);

  var filelist = [];
  for (var i in folderlist){
    var folderid = folderlist[i][folderlist[i].length - 1];
    var temp = [];
    var folder = DriveApp.getFolderById(folderid);
    var files = folder.getFiles();
    var foldername = folder.getName();
    while(files.hasNext()){
      var file = files.next();
      temp.push([foldername, file.getName()]);
    }
    filelist.push(temp);
  }
  return Array.prototype.concat.apply([], filelist);
}

function main() {
  var data = getFileList();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange(1,1,data.length,data[0].length).setValues(data);
}

Result :

This sample script embeds a map to a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

When the map is embeded to a cell on spreadsheet as an image, the function =IMAGE() is suitable for this situation. However, setFormula() for importing =IMAGE() and DriveApp.createFile() for creating images from maps also cannot be used for custom functions.

Solution

In order to avoid these limitations, I used Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.

This sample is for changing from “float64” to “int” for values did unmarshal using map[string]interface{}.

When it did unmarshal using map[string]interface{}, a number with “int” was changed to “float64”. And it shows an error as follows.

Error :

panic: interface conversion: interface {} is float64, not int

Sample Script : It solves using following script.

package main

import (
    "encoding/json"
    "fmt"
    "reflect"
)

func main() {
    data := `{"key": 10}`
    var i map[string]interface{}
    json.Unmarshal([]byte(data), &i)

    val1 := i["key"]
    fmt.Printf("%v, %v\n", val1, reflect.TypeOf(val1)) // 10, float64

    i["key"] = int(i["key"].(float64))
    val2 := i["key"]
    fmt.Printf("%v, %v\n", val2, reflect.TypeOf(val2)) // 10, int
}

Go Playground

This sample is for replacing JSON key by golang.

package main

import (
    "encoding/json"
    "fmt"
)

func main() {
    json1 := `{"key1": "value1"}`

    obj := map[string]interface{}{}
    json.Unmarshal([]byte(json1), &obj)

    fmt.Println(obj) // <-- map[key1:value1]

    obj["key2"] = obj["key1"]
    delete(obj, "key1")

    fmt.Println(obj) // <-- map[key2:value1]
}

This sample script is for splitting command-line arguments by golang. There are 2 types. One is the regular expression is used. Another is that Split() and TrimSpace() are used.

Here, each process speed was compared.

Script :

package main

import (
    "regexp"
    "strings"
    "testing"
)

func BenchmarkB1(b *testing.B) {
    str := "test1.txt, test2.txt"
    b.ResetTimer()
    for i := 0; i < b.N; i++ {
        ar := regexp.MustCompile(`\s*,\s*`).Split(str, -1)
        var result []string
        for _, x := range ar {
            result = append(result, x) // --> 'test.js', 'test2.py'
        }
        _ = result
    }
}

func BenchmarkB2(b *testing.B) {
    str := "test1.txt, test2.txt"
    b.ResetTimer()
    for i := 0; i < b.N; i++ {
        ar := strings.Split(str, ",")
        var result []string
        for _, x := range ar {
            result = append(result, strings.TrimSpace(x)) // --> 'test.js', 'test2.py'
        }
        _ = result
    }
}

Result :

$ go test -bench .
BenchmarkB1-4             100000             13048 ns/op
BenchmarkB2-4            3000000               399 ns/op
PASS

Just as expected, the regular expression was slow. And it’s much slower than that of Split() and TrimSpace().