Gists

This is a workaround for detecting to edit Google Spreadsheet using Sheets API with the service account.

It has already been found that when Google Spreadsheet is edited using Sheets API, this can be detected by the installed OnChange trigger. For example, when a Spreadsheet is edited using Sheets API with the access token retrieved from your Google account, the event object of the installable OnChange trigger includes your email address and nickname. With this information, you can know the user who edited the Spreadsheet. However, when the Spreadsheet is edited using Sheets API with the access token retrieved from the service account, unfortunately, the email of the service account is not included in the event object of the OnChange event. It is considered that this is the current specification.

Gists

These are the sample scripts for using getBatchGet, batchUpdateContacts, batchDeleteContacts, batchCreateContacts of People API with Google Apps Script.

When the data is retrieved and put with People API, the process cost can be reduced by using batch requests. Fortunately, in the current stage, People API has native methods for using batch requests. Those are getBatchGet, batchCreateContacts, batchDeleteContacts, and batchUpdateContacts. In this post, I would like to introduce the sample scripts for using the batch requests of People API.

My report has been published at Champion Innovators Content Library.

• Report: Implementing Pseudo 2FA for Web Apps using Google Apps Script

If these reports will be useful, I’m glad.

Gists

This is a workaround for checking the existence of file ID in Google Drive without both the access token and API key.

When you want to check whether the file of the file ID is existing in Google Drive, generally, you might use Drive API and Drive service (DriveApp) of Google Apps Script. In this case, the scope of Drive API is required to be used. By this, the access token and the API key (in the case of publicly shared files) are required to be used. But, there might be a case that the available scopes are limited. In this post, I would like to introduce a workaround for checking the existence of file ID in Google Drive without both the access token and API key.

Gists

This is a workaround for retrieving the values and URLs from the smart chips inserted in Google Document using Google Apps Script.

Recently, the smart chips for Google Document are updated. Ref1 and Ref2 It is considered that this update will advance the collaboration for editing Document. So, there might a case that the information of the smart chips is required to be retrieved. Unfortunately, in the current stage, there are no methods for directly retrieving the information of the smart chips while the smart chips of DATE and PERSON can be retrieved. I believe that all smart chips will be able to be managed with Google Apps Script in the future update.

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.