Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-macros

How to delete all unprotected rows in a range of particular sheets from Google Sheets using Apps Scripts

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.

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.

Solution

  • I believe your goal as follows.

    • 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.
    • You want to run the script by the custom menu on Google Spreadsheet.

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

    1. Retrieve data range.
    2. Create an object from the protected range. This is used for removing from the cleared rows.
    3. Create range list for clearing rows using the object.
    4. Delete the rows without the rows of the protected ranges.

    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()));
  });
}

    References: