Overview

This is a Golang library for managing tables on Google Document using Google Docs API.

Description

Google Docs API has been released. When I used this API, I found that it is very difficult for me to manage the tables on Google Document using Google Docs API. Although I checked the official document, unfortunately, I thought that it’s very difficult for me. So in order to easily manage the tables on Google Document, I created this library.

Gists

This is the method for protecting cells of Spreadsheet that users copied from your Google Drive to user’s Google Drive using Google Apps Script.

Situation:

This method supposes the following situation.

• You want to make users copy a Spreadsheet on your Google Drive to user’s Google Drive. - Your Spreadsheet has several protected ranges. - Your Spreadsheet is shared with the user. - User doesn’t have own folder shared with you.
• You want to protect the ranges of Spreadsheet for the user. Namely, you want to remove the user from the editor of protected ranges.
• You want to achieve this using Google Apps Script.

Issue:

In above situation, I think that the following points are the bottleneck.

Gists

Introduction

At April 8, 2019, the specification of Google Apps Script Project was changed. You can see this at Google Cloud Platform Projects. The official document says as follows.

Warning: Starting on or after April 8, 2019, the Google Cloud Platform Console won’t be able to access the default GCP projects created for new Apps Script projects. Older, existing scripts may have default GCP projects that are still accessible, however. If you have a new script and require access to its GCP project from the Google Cloud Platform Console, you must use a standard GCP project.

Gists

This is a sample script for dynamically retrieving the keys and values from struct property using golang.

Sample script:

Go Playground

package main

import (
	"fmt"
	"reflect"
)

func main() {
	s := struct {
		key1 string
		key2 string
		key3 string
	}{"value1", "value2", "value3"}

	r := reflect.ValueOf(&s).Elem()
	rt := r.Type()
	for i := 0; i < rt.NumField(); i++ {
		field := rt.Field(i)
		rv := reflect.ValueOf(&s)
		value := reflect.Indirect(rv).FieldByName(field.Name)
		fmt.Println(field.Name, value.String())
	}
}

Gists

This is a sample script for sorting a slice using Golang. Recently, I had a situation for sorting the coordinates of cells of Spreadsheet. As a test case, it thinks of the situation that the randomized cells are sorted. I think that this can be also used for a table except for Spreadsheet.

Sample slice:

The sample slice is as follows.

ar := []struct {
    row   int
    col   int
    value string
}{
    {0, 0, "A1"},
    {0, 1, "B1"},
    {0, 2, "C1"},
    {1, 0, "A2"},
    {1, 1, "B2"},
    {1, 3, "D2"},
    {2, 0, "A3"},
    {2, 2, "C3"},
}

When each element of above slice is put to a Spreadsheet, it becomes as follows.

Overview

GetEditType is a GAS library for retrieving the edit types of the OnEdit event trigger of Spreadsheet using Google Apps Script (GAS).

Description

In the case that the OnEdit event trigger (simple and installable triggers) is used at Spreadsheet, when users manually edited the cell of Spreadsheet, the trigger is fired. At this time, there is the case that I want to know the edit type. For example, I would like to know about the following edit types.

Gists

This is a sample script for fixing a value putting by a custom function of Spreadsheet using Google Apps Script. When a custom function is used, the value retrieved by the custom function of Spreadsheet is automatically updated by recalculating. So in the case that the value retrieved by the custom function is changed by the time, the value is also changed by automatically updating. In this sample script, I would like to introduce a method for fixing such values.

Gists

This is a sample script for retrieving the values with and without duplicating from JSON object using Google Apps Script. Also this can be used by Javascript.

Sample script

var obj = [
  { key1: "value1a", key2: "value1b" },
  { key1: "value2a", key2: "value2b" },
  { key1: "value5a", key2: "value5b" },
  { key1: "value3a", key2: "value3b" },
  { key1: "value1a", key2: "value1b" },
  { key1: "value4a", key2: "value4b" },
  { key1: "value5a", key2: "value5b" },
  { key1: "value3a", key2: "value3b" }
];

var res = obj.reduce(
  function(obj, e) {
    if (
      obj.withoutDuplicating.some(function(f) {
        return f.key1 === e.key1 && f.key2 === e.key2;
      })
    ) {
      obj.withDuplicating.push(e);
    } else {
      obj.withoutDuplicating.push(e);
    }
    return obj;
  },
  { withoutDuplicating: [], withDuplicating: [] }
);

Logger.log(res);

Result

{
  "withoutDuplicating": [
    {
      "key1": "value1a",
      "key2": "value1b"
    },
    {
      "key1": "value2a",
      "key2": "value2b"
    },
    {
      "key1": "value5a",
      "key2": "value5b"
    },
    {
      "key1": "value3a",
      "key2": "value3b"
    },
    {
      "key1": "value4a",
      "key2": "value4b"
    }
  ],
  "withDuplicating": [
    {
      "key1": "value1a",
      "key2": "value1b"
    },
    {
      "key1": "value5a",
      "key2": "value5b"
    },
    {
      "key1": "value3a",
      "key2": "value3b"
    }
  ]
}

Note

• As other situation, when f.key1 === e.key1 && f.key2 === e.key2 is modified to f.key1 === e.key1, the duplication of key1 can be retrieved.

Reference

• Benchmark: Loop for Array Processing using Google Apps Script

Gists

This is a sample script for creating new table and putting values to cells using Google Docs API with Google Apps Script. Unfortunately, in the current stage, although I had been looking for the method for creating a table and putting the values in each cell at the official document, I couldn’t find. Google Docs API is growing now. So such documents might be not prepared yet. By this situation, I investigated about the method for achieving this method.

Gists

This is a sample script for investigating the possibility of the real time processes in a cell on Google Spreadsheet using Google Apps Script. As a sample situation, it tried the real time clock in a cell on Google Spreadsheet using Google Apps Script.

Demo:

Usage:

When you use this script, please do the following flow.