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.
Initial situation
As a simple sample situation, it supposes the following situation.
- Cells “A1:A5” have the values of “a1”, “a2”, “a3”, “a4”, and “a5”, respectively.
- Cells “B1:B5” have the values of “b1”, “b2”, “b3”, “b4”, and “b5”, respectively.
The script of the sample custom function is as follows.
function SAMPLE(argument1, argument2) {
return JSON.stringify({ argument1, argument2 });
}
When a custom function of =SAMPLE(A1,B1) is put into cell “C1”, the following result is obtained.
When A1 and B1 are used as the arguments of SAMPLE, {"argument1":"a1","argument2":"b1"} is returned. From this result, it is found that the function SAMPLE is run with the values of cells “A1” and “B1”. This is a basic result of this sample custom function.
In this report, I would like to introduce several sample results for the array processing using this situation.
Using array to custom function
When a custom function of =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5)) is put into cell “C1”, the following result is obtained.
In this case, the values of “A1:A5” and “B1:B5” are ["a1", "a2", "a3", "a4", "a5"] and ["b1", "b2", "b3", "b4", "b5"], respectively. By this, the arguments of argument1, argument2 in the script are ["a1", "a2", "a3", "a4", "a5"] and ["b1", "b2", "b3", "b4", "b5"], and such the result is returned. If you want this result, you can use this without modifying the custom function and the script.
However, if your expected result was {"argument1":"a1","argument2":"b1"}, {"argument1":"a2","argument2":"b2"}, {"argument1":"a3","argument2":"b3"},,, in “C1:C5”, respectively, it is required to modify the custom function or the script. Each pattern is introduced in the next sections.
Modifying custom function
In this pattern, the expected result is obtained by modifying the custom function. In this case, it is not required to modify the script.
The modified custom function is as follows.
=MAP(A1:A5,B1:B5,LAMBDA(value1,value2,SAMPLE(value1,value2)))
When this formula is used, the following result is obtained.
From this image, it is found that the expected result could be obtained by using MAP function.
Modifying script of custom function
In this pattern, the final result is obtained by modifying the original script of the custom function. In this case, it is not required to modify the custom function from =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5)).
The modified script is as follows.
function SAMPLE(argument1, argument2) {
return argument1.map((e, i) =>
JSON.stringify({ argument1: e[0], argument2: argument2[i][0] })
);
}
When a custom function of =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5)) is put into cell “C1”, the following result is obtained.
But, in this modified script, when =SAMPLE(A1,B1) is used, an error like TypeError: argument1.map is not a function occurs. Because the values of “A1” and “B1” are not an array. When you want to use both =ARRAYFORMULA(SAMPLE(A1:A5,B1:B5)) and =SAMPLE(A1,B1), the following modification can be used.
function SAMPLE(argument1, argument2) {
if ([argument1, argument2].every((e) => Array.isArray(e))) {
return argument1.map((e, i) =>
JSON.stringify({ argument1: e[0], argument2: argument2[i][0] })
);
}
return JSON.stringify({ argument1, argument2 });
}
In this case, the following script can be also used.
function SAMPLE(argument1, argument2) {
if ([argument1, argument2].every((e) => Array.isArray(e))) {
return argument1.map((e, i) => SAMPLE(e[0], argument2[i][0]));
}
return JSON.stringify({ argument1, argument2 });
}
Note
As additional information, in the current stage, when the number of cells is large, the custom function cannot be used because of the current specification of the Spreadsheet. If an error related to the number of cells occurs in the custom function, please directly use the Google Apps Script instead of the custom function as follows.
const sheetName = "Sheet1"; // Please set your sheet name.
function onEdit(e) {
const { range } = e;
const sheet = range.getSheet();
if (sheet.getSheetName() != sheetName || range.columnStart > 2) return;
myFunction(sheet);
}
function myFunction(
sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
) {
const range = sheet.getRange("A1:B" + sheet.getLastRow());
const values = range
.getValues()
.map(([argument1, argument2]) => [
[argument1, argument2].includes("")
? [null]
: JSON.stringify({ argument1, argument2 }),
]);
range.offset(0, 2, values.length, 1).setValues(values);
}
When you test this script, please set your sheet name to sheetName, and run myFunction. By this, the result values are put into column “C”. And also, when you edit the cells “A1:A” and “B1:B”, the values of column “C” are updated by the function onEdit of the simple trigger.