I've written a custom formula that takes in 3 unique identifiers and a range of columns. The goal is to make a new row for each combination of 3 columns in this range of columns (e.g. 1 row for A,B,C - 1 row for D,E,F ...) and append the identifiers to each row
This mostly works but I've run into a few issues, the main one of which is that I have a array that holds my return arrays (as each inner array corresponds to a row) - but this array just stops existing after a forEach function where I insert the required values.
Running this just gets me a reference error and the coloring scheme indicates that the
return returnArray line is trying to access a element that does not exist.
// Formula accepts 3 unique identifiers and an array of multiple strings
// It splits the array into combinations of 3 strings in order
// It then turns it into new arrays of [identifier, identifier2, identifier3, string1, string2, string3]
// These are put into 1 array
// This array is returned and each inner-array becomes its own row
function dupeRowsForUniqueCombinations(documentID, documentVersion, documentPublisher, arrayOfCombinationsOf3Rows) {
if (documentID && documentVersion && documentPublisher && arrayOfCombinationsOf3Rows) {
let returnArray = [];
let arrayOfArray = [];
let returnArrayInner = [];
arrayOfCombinationsOf3Rows = arrayOfCombinationsOf3Rows.join(","); // should not be needed, but gsheets treats this as an array and not an array - arrayOfCombinationsOf3Rows[n] always fails, .indexOf always returns -1, isArray returns true
arrayOfCombinationsOf3Rows = arrayOfCombinationsOf3Rows.split(","); // converting this back to a normal array
let chunkSize = 3;
for (let i = 0; i < arrayOfCombinationsOf3Rows.length; i += 3) {
let chunk = arrayOfCombinationsOf3Rows.slice(i, i + chunkSize);
arrayOfArray.push(chunk)
}
arrayOfArray.forEach(function (arrayOf3Rows) {
if (arrayOf3Rows[0] != '""' && arrayOf3Rows[0].length > 1) {
returnArrayInner = [documentID, documentVersion, documentPublisher, ...arrayOf3Rows]
returnArray.push(returnArrayInner)
}
}
)
return returnArray
} else {
return "Missing Needed Values"
}
}
Note: Another issue I ran into is that my value of arrayOfCombinationsOf3Rows is a range (e.g. a3:z3) - this should be an array but doesn't seem to be a normal one. All the checks (Array.isArray(),stringify,etc...) seem to indicate it's an array but calling any element within always fails. arrayOfCombinationsOf3Rows[1] is always blank, and arrayOfCombinationsOf3Rows.indexOf(existingElement) is always -1. I got around this in JS by joining then splitting that array - but then ran into this issue.
What am I doing wrong here?
