英文:
Google Apps Script to Hide a Row when both of the checkboxes in Col15 and Col18 are checkmarked
问题
在图像中,第一个复选框位于Col15,第二个复选框位于Col18。我需要的是,当两个复选框都被选中时,行应该自动隐藏!
我对此非常陌生,所以无法真正理解如何实现我的目标。
英文:
In the image, the first checkbox is in Col15 and second checkbox is in Col18. I need that when both the boxes are checkmarked, the row should autohide!
I am very new at it, so could not really get the hang of how to achieve my objective.
答案1
得分: 0
以下是您要求的内容的翻译:
- 原始脚本的翻译部分:
如果我正确理解了任务,那么您可以尝试使用以下脚本隐藏行(其中列15和18中的两个单元格都被选中)...
function onEdit(e){
if (e.range.columnStart == 15 && e.source.getActiveSheet().getRange(e.range.rowStart,18).getValue() == true){
const sheetName = e.source.getActiveSheet().getName();
var currentRow = e.range.rowStart;
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const value15 = sheet.getRange(currentRow,15).getValue();
if (value15){
sheet.hideRows(currentRow);
}
} else if (e.range.columnStart == 18 && e.source.getActiveSheet().getRange(e.range.rowStart,15).getValue() == true){
const sheetName = e.source.getActiveSheet().getName();
var currentRow = e.range.rowStart;
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const value18 = sheet.getRange(currentRow,18).getValue();
if (value18){
sheet.hideRows(currentRow);
}
}
}
- 简化版本脚本的翻译部分:
function onEdit(e){
if (e.range.columnStart == 15 || e.range.columnStart == 18){
if (e.source.getActiveSheet().getRange(e.range.rowStart,15).getValue()==true && e.source.getActiveSheet().getRange(e.range.rowStart,18).getValue()==true){
e.source.getActiveSheet().hideRows(e.range.rowStart);
}
}
}
- 用于通过菜单按钮执行删除所有在列15和18中选中字段的行的更新:
function onOpen(e) {
var ui = SpreadsheetApp.getUi()
.createMenu("菜单")
.addItem("隐藏已选项", "hideChecked")
.addToUi();
}
function hideChecked(){
const activeSheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetName();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(activeSheetName);
const lastRow = sheet.getLastRow();
for (var i = lastRow; i > 0 ; i--)
{
if (sheet.getRange(i,15).getValue()==true && sheet.getRange(i,18).getValue()==true){
sheet.hideRows(i);
}
}
}
请注意,代码中的特殊字符如&
和"
已被还原为原始字符。
英文:
If I understood the task correctly, then you can try to hide the row (where both cells in column 15 and 18 are checked) with this script...
function onEdit(e){
if (e.range.columnStart == 15 && e.source.getActiveSheet().getRange(e.range.rowStart,18).getValue() == true){
const sheetName = e.source.getActiveSheet().getName();
var currentRow = e.range.rowStart;
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const value15 = sheet.getRange(currentRow,15).getValue();
if (value15){
sheet.hideRows(currentRow);
}
} else if (e.range.columnStart == 18 && e.source.getActiveSheet().getRange(e.range.rowStart,15).getValue() == true){
const sheetName = e.source.getActiveSheet().getName();
var currentRow = e.range.rowStart;
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const value18 = sheet.getRange(currentRow,18).getValue();
if (value18){
sheet.hideRows(currentRow);
}
}
}
This is a little bit simplified version:
function onEdit(e){
if (e.range.columnStart == 15 || e.range.columnStart == 18){
if (e.source.getActiveSheet().getRange(e.range.rowStart,15).getValue()==true && e.source.getActiveSheet().getRange(e.range.rowStart,18).getValue()==true){
e.source.getActiveSheet().hideRows(e.range.rowStart);
}
}
}
Update for delete all rows with checked fields in column 15 and 18, executed with menu button...
function onOpen(e) {
var ui = SpreadsheetApp.getUi()
.createMenu("Menu")
.addItem("Hide Checked", "hideChecked")
.addToUi();
}
function hideChecked(){
const activeSheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetName();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(activeSheetName);
const lastRow = sheet.getLastRow();
for (var i = lastRow; i > 0 ; i--)
{
if (sheet.getRange(i,15).getValue()==true && sheet.getRange(i,18).getValue()==true){
sheet.hideRows(i);
}
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论