Hide an array of columns in Google App Scripts-CodePudding

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

Thanks in advance for your help!

CodePudding user response：

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

When this script is run, an array of [ 1, 2, 3, 4, 7, 8, 9, 10, 11, 12, 13 ] is obtained.

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

At hide: columns([[1, 4], [7, 13]]), hide: [ 1, 2, 3, 4, 7, 8, 9, 10, 11, 12, 13 ] is created.

CodePudding user response：

Suggestion

In your script I am not sure where you will use the arrays 1-4 and 7-13 since in the scripts specially on the part { sheetName: "Planning / Tracking", hide: [1, 2, 3, 4, 7, 8, 9, 11, 12, 13 ] } you are skipping number 10 here and on the other function you skipped 2.

Here's a suggestion on how you can use an array to your function. You can just add a new variable outside the functions to make it global variables, here is where you will insert the array values you want. You can then access these variables in any of your functions so you only have to edit one line in case you will adjust the columns.

Try this code:

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

//Declare arrays here***
//Put in array the columns for Resources and Documentation sheets
var resDocArray = [2,4,5,9];
//Put in array the columns for the planning/tracking tab for 2 different functions
var colAll30d = [1, 2, 3, 4, 7, 8, 9, 11, 12, 13];
var colAll1y = [1, 3, 4, 5, 6, 7, 8, 9, 11, 12]
//***

function showColumns() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  showColumnsInAllSheets_(sheets);
};

function hideColumnsAll30() {
  const obj = [{ sheetName: "Planning / Tracking", hide: colAll30d }, { sheetName: "Resources", hide: resDocArray}, {sheetName: "Documentation", hide:resDocArray} ];
  sample_(obj);
};

function hideColumnsAll1y() {
  const obj = [{ sheetName: "Planning / Tracking", hide: colAll1y }, { sheetName: "Resources", hide: resDocArray}, {sheetName: "Documentation", hide:resDocArray} ];
  sample_(obj);
};

function sample_(obj) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  showColumnsInAllSheets_(sheets);
  const sheetObj = sheets.reduce((o, s) => (o[s.getSheetName()] = s, o), {});
  obj.forEach(({ sheetName, hide }) => {
    if (sheetObj[sheetName]) {
      hide.forEach(h => sheetObj[sheetName].hideColumns(h, 1));
    }
  });
};

function showColumnsInAllSheets_(sheets) {
  sheets.forEach(s => s.showColumns(1, s.getMaxColumns()));
};

You can also add the arrays the way Tanaike suggested.

Let me know if this helps!

Reference: Hide columns in multiple sheets