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.

Gists

In this report, I would like to report about the current specification of the search query for the file list method in Drive API.

Recently, I noticed that the specification of the search query for the file list method in Drive API might have been changed. I thought that to know the change of specification of the search query is important for creating the application using Drive API. In this report, I would like to introduce the current specification of the search query.

Gists

This is a sample script for creating the colorful buttons on Google Spreadsheet on Google Apps Script.

In order to achieve this, I have been looking for the method for creating the PNG image with the alpha channel using Google Apps Script. Recently, finally, I could find it. By this, the goal of this report got to be able to be achieved by the report of “Creating PNG Image with Alpha Channel using Google Apps Script”.

This is a sample script for creating a PNG image with the alpha channel using Google Apps Script.

The PNG image with the alpha channel is the image includes the transparent background. I have been looking for the method for creating such image using Google Apps Script. It is considered that when this will be achieved, it will be able to be used for various situations. For example, it is considered the following situations.

Gists

I had confirmed a bug of new IDE about the time zone of Google Apps script project. When I created new Google Apps Script project using new IDE, the time zone of GAS project is always America/New_York. In my area, it’s Asia/Tokyo. The time zone can be confirmed at appscript.json.

So, I had reported this issue to Google issue tracker. Ref After I posted this, I had also confirmed this issue at Stackoverflow. Ref

Gists

This is a sample script for plotting the points on an image using Google Apps Script.

Unfortunately, in the current stage, there are no methods for directly editing the image and putting the texts and some shapes in the methods for Google Apps Script. So in this case, it is required to use the workaround.

Fortunately, I have already published a report about “Inserting Text on Image using Google Apps Script” in my blog. Ref In this report, this sample is used.