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

The Thinker

Workaround: Making Users Edit Protected Cells using Google Apps Script

Gists

Abstract

One day, you might have a situation where you are required to make users edit the protected cells using Google Apps Script. This report introduces a workaround for achieving this situation. The key factors for achieving this are as follows. 1. Run the script as the owner of Spreadsheet even when the script is run by a user. 2. In order to run the script as the owner, the installable triggers and the Web Apps are used.

Technique of Array Processing for Custom Functions on Google Spreadsheet using Google Apps Script

Gists

Description

At Google Spreadsheet, custom functions created by Google Apps Script can be used. Ref When the custom function is used, the users can create a function for expanding the built-in functions for Spreadsheet. One day, you might have a situation for executing the custom function using an array. For example, under the situation that your custom function uses a single value instead of an array as the argument, when you want to use an array for each argument of the custom function, it is required to modify the original script of the custom function or the function calling the custom function. In this report, I would like to introduce a technique of array processing for the custom functions on Google Spreadsheets.

Technique for Processing Google Spreadsheet Including Merged Cells using Google Apps Script

Gists

Description

At Google Spreadsheet, the cells can be merged as one cell. But, when the Spreadsheet including the merged cells is used with Google Apps Script, the script becomes a bit complicated. Also, I sometimes find some questions like this situation on Stackoverflow. In this report, I would like to introduce a technique for easily using the Spreadsheet including the merged cells with Google Apps Script.

Principle

Before it introduces the sample scripts, I would like to introduce the principle for using the Spreadsheet for the merged cells. You can understand the principle of this method from the following sample input and output images.

Uploading Files without Authorizing Scopes by Shared Users with Dialog on Google Spreadsheet using Google Apps Script

Gists

Abstract

One day, you might have a situation where you are required to make the shared users upload a file and text using a dialog or sidebar on Google Spreadsheet to your Google Drive and Spreadsheet without authorization by the users. This report introduces a solution for achieving this situation.

Introduction

Google Spreadsheet can run Javascript on a dialog and a sidebar. Ref These can be used as a strong tool for working on Spreadsheet. There might be a situation where you want to make the shared users input a value and upload a file without authorization of the scopes. In this report, I would like to introduce 2 patterns to achieve this goal.

Workaround: Detecting Change of IMPORTRANGE using OnEdit trigger with Google Apps Script

Gists

Abstract

One day, you might have a situation where you are required to update a sheet using Google Apps Script when the cell values retrieved by IMPORTRANGE are changed. This report introduces a workaround for achieving this situation.

Introduction

Google Apps Script can be executed by several triggers. Ref When a cell in a Google Spreadsheet is manually edited, a function of Google Apps Script can be executed by detecting this edit. In most cases, the OnEdit trigger trigger of the simple trigger or the installable trigger is used. When the OnEdit trigger is used, a function can be executed by manually editing a cell. When the function is executed, the function can be run by giving the event object including the information about the edited cell.

Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script

Gists

Abstract

This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.

Introduction

Recently, the Emoji reactions have been implemented in the comments on Google Docs files (Google Documents, Google Slides, and Google Spreadsheets). Ref With this implementation, the collaborative work has been higher. Here, it is considered that when the Emoji reactions can be retrieved from the Google Docs files, the statistics of the reactions will be also more useful for increasing collaboration. This report introduces a sample script for retrieving Comments including the Emoji reactions from Google Docs files.

Inserting HTML including Javascript on Web Apps Created by Google Apps Script

Gists

Abstract

One day, you might have a situation where you are required to create a Web Apps with Google Apps Script and are required to load another HTML created by Javascript on the Web Apps. This report will help achieve such a situation.

Introduction

Google Apps Script can create Web Apps. Ref When you access the Web Apps using your browser, you can see the HTML. When your browser can run Javascript, you can see the HTML reflecting the Javascript. The Web Apps created by Google Apps Script is one of the important and useful cloud applications. About the Web Apps, you have a situation where it is required to insert another HTML to the current HTML. And, another HTML might be required to be created by Javascript including the HTML. However, it is difficult a little to find detailed information about this. This report introduces a simple sample script for achieving such a situation.

Executing Google Apps Script with Service Account

Gists

Abstract

One day, you might have a situation where it is required to run Google Apps Script using the service account. Unfortunately, in the current stage, Google Apps Script cannot be directly run with the service account because of the current specification. So, this report introduces a workaround for executing Google Apps Script using the service account.

Introduction

When you want to execute Google Apps Script from outside of Google, as the basic approach, it can be achieved by Google Apps Script API. Ref In order to use Google Apps Script, it is required to link the Google Apps Script project with the Google Cloud Platform project. Ref But, in the current stage, Google Apps Script can be executed by Google Apps Script API with only the access token obtained from OAuth2. Unfortunately, the access token obtained by the service account cannot used for executing Google Apps Script using Google Apps Script API. It seems that this is the current specification on the Google side. However, there might be a case that it is required to execute Google Apps Script using the service account. In this report, I would like to introduce a workaround for executing Google Apps Script using the service account. In this workaround, the Web Apps created by Google Apps Script is used. The Web Apps can be used for executing the preserved functions of doGet and doPost from outside of Google. Ref In this workaround, this Web Apps is used for executing the various functions.

Updated: GAS Library - HtmlFormApp

HtmlFormApp was updated to v1.0.2.

  • v1.0.2 (October 17, 2023)

    1. The 2nd argument row of appendFormData(object, row) was added. This is from this suggestion. When row is used, the value is put into the specific row of the Spreadsheet. In this case, please set the value of row more than 1.

      • In this case, the submitted row can be forcefully put into the specific row of Google Spreadsheet. So, when you run appendFormData(object, row) by the constant value of row, the submitted row is put into the same row. Please be careful about this.

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