隐藏/取消隐藏基于单元格内容的行(Hide/unhide Rows Based on Content of a Cell)

隐藏/取消隐藏基于单元格内容的行(Hide/unhide Rows Based on Content of a Cell)

我想要我的当前脚本给我一个自定义菜单，侧边栏和隐藏基于值的单元格，如果单元格更改远离值，也包括取消隐藏。

例如

function onOpen() { SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .createMenu('x Tools') .addItem('Instructions', 'showSidebar') .addItem('Change y Form', 'openform') .addToUi(); } function showSidebar() { var html = HtmlService.createHtmlOutputFromFile('Page') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setTitle('Instructions') .setWidth(300); SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .showSidebar(html); } function openform() { showURL("https://docs.google.com/a/xxx/viewform") } function showURL(href) { var app = UiApp.createApplication().setHeight(50).setWidth(200); app.setTitle("Add Project to x"); var link = app.createAnchor('Open x Form ', href).setId("link"); app.add(link); var doc = SpreadsheetApp.getActive(); doc.show(app); } function onEdit(e) { Logger.log('e.value: ' + e.value); var cellEdited = e.range.getA1Notation(); Logger.log('cellEdited: ' + cellEdited); if (cellEdited === "C12" && e.value === "X") { var ss = SpreadsheetApp.getActiveSpreadsheet(); var theSheet = ss.getActiveSheet(); theSheet.hideRows(13, 4); }; }

E.g.

function onOpen() { SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .createMenu('x Tools') .addItem('Instructions', 'showSidebar') .addItem('Change y Form', 'openform') .addToUi(); } function showSidebar() { var html = HtmlService.createHtmlOutputFromFile('Page') .setSandboxMode(HtmlService.SandboxMode.IFRAME) .setTitle('Instructions') .setWidth(300); SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .showSidebar(html); } function openform() { showURL("https://docs.google.com/a/xxx/viewform") } function showURL(href) { var app = UiApp.createApplication().setHeight(50).setWidth(200); app.setTitle("Add Project to x"); var link = app.createAnchor('Open x Form ', href).setId("link"); app.add(link); var doc = SpreadsheetApp.getActive(); doc.show(app); } function onEdit(e) { Logger.log('e.value: ' + e.value); var cellEdited = e.range.getA1Notation(); Logger.log('cellEdited: ' + cellEdited); if (cellEdited === "C12" && e.value === "X") { var ss = SpreadsheetApp.getActiveSpreadsheet(); var theSheet = ss.getActiveSheet(); theSheet.hideRows(13, 4); }; }

My problem is I tried adding a 2nd if statement - didnt get error but just didnt unhide. Tried using else and got error. Basic Idea is if the cell c12 is X then rows 13,14,15,16 should be hidden, if it is anything else the cells should be visible. What I have now works for the hiding however it doesnt unhide the cells if you change the X to something else.

最满意答案

你只需要以相同的方式取消隐藏，只需将表达式更改为！==“X”，并使用.showRows方法。

if (cellEdited === "C12" && e.value !== "X") { var ss = SpreadsheetApp.getActiveSpreadsheet(); var theSheet = ss.getActiveSheet(); theSheet.showRows(13, 4); }

You'll just have to unhide in the same way, just changing the expression to !== "X", and using the .showRows method.

if (cellEdited === "C12" && e.value !== "X") { var ss = SpreadsheetApp.getActiveSpreadsheet(); var theSheet = ss.getActiveSheet(); theSheet.showRows(13, 4); }