如何使用Apps脚本从Google表格中删除特定表格范围内的所有不受保护的行 [英] How to delete all unprotected rows in a range of particular sheets from Google Sheets using Apps Scripts

问题描述

我的Google表格中有几个保护范围，例如A2到F40，然后是A45到F90.我使用按钮(菜单-子菜单)执行此受保护范围.但是，在一系列特定工作表中，几乎没有几行数据不受保护，这些工作表的名称同时使用字母和数字(例如ICT4113，MATH4104或HUM4119).很少有其他表的名称仅包含字母，不需要此功能.

I have several protected range in a sheet of my Google Sheets for example, A2 to F40 and then A45 to F90. I do this protected range using a button (menu--submenu). But there are few rows of data unprotected throughout a range of particular sheets having names using alphabets and numbers together (e.g. ICT4113, MATH4104 or HUM4119). There are few other sheets having names containing only alphabets which does not require this function.

如何在函数onOpen()中的整个特定工作表范围内删除那些不受保护的数据行，而无需任何触发器.刚要删除时，我将按一个子菜单.

How can I delete those unprotected rows of data throughout a range of specific sheets inside function onOpen() without any trigger. Just when I want to delete, I will press a sub-menu.

推荐答案

我相信您的目标如下.

• 您要使用Google Apps脚本完全删除特定工作表没有保护范围的行.
  • 要实现这一目标，有几个具体的工作表.
  • You want to completely delete the rows which has no protected ranges for the specific sheets using Google Apps Script.
    • There are several specific sheets you want to achieve this.

    为此，这个答案如何?该示例脚本的流程如下.

    For this, how about this answer? The flow of this sample script is as follows.

    1. 检索数据范围.
    2. 在受保护范围内创建一个对象.这用于从清除的行中删除.
    3. 创建范围列表以使用该对象清除行.
    4. 删除不包含受保护范围的行的行.

    示例脚本:

    请将以下脚本复制并粘贴到脚本编辑器中，并设置工作表名称，然后重新打开电子表格.这样，设置了自定义菜单.当您运行myFunction时，将删除没有受保护范围的行的单元格.

    Sample script:

    Please copy and paste the following script to the script editor and set the sheet name, and reopen the Spreadsheet. By this, the custom menu is set. When you run myFunction, the cells of rows without the protected range are deleted.

    function onOpen() {
      SpreadsheetApp.getUi().createMenu('Custom Menu').addItem('run script', 'myFunction').addToUi();
    }
    
    function myFunction() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      ss.getSheets().filter(s => /\d/.test(s.getSheetName())).forEach(sheet => {
        // 1. Retrieve data range.
        const dataRange = sheet.getDataRange();
    
        // 2. Create an object from the protected range. This is used for removing from the cleared rows.
        const protectedRanges = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).map(e => {
          const r = e.getRange();
          const start = r.getRow();
          return {start: start, end: r.getNumRows() + start - 1};
        });
    
        // 3. Create range list for clearing rows using the object.
        let rangeList = [];
        for (let r = 2; r <= dataRange.getNumRows(); r++) {
          let bk = false;
          for (let e = 0; e < protectedRanges.length; e++) {
            if (protectedRanges[e].start == r) {
              r = protectedRanges[e].end;
              bk = true;
              break;
            }
          }
          if (!bk) rangeList.push(`A${r}:${r}`);
        }
    
        // 4. Delete the rows without the rows of the protected ranges.
        sheet.getRangeList(rangeList).getRanges().reverse().forEach(r => sheet.deleteRow(r.getRow()));
      });
    }
    

    参考文献:

    • G Suite中的自定义菜单
    • getProtections(类型)
    • getRangeList(a1Notations)

    References:

    • Custom Menus in G Suite
    • getProtections(type)
    • getRangeList(a1Notations)

    这篇关于如何使用Apps脚本从Google表格中删除特定表格范围内的所有不受保护的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！