Custom Function

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.

Gists

Description

I have already reported about “Automatic Recalculation of Custom Function on Spreadsheet Part 1” at here. Here, I would like to introduce other workaround for forcibly recalculating the custom functions and built-in functions using Class TextFinder. Class TextFinder has added at April 5, 2019. By this, this workaround can be proposed.

Sample scripts

Pattern 1

If you want to refresh all functions of all sheets in a Spreadsheet, you can use the following script. In this script, when the script is run, all built-in functions and custom functions in the Spreadsheet are refreshed.

Gists

This is a sample script for fixing a value putting by a custom function of Spreadsheet using Google Apps Script. When a custom function is used, the value retrieved by the custom function of Spreadsheet is automatically updated by recalculating. So in the case that the value retrieved by the custom function is changed by the time, the value is also changed by automatically updating. In this sample script, I would like to introduce a method for fixing such values.

Gists

In this report, I would like to introduce a workaround for automatically recalculating custom functions on Spreadsheet.

1. Situation

The sample situation is below. This is a sample situation for this document.

• There are 3 sheets with “sheet1”, “sheet2” and “sheet3” of sheet name in a Spreadsheet.
• Calculate the summation of values of “A1” of each sheet using a custom function.
• Sample script of the custom function is as follows.

function myFunction(e) {
    var r = 0;
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i in sheets) {
        r += sheets[i].getRange(e).getValue();
    }
    return r;
}

2. Workaround for recalculating

When =myFunction("A1") is put in a cell, the custom function sums each “A1” of “sheet1”, “sheet2” and “sheet3”. But in this case, when “A1” of one of 3 sheets is changed, the custom function is not recalculated.