Google Sheets – How to Indicate a Range in a Variable

google sheetsgoogle-apps-script

I have a Google sheet in which I created a custom show/hide columns function in the Script Editor. I want to show/hide columns 1, 3, and 14 through 30.

This is the code I tried:

var colsCheck = [1,3,14:30];

But when I save, I get this error:

Missing ] after element list. (line 4, file "Code")

So I tried this:

var colsCheck = [1,3,14-30];

This doesn't give an error but it doesn't hide columns 14-30, either. Is there a way to indicate I want columns 14 through 30 without typing 14, 15, 16, 17...?

I am trying to Google this but I don't know the terminology well enough to find what I'm looking for.

Best Answer

Remember that what you're writing in the Script Editor is JavaScript, so your script must conform to JavaScript syntax.

14:30 does not, hence the error Missing ] after element list. 14-30 can very well be legal JavaScript, but it will be interpreted as 14 minus 30, so it will result in -16.

If you want an array with column numbers, you can populate it programmatically. First, let's list the single columns you want:

var colsCheck = [1, 3];

Then, let's loop through the numbers 14 to 30, inclusive, and append (push) those to the array:

for (var col = 14; col <= 30; col++) {
  colsCheck.push(col);
}

At this point, your array will be [1, 3, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30].

Now, I suspect you will have to expand your script a whole lot in order to accomplish what you really want. I suggest you try to familiarize yourself with JavaScript, through an online course like this one from Mozilla. Then, learn how JavaScript is used in Google Scripts with Google's tutorials.

Related Topic