Gists

I have submitted a report of “Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API” before. At that time, there are no direct methods for setting multiple hyperlinks to the part of text in a cell. But, recently, textFormatRuns was added to Sheets API. By this, multiple hyperlinks got to be able to be set to the part of text in a cell. In this report, I would like to introduce a sample script for this.

Gists

Abstract

In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.

Gists

Unfortunately, in the current stage, in order to retrieve the file list using the created date, the search query of createdDate cannot be used with searchFiles method of DriveApp in Google Apps Script. This has already been reported at this issue tracker In this post, I would like to introduce a workaround for searching the files using the created date.

Issue and workaround

• The parameter of “searchFiles” method of DriveApp uses the search query for Drive API v2. When I tested createdDate > '####-##-##' for “searchFiles” and “Files: list” of Drive API v2, I confirmed errors like Invalid argument: q and Invalid query occurred, respectively.

Gists

These are the sample scripts for creating and deleting multiple events in Google Calendar by batch requests using Calendar API with Node.js.

In the current stage, unfortunately, googleapis for Node.js cannot request batch requests. Ref So, when multiple events are created and deleted in Google Calendar using Node.js, it is required to run the script in a loop. In this case, the process cost becomes high. Ref In this post, I would like to introduce creating and deleting multiple events in Google Calendar using batch request with Node.js.

Overview

This is a Node.js module to run the batch requests of Google APIs.

Description

In Google APIs, there are APIs where batch requests can be run. The batch requests can run multiple API calls by one API call with the asynchronous process. By this, both the process cost and the quota cost can be reduced. Ref In Node.js, the wonderful module of googleapis for Node.js is existing. But, in the current stage, unfortunately, it seems that the googleapis for Node.js cannot run the batch requests. Ref So, I created this module. This module can achieve batch requests with Node.js. In order to run batch requests, the access token retrieved from googleapis for Node.js can be used.

Gists

This is a sample script for updating Array1 with Array2 using Google Apps Script.

As a sample situation, there are 2 arrays (Array1 and Array2) of the 2-dimensional array. The sample situation can be seen in the above sample Spreadsheet.

• Conditions
  • When the values of column “A” of Array2 are existing in column “A” of Array1, the rows of Array1 are updated by that of Array2.
  • When the values of column “A” of Array2 are not existing in column “A” of Array1, the rows of Array2 are appended to Array1.
  • When the values of column “A” of Array1 are not existing in column “A” of Array2, the rows of Array1 are deleted.

I sometimes see such questions on Stackoverflow. So, I thought that when this sample script is posted, it might be useful for users.

My report has been published on the Blog of Google Cloud.

Blog

Efficient File Management using Batch Requests with Google Apps Script

Twitter

Gist

This is a sample script for retrieving the batch path for using the batch requests using Google Apps Script.

After August 12, 2020, in order to use batch requests, the batch path is required to be used to the endpoint of the batch requests. And, the batch path is sometimes updated. So, when a constant batch path has been continued to be used, this might lead to the reason for an error. In this sample script, the batch path is retrieved from Discovery API. By this, the latest batch path can be always obtained.

BatchRequest was updated to v1.2.0.

• v1.2.0 (September 30, 2022)

  1. A new method of getBatchPath(name, version) was added. After August 12, 2020, in order to use batch requests, the batch path is required to be used to the endpoint of the batch requests. And, the batch path is sometimes updated. So, when a constant batch path has been continued to be used, this might lead to the reason for an error. In this method, the batch path is retrieved from Discovery API. By this, the latest batch path can be always simply obtained from the name of Google API. And, the retrieved batch path can be used in Do(object) and EDo(object) methods.

You can check this at https://github.com/tanaikech/BatchRequest.

Overview

This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.

DocsServiceApp was updated to v1.2.0

• v1.2.0 (September 29, 2022)

  1. Added a new method of getNamedFunctions(). This method can retrieve the named functions from Google Spreadsheet.

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