英文:
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);
    }
  }
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论