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().

ggsrun was updated to v.1.2.1

1. Configuration file (ggsrun.cfg) became to be able to be read using the environment variable.

You can check this at here.

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

I think that this method is one of various ideas.

Problem

When you want to create a chart and embed it to a cell using custom functions, you notice that insertChart() cannot be used. There are some limitations for using custom functions. But insertChart() creates floating charts. So in order to embed a chart to a cell, the function =IMAGE() is suitable for this situation. Here, setFormula() for setting =IMAGE() and DriveApp.createFile() for creating images from charts also cannot be used for custom functions.

This sample script converts from Microsoft Docx File on Google Drive to Google Spreadsheet, and converts to HTML file.

Drive APIs v2 and v3 are used for this. Please set as follows.

• Drive API v2
• Drive API v3

“Drive API v2” can be used at Google Apps Script by enabling Drive API of Advanced Google services and of Google API Console.

How to use it is as follows.

1. In the script editor, select Resources > Advanced Google services

Decoding JSON by Golang

func main() {
    data := `{
      "A_key1": {
        "B_key1": {
          "C_key": "value"
        }
      },
      "A_key2": {
        "B_key2": {
          "C_key": "value"
        }
      },
      "A_key3": {
        "B_key3": {
          "C_key": "value"
        }
      },
      "A_key4": {
        "B_key4": {
          "C_key": "value"
        }
      },
      "A_key5": {
        "B_key5": {
          "C_key": "value"
        }
      }
    }`
    var p interface{}
    json.NewDecoder(strings.NewReader(data)).Decode(&p)
    fmt.Println(p)
}

Go Playground

Dynamically Creating JSON by Golang

obj := map[string]interface{}{}
obj["hoge"] = "huga"
fmt.Println(obj)

Go Playground

Retrieving Response Headers by Golang

res, _ := client.Do(req)
contentType := res.Header.Get("Content-Type")
contentLength := res.Header.Get("Content-Length")