Gists

In the case appending values to cell by inserting rows, when sheets.spreadsheets.values.append is used, the values are appended to the next empty row of the last row. If you want to append values to between cells with values by inserting row, you can achieve it using sheets.spreadsheets.batchUpdate.

When you use this, please use your access token.

Endpoint :

POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate

Request body :

In this request body, it appends the data of “sample1, sample2, sample3” to “A1:A3” of the sheetId of “1234567890”. Before appends the data, it supposes that there are some values at “A1:A3”.

Gists

This is a sample script for transposing JSON object using Google Apps Script.

Input data :

[
    {"key1":"a1","key2":"a2","key3":"a3","key4":"a4","key5":"a5"},
    {"key1":"b1","key2":"b2","key3":"b3","key4":"b4","key5":"b5"},
    {"key1":"c1","key2":"c2","key3":"c3","key4":"c4","key5":"c5"},
    {"key1":"d1","key2":"d2","key3":"d3","key4":"d4","key5":"d5"},
    {"key1":"e1","key2":"e2","key3":"e3","key4":"e4","key5":"e5"}
]

Output data :

{
    "key1": ["a1", "b1", "c1", "d1", "e1"],
    "key2": ["a2", "b2", "c2", "d2", "e2"],
    "key3": ["a3", "b3", "c3", "d3", "e3"],
    "key4": ["a4", "b4", "c4", "d4", "e4"],
    "key5": ["a5", "b5", "c5", "d5", "e5"]
}

Script :

At first, keys have to be defined by yourself, because the order of json is not decided.

var keys = ["key1", "key2", "key3", "key4", "key5"];
var result = {};
data.map(function(_, i){return keys.map(function(f, j){return data[i][keys[j]]})}).forEach(function(e, i){result[keys[i]] = e});
Logger.log(JSON.stringify(result))

Gists

This is a sample script for transposing slice from (n x m) to (m x n) for golang.

Script :

package main

import "fmt"

func transpose(slice [][]string) [][]string {
    xl := len(slice[0])
    yl := len(slice)
    result := make([][]string, xl)
    for i := range result {
        result[i] = make([]string, yl)
    }
    for i := 0; i < xl; i++ {
        for j := 0; j < yl; j++ {
            result[i][j] = slice[j][i]
        }
    }
    return result
}

func main() {
    sample := [][]string{
        []string{"a1", "a2", "a3", "a4", "a5"},
        []string{"b1", "b2", "b3", "b4", "b5"},
        []string{"c1", "c2", "c3", "c4", "c5"},
    }
    ar := transpose(sample)
    fmt.Println(ar)
}

Result :

[
    ["a1", "b1", "c1"],
    ["a2", "b2", "c2"],
    ["a3", "b3", "c3"],
    ["a4", "b4", "c4"],
    ["a5", "b5", "c5"]
]

The Go Playground

https://play.golang.org/p/XoZy7m65rEG

Gists

This is a sample script for opening a site with new window using Google Apps Script. It is possible to open the site inside the opened dialog box using iframe. But in my situation, I had to open the site as new window. So I created this. As a sample application, it can think of like this. When the special keyword was inputted, open sites and files in Google Drive as a help window. In this case, the trigger installed onEdit() is required to be used. I think that there are some other applications.

At February 15th, 2018, ShapeApp was featured as one of “4 useful add-ons launched last month”.

Gists

This is a sample script for uploading files from local PC to Google Drive using Python. In this sample, Quickstart is not used. So when you use this script, please retrieve access token.

Curl sample :

curl -X POST \
    -H "Authorization: Bearer ### access token ###" \
    -F "metadata={name : 'sample.png', parents: ['### folder ID ###']};type=application/json;charset=UTF-8" \
    -F "file=@sample.png;type=image/png" \
    "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart"

]

Python sample :

When above curl sample is converted to Python, it becomes as follows.

ggsrun was updated to v.1.4.1

• v1.4.1 (February 9, 2018)
  1. For uploading, the resumable-upload method was added.
     • The resumable-upload method is automatically used by the size of file.
       • “multipart/form-data” can upload files with the size less than 5 MB.
       • “resumable-upload” can upload files with the size more than 5 MB.
     • The chunk for resumable-upload is 100 MB as the default.
       • Users can also give this chunk size using an option.
     • $ ggsrun u -f filename -chunk 10
       • This means that a file with filename is uploaded by each chunk of 10 MB.

You can read “How to install” at here.

Gists

This is a sample script for copying values from JSON to a struct using reflect package.

Script :

package main

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

type obj struct {
	Key1 string `json:"k1"`
	Key2 string `json:"k2"`
	Key3 int64  `json:"k3"`
	Key4 int    `json:"k4"`
	Key5 bool   `json:"k5"`
}

func main() {
	data := `{"k1": "v1", "k2": "v2", "k3": 1234567890, "k4": 456, "k5": true}`
	d := map[string]interface{}{}
	json.Unmarshal([]byte(data), &d)
	obj := &obj{}
	s := reflect.ValueOf(obj).Elem()
	typeOfT := s.Type()
	for i := 0; i < s.NumField(); i++ {
		for j, f := range d {
			if typeOfT.Field(i).Tag.Get("json") == j {
				fl := s.FieldByName(typeOfT.Field(i).Name)
				switch fl.Kind() {
				case reflect.Bool:
					fl.SetBool(f.(bool))
				case reflect.Int, reflect.Int64:
					c, _ := f.(float64)
					fl.SetInt(int64(c))
				case reflect.String:
					fl.SetString(f.(string))
				}
			}
		}
	}
	fmt.Printf("%+v\n", obj) // &{Key1:v1 Key2:v2 Key3:1234567890 Key4:456 Key5:true}
}

Result :

&{Key1:v1 Key2:v2 Key3:1234567890 Key4:456 Key5:true}

The Go Playground

https://play.golang.org/p/Rz-GNbFyDfh

Gists

This sample script is for parsing JSON object. In the object, the keys are number and changing every time.

Object:

{
    "key1": {
        "key2": [
            {"0": [{"key3": "value3a"}, {"key3": "value3b"}]},
            {"1": [{"key3": "value3c"}, {"key3": "value3d"}]}
        ]
    }
}

Script:

package main

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

type key1 struct {
    Key1 key2 `json:"key1"`
}

type key2 struct {
    Key2 []interface{} `json:"key2"`
}

func main() {
    data := `{"key1": {"key2": [{"0": [{"key3": "value3a"}, {"key3": "value3b"}]},{"1": [{"key3": "value3c"}, {"key3": "value3d"}]}]}}`
    k1 := &key1{}
    json.Unmarshal([]byte(data), &k1)
    for i, e := range k1.Key1.Key2 {
        if key, ok := e.(map[string]interface{})[strconv.Itoa(i)].([]interface{}); ok {
            for _, f := range key {
                fmt.Printf("%+v\n", f.(map[string]interface{})["key3"])
            }
        }
    }
}

Result:

value3a
value3b
value3c
value3d

The Go Playground

https://play.golang.org/p/xm2KvgOIkKH

• v2.1.0 (February 3, 2018)

  • From this version, it got to be able to use the projects of both standalone script type and container-bound script type.

• Add-on
• GitHub