1

I am from a non-computer background trying to execute a small code and am sharing some inputs which I think are relevant to this query (based on my evening of research). Can please someone help me create this code to execute the "purpose"?

Purpose- Use a checkbox to hide some columns which are not in order or next to one another.

Range in question: A:S

Checkbox location: Cell- "K47"

If above is true, Hide columns- A, B, D, F, G, H, I, L, M, P, Q, R, and S

If above is false, Show all columns A to S

Sheet name- "USER"

Regards, Kavita

Improve this question
0

3 Answers 3

Reset to default
1

Thanks for the inputs guys. I had a friend help me and here's what I got.

Note, ["ABHISHEK", "MITESH", "KAVITA", "MEENA","SUNIL"] are the sheet names where i wanted the switch to work . the switch location has been changed from K47 to E1

// @ts-nocheck function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); ["ABHISHEK", "MITESH", "KAVITA", "MEENA","SUNIL"].forEach(function (s) { var sheet = ss.getSheetByName(s); var st =sheet.getRange("E1").getValue(); if(st == true) { sheet.hideColumn(sheet.getRange("A:B")); sheet.hideColumn(sheet.getRange("D:D")); sheet.hideColumn(sheet.getRange("F:I")); sheet.hideColumn(sheet.getRange("L:M")); sheet.hideColumn(sheet.getRange("P:S")); } else { sheet.unhideColumn(sheet.getRange("A:B")); sheet.unhideColumn(sheet.getRange("D:D")); sheet.unhideColumn(sheet.getRange("F:I")); sheet.unhideColumn(sheet.getRange("L:M")); sheet.unhideColumn(sheet.getRange("P:S")); } } ) }

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This does certainly work, but note that this is hiding/unhiding the columns everytime you edit the sheet (hiding the columns already hidden or showing the columns already shown) even if you are not ticking/unticking the checkbox. I recommend you use the event object and limit your onEdit to specific cell like the other answer has shown. The other answer only hide/unhide when the checkbox is changed, not when other cells are edited.
0

In the Script Editor, write a function (a simple trigger) for onEdit(). In your code, evaluate if the checkbox was checked using sheet.getRange("K47") and range.getValue() (the sheet can be obtained using SpreadsheetApp.getActive().getSheetByName('USER')). Contain within an if-else statement the actions you listed and then run the script once to authorize. For resources on how to further translate your pseudocode into Apps Script, I would suggest https://www.w3schools.com/js/ and https://developers.google.com/apps-script/guides/sheets.

Improve this answer

Comments

0

User86530 points you to the correct direction. But I would prefer using the event object.

Code:

function onEdit(e) { var spreadsheet = e.source; var range = e.range; var value = e.value; // if sheet name is 'USER' and edited cell is 'K47' if(spreadsheet.getSheetName() == 'USER' && range.getRow() == 47 && range.getColumn() == 11) { sheet = spreadsheet.getSheetByName('USER'); if(value == "TRUE") { // Hide columns sheet.hideColumns(1, 2); // A-B sheet.hideColumns(4, 1); // D sheet.hideColumns(6, 4); // F-I sheet.hideColumns(12, 2); // L-M sheet.hideColumns(16, 4); // P-S } else { sheet.showColumns(1, 19); // A-S } } }

Output:

output

Note:

  • Add the code above as script for the sheet, save and try to edit the cell right away.
  • This will only get triggered if K47 is edited on USER

Resource:

Improve this answer

2 Comments

@User86530, that is where you are wrong. If you test it thoroughly by doing typeof e.value, it will return a string. If that happens, then if('FALSE') will return true. That is what we do not want to happen. e.value is a string, not a boolean object.
@User86530, getValue() returns a typed variable whereas e.value doesn't. Wasn't able to test other data like date but as far as the checkbox is concerned, it does return a string in e.value

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.