Beginner programmer here, apologies in advance if my vocabulary is not really accurate while explaining my issue.
I have a script that creates a custom menu (view) and hides different set of columns based on the view you want to see.
How can I change the script so instead of typing all my columns I can get an array of columns (in this case array [1 to 4] and [7 to 13])?
Below the particular section of my code that I'd like to change
function hideColumnsAll30() {
const obj = [{ sheetName: "Planning / Tracking", hide: [1, 2, 3, 4, 7, 8, 9, 11, 12, 13 ] }, { sheetName: "Resources", hide: [2, 4, 5, 9]}, {sheetName: "Documentation", hide:[2, 4, 5, 9]} ];
sample_(obj);
}
And here is the full script if it helps solving my issue
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Custom View')
.addItem('All - 30 days', 'hideColumnsAll30')
.addItem('All - 1 year', 'hideColumnsAll1y')
.addItem('Unhide All', 'showColumns')
.addToUi();
function showColumns() {
const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
showColumnsInAllSheets_(sheets);
}
function hideColumnsAll30() {
const obj = [{ sheetName: "Planning / Tracking", hide: [1, 2, 3, 4, 7, 8, 9, 11, 12, 13 ] }, { sheetName: "Resources", hide: [2, 4, 5, 9]}, {sheetName: "Documentation", hide:[2, 4, 5, 9]} ];
sample_(obj);
}
function hideColumnsAll1y() {
const obj = [{ sheetName: "Planning / Tracking", hide: [1, 3, 4, 5, 6, 7, 8, 9, 11, 12, ] }, { sheetName: "Resources", hide: [2, 4, 5, 9]}, {sheetName: "Documentation", hide:[2, 4, 5, 9]} ];
sample_(obj);
}
From your following replying,
I need to type all the columns manually -> [1, 2, 3, 4, 7, 8, 9, 10, 11, 12, 13] I would like to have 2 arrays, like this -> [1 - 4], [7 -13]
I guessed that you wanted to retrieve an array of [1, 2, 3, 4, 7, 8, 9, 10, 11, 12, 13]
by using 2 arrays like [1 - 4], [7 -13]
. If my understanding is correct, how about the following sample script?
const input = [[1, 4], [7, 13]]; // 1st and 2nd element of each array are start and end values, respectively.
const res = input.flatMap(([start, end]) => [...Array(end)].map((_, i) => i + 1).slice(start - 1, end));
console.log(res);
[ 1, 2, 3, 4, 7, 8, 9, 10, 11, 12, 13 ]
is obtained.From your following reply,
I actually found similar piece of code to add to my script online. However it does not fit my case as I need to give the name of each sheets where I want to hide columns.
And, in your provided script, I found the following message.
I have other speadsheets with 1000+ columns, so I cannot hide them manually from 1 to 1000. This is the reasons why I'm trying to include several arrays in my script
In this case, is the following script your expected script?
const columns = input => input.flatMap(([start, end]) => [...Array(end)].map((_, i) => i + 1).slice(start - 1, end));
function hideColumnsAll30() {
const obj = [{ sheetName: "Planning / Tracking", hide: columns([[1, 4], [7, 13]]) }, { sheetName: "Resources", hide: [2, 4, 5, 9]}, {sheetName: "Documentation", hide:[2, 4, 5, 9]} ];
sample_(obj);
}
hide: columns([[1, 4], [7, 13]])
, hide: [ 1, 2, 3, 4, 7, 8, 9, 10, 11, 12, 13 ]
is created.