Here, I'll document some code I found to be much easier or better to do in an appscripts context with modern Javascript.
You have a string that represents a range speciation using a1Notation. You need to get the starting row number. Or you need to get the last column. Or you need to get all of those things. You'd like to do it in a way that can be reused. And you wonder if some of the new syntax features makes this interesting.
Let's go over the skeleton of the function first:
function rangeBreak (range) {
const [sheet, cells] = range.split('!');
const regexp = /(^[A-Z]+)|([0-9]+$)/g;
...
return [sheet, row1, col1, row2, col2];
}We're going to return an array of all the things broken up, and at the start we see how it's going to be split up. In our example we'll use value "Sheet!A1:A10" for range.
Take the range.split('!') line. Since we know that the string will definitely have an exclamation mark and will demarcate the sheet name from the rest of it, we'll do that first. We make two variables, sheet and cells, which in our example will have the values "Sheet" and "A1:A10", respectively.
What's going on with this regular expression? It's going to be used to divide cells into its parts, so let's take a look at it: /(^[A-Z]+)|([0-9]+$)/g.
But didn't I get ahead of myself? Don't we have to split the cells by the ":" first? Yeah, but you know what, not all ranges will necessarily have a colon in there, so we'll pin that for the moment.
Back to the regular expression. It'll define how we can split between the "A" and "1" of a range like "A1". It relies on the fact that an a1 notation always begins ^ with at least one or more + of some capital alpha characters [A-Z], and ends with at least one + numeric digits [0-9] and the end of the string $. The | means "altercation" which I don't think is actually an "OR" operation (as is usually explained it to be), but will ensure that instead of getting one big match, we get two.
That was a dense paragraph! If you need help with learning and unpacking regular expressions, I find tools like Regexr invaluable. If you don't need such tools, what are you doing messing with Javascript shoudn't you be flexing your super human ability in some other more productive way?
Okay, so we're going to use that fabulous regular expression to divide the cells, so let's finally get to the middle part of the code:
function rangeBreak (range) {
...
const [first, second] = (_ => {
if (!cells.includes(':')) return [cells, ''];
return cells.split(':');
})();
let match = first.match(regexp);
const [col1, row1] = match;
match = second.length === 0 ? [null, null] : second.match(regexp);
const [col2, row2] = match
return [sheet, row1, col1, row2, col2];
}We want to make two variables first and second and fill them in with the stuff between the ":". But not all ranges have a colon! So the mini-function inside the body does a different thing depending on whether a ":" is present or not.
(The String.includes is a far better than using String.indexOf don't you think!?)
See [first, second], where I define variable values depending on the return values of a function inline like that? I use an an anonymous function call to determine whether or not second should be empty string or not. To me, this pattern works, as long as the body of the function is short and sweet, and readable.
So then I take first and use the regular expression above to split it into column and row values, A and 1, respectively. Repeat the process for second, but wait! I have to account for the case where second is an empty string. So I use an if statement in the form of ternary operator to unpack that the right way.
Finally, I return the values in the order I wish to, and voila.
This method will use classes to make this. To be written.