I was given a custom function to add to the spreadsheet I’m building but it doesn’t quite do what I now need it to do. I have a basic understanding of JavaScript but since I didn’t write this code, I’m having a little trouble parsing out what pieces do what.
Currently, the function grabs values from the range I select, separates any values separated by commas, and puts all the resulting values on a second tab. For example it takes ValueA (ValueB) for name and gender and puts the data into A2 and B2 on the second tab. I’m looking to add to the function so that I can have ValueA (ValueB, ValueC) added to A2, B2, and C2 on the second tab.
function getAuthorList(range) {
if (range.map) {
var count = 0;
var field = '';
var author = '';
var authors = [];
for(var i = 0; i < range.length; i++ ){
field = range[i][0];
var pos = 0, posNext = 0;
while( field.length > 0 ){
if( field.indexOf(',') > 0 ){
posNext = field.indexOf(',');
author = field.substring( pos, posNext );
field = field.substring( posNext + 1 , field.length );
} else {
posNext = field.length;
author = field.substring( pos, posNext );
field = field.substring( posNext, field.length );
}
var start = author.indexOf('(');
var end = author.indexOf(')');
var name = '';
if( start > 0 ){
name = author.substring(0, start - 1);
if( name.indexOf(' ') == 0 ){
name = name.substring(1, name.length );
}
} else {
name = author;
}
var gender = '';
if( start > 0 && end > 0 ){
gender = author.substring(start + 1, end);
}
authors.push([ name, gender]);
}
}
if( authors.length < 1 ){
return "";
}
return authors;
}
}
I know I need to duplicate & then adjust the gender section but I’m stuck on what pieces would need to be adjusted and how.
1
You can do that with a plain vanilla spreadsheet formula:
=arrayformula(iferror(split(Sheet1!A2:A, "(),")))
To do the same with an Apps Script custom function, use Array.map() and String.split(), like this:
/**
* Splits a column of strings like 'ValueA (ValueB)' or 'ValueA (ValueB, ValueC)'
* into two or three columns like 'ValueA', 'ValueB', 'ValueC'.
* Ignores any additional columns.
*
* @param {Sheet1!A2:A} values The column of text strings to split into three columns.
* @customfunction
*/
function getAuthorList(values) {
// version 1.0, written by --Hyde, 1 September 2024
// - see /a/78937004/13045193
if (!Array.isArray(values)) values = [[values]];
return values.map(row => String(row[0]).split(/[,()]/).map(v => v.trim()))
.map(row => row.slice(0, 3));
}
See
Array.map(),
String.split() and
Array.slice().
Below is a proposal based on the use of regular expression instead of String.prototype.indexOf and String.prototype.substring. test_1 and test_2 are testing functions to test using a multiple cells (A1:A2) and single cell (A1). Remember that this is a custom function, it’s intended to be used in Google Sheets formula =getAuthorList(A1:A2) or =getAuthorList(A1)
Sample input values
| A | |
|---|---|
| 1 | García Márques (Novela) |
| 2 | Octavio Paz (Ensayo, Poesía) |
Code.gs
/**
* Multiple cells
* Expected result: string[][]
*/
function test_1() {
const name = 'Sheet1';
const a1Notation = 'A1:A2';
// Expected values [["García Márquez (Novela)"], ["Octavio Paz (Ensayo, Poesía)"]];
const values = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName(name)
.getRange(a1Notation)
.getValues();
const result = getAuthorList(values);
Logger.log(result);
}
/**
* Single cell
* Expected result: string[][]
*/
function test_2() {
const name = 'Sheet1';
const a1Notation = 'A1';
// Expected value "García Márquez (Novela)"
const value = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName(name)
.getRange(a1Notation)
.getValue();
const result = getAuthorList(value);
Logger.log(result);
}
/** type {string[]|string[][]} */
const authorList = [];
/**
* @customfunction
*/
function getAuthorList(value) {
if (Array.isArray(value)) {
// Split row values
value.flat().forEach(v => getAuthorList(v));
// Get the max number of elements by row
const columns = authorList.reduce((n, row) => Math.max(n, row.length), 0);
// Add empty strings to make a square matrix;
authorList.forEach(row => { for (i = 0; i < columns - row.length; i++) { row.push('') } });
} else {
const [match, name, genre] = /([^()]+)(([^()]+,?[^)]*))?/.exec(value);
authorList.push([name, ...genre.split(',').map(s => s.trim())]);
}
return authorList;
}
The above might be refined to improve the handling of unexpected input values.