tanaike - Google Apps Script, Gemini API, and Developer Tips

The Thinker

Managing A Lot Of Google Calendar Events using Batch Requests with Google Apps Script

Overview

This is the sample scripts for managing a lot of Google Calendar Events using the batch requests with Google Apps Script.

Description

When we want to manage the events of Google Calendar, we have 2 ways. One is the use of Calendar service. Another is the use of Calendar API. In the case of them, when we want to manage a lot of calendar events, unfortunately, both ways have no batch requests. Namely, for example, when a lot of events are deleted, deleteEvent() and Events: delete are required to be used in the loop. In this case, the process cost will be high. On the other hand, Calendar API can use the batch requests. But in this case, in order to use this batch requests with Google Apps Script, it is required to create the request body of multipart/mixed by each user. Because there are no methods for automatically requests the batch requests. From this situation, here, I would like to introduce the simple sample scripts for creating, updating and deleting the events of Google Calendar using the batch requests with Google Apps Script.

Highlighting Row and Column of Selected Cell using Google Apps Script

Gists

This is a sample script for highlighting the row and column of the selected cell using Google Apps Script. For this, the OnSelectionChange event trigger is used.

Demo

Sample script

Please copy and paste the following script to the script editor of Spreadsheet. And, please select a cell. By this, the script is run by the OnSelectionChange event trigger.

function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const maxRows = sheet.getMaxRows();
  const maxColumns = sheet.getMaxColumns();
  sheet.getRange(1, 1, maxRows, maxColumns).setBackground(null);
  sheet.getRange(1, range.getColumn(), maxRows, 1).setBackground("yellow");
  sheet.getRange(range.getRow(), 1, 1, maxColumns).setBackground("yellow");
}

References

Disabling Buttons Put on Google Spreadsheet using Google Apps Script

Gists

Description

This is a sample script for disabling the buttons put on Google Spreadsheet using Google Apps Script.

When a script is run by clicking a button on Google Spreadsheet, there is the case that you don’t want to make users run the script in duplicate. This sample script achieves this situation.

Demo

In this demonstration, 2 types of buttons are used. Those are the drawing and image, respectively. When the button is clicked, the worker of 10 seconds is run. You can see that after the button was clicked, even when the button is clicked again, the worker script is not run.

Updated: GAS Library - BatchRequest

BatchRequest was updated to v1.1.0.

  • v1.1.0 (June 10, 2020)

    1. New method of EDo() was added. This method is the enhanced Do() method. When this method is used, the result values from the batch requests are parsed. And also, the number of requests more than 100 can be used. In this case, the split of the number of requests is processed for the limitation of 100.

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

Enhanced Custom Function for Google Spreadsheet using Web Apps as Wrapper

Overview

This is a proposal of the enhanced custom function for Google Spreadsheet using Web Apps as the wrapper.

Demo

Description

When the custom function is used, in the current specification, the most methods except several methods (for example, one of them is UrlFetchApp.) that the authorization is required cannot be used. So for example, when the filenames in the folder are retrieved from the folder name, unfortunately, this cannot be directly achieved. When this is tried, an error like Exception: You do not have permission to call DriveApp.getFoldersByName. Required permissions: (https://www.googleapis.com/auth/drive.readonly || https://www.googleapis.com/auth/drive) occurs. From this situation, it is considered that when the authorization has already been done, the method that the authorization is required might be able to be used. In this report. I would like to introduce the method for using such methods by using Web Apps as the wrapper.

Managing Shared Drive using Drive Service of Google Apps Script

Gists

When the method of “Files: list” in Drive API v3, the official document of includeItemsFromAllDrives and supportsAllDrives says as follows.

Deprecated - Whether both My Drive and shared drive items should be included in results. This parameter will only be effective until June 1, 2020. Afterwards shared drive items are included in the results. (Default: false)

Deprecated - Whether the requesting application supports both My Drives and shared drives. This parameter will only be effective until June 1, 2020. Afterwards all applications are assumed to support shared drives. (Default: false)

Updated: GetFileList for golang, Javascript, Node.js and Python

Updated: GetFileList for golang, Javascript, Node.js and Python

This is the libraries to retrieve the file list with the folder tree from the specific folder of own Google Drive and shared Drives.