google-sheetsgoogle-apps-script

Hide an array of columns in Google App Scripts

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);
}
Solution

  • 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?

    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);

    References:

    Added:

    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);
}