Google Apps Script 用于在 Col15 和 Col18 中的复选框都被选中时隐藏一行。

huangapple go评论67阅读模式
英文:

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!

enter image description here

I am very new at it, so could not really get the hang of how to achieve my objective.

答案1

得分: 0

以下是您要求的内容的翻译:

  1. 原始脚本的翻译部分:
如果我正确理解了任务那么您可以尝试使用以下脚本隐藏行其中列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);
    }
  }
}
  1. 简化版本脚本的翻译部分:
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);
    }
  } 
}
  1. 用于通过菜单按钮执行删除所有在列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);
    }
  }
}

huangapple
  • 本文由 发表于 2023年3月7日 18:28:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/75660766.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定