Gists

In this report, I would like to introduce the method for running functions by directly specifying the function names with Web Apps for Google Apps Script.

Description

It has already been known that the directly specified functions in the project can be run from the outside by enabling “API executable” and using the method of scripts.run in Google Apps Script API. In this case, the installation for using Apps Script API is a bit complicate. I think that this is making users difficult to use Apps Script API, although the important settings are including in the installation when the importance of the security is considered. As one of workarounds for making easy to run the directly specified functions in the project, I would like to introduce the method using Web Apps. When Web Apps is accessed, the functions of doGet() and doPost() are used. These are the reserved function name. But, when this method is used, the functions in the project can be run by directly selecting from the outside.

RunAll was updated to v1.1.0.

• v1.1.0 (August 26, 2019)

  1. New method for achieving the parallel processing with Web Apps was added.

You can see the detail information here https://github.com/tanaikech/RunAll

python library - getfilelistpy was updated to v1.0.4.

• v1.0.4 (August 23, 2019)

  1. For OAuth2, oauth2client and google_auth_oauthlib got to be able to be used. About the sample script for google_auth_oauthlib, please see this.

You can check getfilelistpy at https://github.com/tanaikech/getfilelistpy.

You can also check getfilelistpy at https://pypi.org/project/getfilelistpy/.

Gists

This is a sample script for “Examples of How to Derive a Signing Key for Signature Version 4” using Google Apps Script.

In order to use AWS SDKs, there are the sample scripts for the languages of Java, .NET (C#), Python, Ruby, JavaScript (Node.js). But the sample script of Google Apps Script is not prepared. I saw the question related to this at Stackoverflow. So I would like to also introduce the sample script here.

gonetatmo was updated to v.1.0.1

• v1.0.1 (August 2, 2019)

  1. A bug was removed.
     • When the outer module is not active, an error occurred. In this version, this bug was removed.

You can check gonetatmo at https://github.com/tanaikech/gonetatmo.

Gists

This is a sample script for parsing HTML using Google Apps Script. When HTML data is converted to Google Document, the HTML data can be parsed and be converted to Google Document. In this case, the paragraphs, lists and tables are included. From this situation, I thought that this situation can be used for parsing HTML using Google Apps Script. So I could came up with this method.

In the Sheet API, the HTML data can be put to the Spreadsheet with the PasteDataRequest. But unfortunately, in this case, I couldn’t distinguish between the body and tables.

Gists

This is a sample script for retrieving values from filtered Sheet in Spreadsheet using Google Apps Script. When the values are retrieved the filtered sheet by the basic filter, if setValues() and setDisplayValues() are used, all values without the filter are retrieved. In this script, I would like to introduce the method for retrieving the values from the filtered sheet using Google Apps Script.

In order to retrieve the values from the filtered sheet, one method has already been proposed. That method retrieved the values from the filtered sheet by retrieving columnMetadata and rowMetadata of the method of spreadsheet.get of Sheets API. In this case, the rows and columns hidden by the filter can be retrieved.

Overview

This is a GAS library for unzipping a Zip file protected by a password using Google Apps Script.

Description

Recently, I had a situation that it is required to unzip a Zip file protected with the password. But unfortunately, in the current stage, the method of Utilities.unzip() cannot unzip such protected files. So when I had been looking for the other workarounds, I found zlib.js. Especially, it’s unzip.min.js. This is created for Javascript. So when I used this using Google Apps Script, it was found that it didn’t work for Google Apps Script. So I prepared a wrapper script for running it with Google Apps Script. And when I created the wrapper script, I thought that under the current stage, if the protected Zip file can be unzipped, this will be useful for other users. So I created this as a GAS library.

Gists

This is a sample script for the resumable upload of Files to Google Drive using Golang. This script uses the library of google-api-go-client. About the installation of google-api-go-client, please check the Quickstart for golang at the official site.

Sample script:

package main

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

	drive "google.golang.org/api/drive/v3"

	"golang.org/x/oauth2"
	"golang.org/x/oauth2/google"
	"golang.org/x/oauth2/jwt"
)

// 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{
			drive.DriveScope,
		},
		TokenURL: google.JWTTokenURL,
	}
	client := config.Client(oauth2.NoContext)
	return client
}

func main() {
	filename := "sample.txt"     // Filename
	baseMimeType := "text/plain" // MimeType
	client := ServiceAccount("credential.json") // Please set the json file of Service account.

	srv, err := drive.New(client)
	if err != nil {
		log.Fatalln(err)
	}
	file, err := os.Open(filename)
	if err != nil {
		log.Fatalln(err)
	}
	fileInf, err := file.Stat()
	if err != nil {
		log.Fatalln(err)
	}
	defer file.Close()
	f := &drive.File{Name: filename}
	res, err := srv.Files.
		Create(f).
		ResumableMedia(context.Background(), file, fileInf.Size(), baseMimeType).
		ProgressUpdater(func(now, size int64) { fmt.Printf("%d, %d\r", now, size) }).
		Do()
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Printf("%s\n", res.Id)
}

Note:

• In this sample, the Service account is used. So the file is uploaded to the Service account’s Drive. When client retrieved from OAuth2 is used, the file is uploaded to owner’s Drive.

References:

• google-api-go-client
• package drive

Gists

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

Before you use this script, please install python library of gdoctableapppy.

$ pip install gdoctableapppy

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.

from google.oauth2 import service_account
from gdoctableapppy import gdoctableapp
from googleapiclient.discovery import build

SCOPES = ['https://www.googleapis.com/auth/documents',
          'https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'credential.json' # Please set the json file of Service account.
creds = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = build('sheets', 'v4', credentials=creds)

spreadsheet_id = '###'  # Please set here
document_id = '###'  # Please set here

res = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id, range='Sheet1!A1:C5').execute()
values = res['values']

resource = {
    "service_account": creds,
    "documentId": document_id,
    "rows": len(values),
    "columns": len(values[0]),
    "append": True,
    "values": values
}
res = gdoctableapp.CreateTable(resource)
print(res)

References:

• gdoctableapppy
• Service account