Gists

This is a sample script for creating a table to Google Document by retrieving values from Google Spreadsheet for Golang.

Before you use this script, please install go library of go-gdoctableapp.

$ go get -v -u github.com/tanaikech/go-gdoctableapp

Sample script:

This sample script uses Service Account.

In this sample script, the values are retrieved from Sheet1!A1:C5 of Spreadsheet, and new table is created to the Document using the values.

package main

import (
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"os"

	gdoctableapp "github.com/tanaikech/go-gdoctableapp"
	"golang.org/x/oauth2"
	"golang.org/x/oauth2/google"
	"golang.org/x/oauth2/jwt"
	docs "google.golang.org/api/docs/v1"
	sheets "google.golang.org/api/sheets/v4"
)

// ServiceAccount : Use Service account
func ServiceAccount(credentialFile string) *http.Client {
	b, err := ioutil.ReadFile(credentialFile)
	if err != nil {
		log.Fatal(err)
	}
	var c = struct {
		Email      string `json:"client_email"`
		PrivateKey string `json:"private_key"`
	}{}
	json.Unmarshal(b, &c)
	config := &jwt.Config{
		Email:      c.Email,
		PrivateKey: []byte(c.PrivateKey),
		Scopes: []string{
			docs.DocumentsScope,
			sheets.SpreadsheetsScope,
		},
		TokenURL: google.JWTTokenURL,
	}
	client := config.Client(oauth2.NoContext)
	return client
}

func main() {
	spreadsheetID := "###" // Please set here
	documentID := "###"    // Please set here

	client := ServiceAccount("credential.json") // Please set the json file of Service account.
	srv, err := sheets.New(client)
	if err != nil {
		log.Fatalf("%v", err)
	}

	// Retrieve values from Spreadsheet.
	sheetValues, err := srv.Spreadsheets.Values.Get(spreadsheetID, "Sheet1!A1:C5").Do()
	if err != nil {
		log.Fatalf("%v", err)
	}
	values := sheetValues.Values

	// Put the retrieved values to Document.
	g := gdoctableapp.New()
	obj := &gdoctableapp.CreateTableRequest{
		Rows:    int64(len(values)),
		Columns: int64(len(values[0])),
		Append:  true,
		Values:  values,
	}
	res, err := g.Docs(documentID).CreateTable(obj).Do(client)
	if err != nil {
		log.Fatalf("%v", err)
	}
	fmt.Println(res)
}

References:

• go-gdoctableapp
• Service account

Overview

This is a python library to manage the 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.

Overview

This is a Node.js module to manage the 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.

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.