tanaikech

Using Google API Client Library (gapi) for JavaScript with Service Account

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.

Sample script

In this sample script, GetAccessTokenFromServiceAccount_js of Javascript library is used. Before you use this script, please set your private_key, client_email and scopes to the variable of object.

<input type="button" value="Run" onClick="run()" />

tanaikech

Retrieving Hidden Rows and Showing Rows by Filter View on Google Spreadsheet using Google Apps Script

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.

The flow of this script is as follows.

1. Retrieve the settings of the filter view (filterViews) you want to use.
   • In this case, the method of "spreadsheets.get" can be used.
2. Create new basic filter to the sheet you want to use using the retrieved settings of the filter view.
   • In this case, the method of "spreadsheets.batchUpdate" can

tanaikech

Benchmark: Process Costs for Retrieving 1st Empty Cell and 1st Non Empty Cell of Specific Column in Google Spreadsheet using Google Apps Script

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

tanaikech

Retrieving Access Token using Service Account for PHP without using googleapis

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

tanaikech

Changing Font of Selected Text to 'Google Sans' on Google Document using Google Apps Script

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.

tanaikech

Specification of Search Query for File List Method in Drive API

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.

Experiments

I have done the following 5 experiments.

tanaikech

Creating Colorful Buttons on Google Spreadsheet using Google Apps Script

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".

Demo

tanaikech

A Bug of New IDE about Time Zone of Google Apps Script project was removed

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

Today, I got a report from Google issue tracker. When I tested above, I could confirm that the issue was removed. When I created new Google Apps Script project, I confirmed that the value of timeZone in appsscript.json is Asia/Tokyo. So I reported this here.

tanaikech

Plotting Points on Image using Google Apps Script

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.

Usage

tanaikech

Redeploying Web Apps without Changing URL of Web Apps for new IDE

At March 15, 2021, one endpoint is created for one deployment. Ref By this, when you redeploy "Web Apps", the endpoint is changed. Because the deployment ID is changed. It seems that this it the new specification. In this report, I would like to introduce the method for redeploying Web Apps without changing the URL of Web Apps for new IDE.

Deploy Web Apps

1. Open "New deployment" dialog with "Deploy" -> "New deployment".

2. Select "Web app" for "Select type".