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

Gists

This is a sample script for creating a shortcut on Google Drive using Google Apps Script.

Sample script

Before you run the script, please enable Drive API at Advanced Google services.

function createShortcut(targetId, name, folderId) {
  const resource = {
    shortcutDetails: { targetId: targetId },
    title: name,
    mimeType: "application/vnd.google-apps.shortcut",
  };
  if (folderId) resource.parents = [{ id: folderId }];
  const shortcut = Drive.Files.insert(resource);
  return shortcut.id;
}

// Please run this function.
function main() {
  const targetId = "###"; // Please set the ID of target file or folder.
  const shortcutName = "###"; // Please set the shortcut name.
  const folderId = "###"; // Please set the folder ID for putting the created shortcut.
  const id = createShortcut(targetId, shortcutName, folderId);
  console.log(id);
}

References

• Advanced Google services

Gists

Abstract

This is a report for detecting quickly checked checkboxes on Google Spreadsheet using Google Apps Script. It supposes that when the checkbox is checked, a function of Google Apps Script is run by the event trigger. In this case, when the multiple checkboxes on Google Spreadsheet are checked quickly, the script cannot be run for all checked checkboxes, because of the response speed of the event trigger. It is considered that to understand the response of event trigger is useful for creating the application for Spreadsheet. In this report, the detection of quickly checked checkboxes on Google Spreadsheet using Google Apps Script has been investigated. From this result, it led to understanding the response of event trigger.

Overview

This is Google Apps Script library for copying folder on Google Drive.

Description

I have sometimes the situation that it is required to back up the folder on Google Drive to Google Drive. But unfortunately, the method of makeCopy() of Class File and the method of Files: copy of Drive API cannot be used for directly copying the folder on Google Drive. So I created this as a library. This library can copy the folder on Google Drive. All files in the folder can be copied to Google Drive with keeping the folder structure. When there are the files with newer modified time in the source folder than those in the destination folder, the files in the destination folder are overwritten by the newer files in the source folder. Also, in this library, the folders in the shared Drive and the publicly shared folders can be used as the source and destination folder.

Gists

Abstract

I have already reported about “Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script”. Ref It is considered that when the situation which uses the event trigger of onSelectionChange is thought, the response speed is important. So, here, I investigated the characteristics of response for the event trigger of onSelectionChange.

Demo

Experiment

Sample script

In order to investigate the response speed, I used the following sample script. The work of sample script can be seen at above demonstration movie. In this report, the script is important for discussing the result. So I pot this at this section instead of the appendix.

Gists

onSelectionChange has been released at April 22, 2020. But this couldn’t be used at the released day. But now, I could confirm that this got to be able to be used. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script.

Demo

Usage

1. Please copy and paste the following script to the container-bound script of Google Spreadsheet, and save the script.
2. Please reopen the Google Spreadsheet.
   • By this, onOpen is run and the current sheet is put to PropertiesService.
   • Unfortunately, in the current stage, it seems that the event object of onSelectionChange has no information about the change of tab. So in order to detect the change of tab, I used the PropertiesService.
3. Then, please select a cell and cells on sheet.
   • By this, onSelectionChange is run by the onSelectionChange event trigger, and put the A1Notation to the cell.
   • When the active tab is moved, the sample script detects this, and the information of the changed tab is put to the cell.

Sample script

function onOpen(e) {
  const prop = PropertiesService.getScriptProperties();
  const sheetName = e.range.getSheet().getSheetName();
  prop.setProperty("previousSheet", sheetName);
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const a1Notation = range.getA1Notation();
  const sheetName = range.getSheet().getSheetName();
  if (sheetName != previousSheet) {
    range.setValue(`Changed tab from ${previousSheet} to ${sheetName}. ${a1Notation}`);
    
    // When the tab is changed, this script is run.
    
  } else {
    range.setValue(a1Notation);
  }
  prop.setProperty("previousSheet", sheetName);
}

References

• onSelectionChange(e)
• Properties Service

Gists

Recently, it seems that the specification of Google Spreadsheet was updated. Before this, when a cell has only one hyperlink. In this case, the hyperlink was given to a cell using =HYPERLINK("http://www.google.com/", "Google") as following figure.

But by the recent update, a cell got to be able to have multiple hyperlinks as following figure. In this case, the hyperlinks are set by the RichTextValue object.

In this report, I would like to introduce the method for setting and retrieving the multiple URLs for a cell.

GetFileList_js was updated to v1.0.2.

• v1.0.2 (May 15, 2020)

  1. Shared drive got to be able to be used. The file list can be retrieved from both your Google Drive and the shared drive.

     • For example, when the folder ID in the shared Drive is used id of resource, you can retrieve the file list from the folder in the shared Drive.

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

python library - getfilelistpy was updated to v1.0.5.

• v1.0.5 (May 15, 2020)

  1. Shared drive got to be able to be used. The file list can be retrieved from both your Google Drive and the shared drive.

     • For example, when the folder ID in the shared Drive is used id of resource, you can retrieve the file list from the folder in the shared Drive.

You can check getfilelistpy at https://github.com/tanaikech/getfilelistpy.

You can also check getfilelistpy at https://pypi.org/project/getfilelistpy/.

go-getfilelist was updated to v1.0.3

• v1.0.3 (May 14, 2020)

  1. Shared drive got to be able to be used. The file list can be retrieved from both your Google Drive and the shared drive.

     • For example, when the folder ID in the shared Drive is used folderID of Folder(folderID), you can retrieve the file list from the folder in the shared Drive.

You can get this from https://github.com/tanaikech/go-getfilelist

node module - google-drive-getfilelist was updated to v1.0.4

• v1.0.4 (May 14, 2020)

  1. Shared drive got to be able to be used. The file list can be retrieved from both your Google Drive and the shared drive.

     • For example, when the folder ID in the shared Drive is used id of resource, you can retrieve the file list from the folder in the shared Drive.

You can get this from https://github.com/tanaikech/node-getfilelist

You can also get this from https://www.npmjs.com/package/google-drive-getfilelist