Gists

This is a sample script for replacing the template texts with an array in Google Document using Docs API with Python.

The sample input and output situations are as follows.

In the current stage, when replaceAllText of Docs API is used with the sample value of ["updated text 1", "updated text 2", "updated text 3"], all values of {{oldText}} are replaced with the 1st value of updated text 1 in one batch request. So in order to replace each {{oldText}} with ["updated text 1", "updated text 2", "updated text 3"], it is required to use a workaround.

Gists

This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved.

• Execute a function every 10 minutes only in 09:00 - 12:00 and 15:00 - 18:00 for the weekday.

When the above situation is shown as an image, it becomes as follows.

Gists

This is a sample script for simply editing the texts of texts boxes on Google Slides using Google Apps Script. The supposed situation is as follows.

• Google Slides has several text boxes of the same size and the same position.
• You want to retrieve the list of texts from the text boxes and want to change the texts using a simpler method.

In this case, I thought that when the sidebar created by Google Apps Script is used for changing the texts, your goal might be able to be simply achieved.

Gists

• Published: September 15, 2021

• Updated: September 17, 2021

  • From the discussions, added data by changing the wait time of LockService for Web Apps.

Kanshi Tanaike

Introduction

When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such a situation was investigated.

Gists

This is a sample script for using Google API Client Library (gapi) for JavaScript with the service account. Unfortunately, in the current stage, gapi cannot directly use the service account. So, in this case, it is required to implement the script for retrieving the access token from the service account. In this report, I would like to introduce the method for using gapi with the service account using a Javascript library.

Overview

This is a Javascript library to retrieve the access token from the Google Service Account. Ref

Description

I have already posted the sample script for retrieving the access token from the Google Service Account. Ref But, when I use this script, I thought that when this was published as the Javascript library, it will be useful. So I created this.

Install

<script src="getaccesstokengromserviceaccount_js.min.js"></script>

Or, using jsdelivr cdn

<script src="https://cdn.jsdelivr.net/gh/tanaikech/GetAccessTokenFromServiceAccount_js@master/getaccesstokengromserviceaccount_js.min.js"></script>

You can see the detail of this at https://github.com/tanaikech/GetAccessTokenFromServiceAccount_js

Gists

This is a sample script for retrieving the hidden rows and showing rows by the filter view on Google Spreadsheet using Google Apps Script. In the current stage, there are no methods for directly retrieving the hidden rows and showing rows by the filter view in Spreadsheet service (SpreadsheetApp). And, isRowHiddenByFilter of Class Sheet cannot be used for the filter view. But, fortunately, when Sheets API is used, the filter view can be retrieved and created. In this report, the hidden rows and showing rows by the filter view are retrieved using Sheets API.

Gists

Introduction

Here, I would like to report the process costs for retrieving the 1st empty cell or 1st non empty cell of the specific column of Google Spreadsheet using Google Apps Script (GAS). For this situations, the following 2 patterns can be considered.

1. Retrieving 1st empty cell of specific column by searching from TOP of sheet

2. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet

Gists

This is a sample PHP script to retrieve the access token from Service Account of Google without using googleapis.

Sample script

<?php

$private_key = "-----BEGIN PRIVATE KEY-----\n###-----END PRIVATE KEY-----\n"; // private_key of JSON file retrieved by creating Service Account
$client_email = "###"; // client_email of JSON file retrieved by creating Service Account
$scopes = ["https://www.googleapis.com/auth/drive.readonly"]; // Sample scope

$url = "https://www.googleapis.com/oauth2/v4/token";
$header = array("alg" => "RS256", "typ" => "JWT");
$now = floor(time());
$claim = array(
	"iss" => $client_email,
	"sub" => $client_email,
	"scope" => implode(" ", $scopes),
	"aud" => $url,
	"exp" => (string)($now + 3600),
	"iat" => (string)$now,
);
$signature = base64_encode(json_encode($header, JSON_UNESCAPED_SLASHES)) . "." . base64_encode(json_encode($claim, JSON_UNESCAPED_SLASHES));
$b = "";
openssl_sign($signature, $b, $private_key, "SHA256");
$jwt = $signature . "." . base64_encode($b);
$curl_handle = curl_init();
curl_setopt_array($curl_handle, [
    CURLOPT_URL => $url,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_POST => true,
    CURLOPT_POSTFIELDS => array(
        "assertion" => $jwt,
        "grant_type" => "urn:ietf:params:oauth:grant-type:jwt-bearer"
    ),
]);
$res = curl_exec($curl_handle);
curl_close($curl_handle);
$obj = json_decode($res);
$accessToken = $obj -> {'access_token'};
print($accessToken . "\n");

// This is the script for testing the access token. The file list of the service account's Drive is retrieved using Drive API.
$curl_test = curl_init();
curl_setopt($curl_test, CURLOPT_URL, 'https://www.googleapis.com/drive/v3/files?access_token=' . $accessToken);
curl_setopt($curl_test, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($curl_test);
print($response);

?>

References

• Using OAuth 2.0 for Server to Server Applications
• jwt.io
• I answered this sample script for this thread in Stackoverflow.

Gists

This is a sample script for changing the font of selected text to Google Sans on Google Document using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Document And, when you use this script, please select a text in Google Document and run the script. By this, the font of selected text is changed to Google Sans.