Google Apps Script – Combine Multiple Ranges with getRange to Clear Specific Cells

google-apps-script

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit#gid=1418057437

I use this sheet to enter data of patients in (Data_Entry) tab. Specific rows are titles and should not be cleared whenever I clear contents of all cells in the sheet/tab. Like Rows 1-4 and rows 20-21. So I made this script to clear specific cells but it seems very basic and it has a problem whenever somebody inserts a new row in between, the whole range needs to be adjusted.

The script has three functions: adding_menu, making borders of query results in (All_patients)tab and ClearContents of (Data_Entry) tab.

So my question is about the last function (ClearContent)

 function onOpen() {

    var menuItems = [
    {name: 'ClearContents', functionName: 'ClearContents'},
    {name: 'Borders', functionName: 'borders'}
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Weekened_Functions', menuItems);
 
 
}

function borders() {
  var range = SpreadsheetApp.getActive().getSheetByName('All_Patients').getDataRange();
  var cell = SpreadsheetApp.getActive().getSheetByName('All_Patients').getRange("B50:H200")
  cell.setBorder(false, false, false, false, false, false);
  var values = range.getValues();
  for (var i = 2; i < values.length; i++) {
    for (var j = 1; j < values[i].length; j++) {
      
        range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true);
      }
    }
 
  }
 
function ClearContents() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Data_Entry');
  sheet.getRange('B5:F19').clearContent(),
  sheet.getRange('B22:F34').clearContent();
  sheet.getRange('B37:F49').clearContent();
  sheet.getRange('B52:F64').clearContent();
  sheet.getRange('B67:F83').clearContent();
  sheet.getRange('B86:F102').clearContent();
  sheet.getRange('B105:F119').clearContent();
  sheet.getRange('B122:F133').clearContent();
  sheet.getRange('B136:F146').clearContent();
  sheet.getRange('B149:F160').clearContent();
  sheet.getRange('B163:F174').clearContent();
  sheet.getRange('B177:F188').clearContent();
  sheet.getRange('B191:F202').clearContent();
  sheet.getRange('B205:F216').clearContent();
  sheet.getRange('B219:F230').clearContent();
  sheet.getRange('B247:F257').clearContent();
  sheet.getRange('B260:F269').clearContent();

  sheet.getRange('M5:M19').clearContent(),
  sheet.getRange('M22:M34').clearContent();
  sheet.getRange('M37:M49').clearContent();
  sheet.getRange('M52:M64').clearContent();
  sheet.getRange('M67:M83').clearContent();
  sheet.getRange('M86:M102').clearContent();
  sheet.getRange('M105:M119').clearContent();
  sheet.getRange('M122:M133').clearContent();
  sheet.getRange('M136:M146').clearContent();
  sheet.getRange('M149:M160').clearContent();
  sheet.getRange('M163:M174').clearContent();
  sheet.getRange('M177:M188').clearContent();
  sheet.getRange('M191:M202').clearContent();
  sheet.getRange('M205:M216').clearContent();
  sheet.getRange('M219:M230').clearContent();
  sheet.getRange('M247:M257').clearContent();
  sheet.getRange('M260:M269').clearContent();

  sheet.getRange('H5:H19').clearContent(),
  sheet.getRange('H22:H34').clearContent();
  sheet.getRange('H37:H49').clearContent();
  sheet.getRange('H52:H64').clearContent();
  sheet.getRange('H67:H83').clearContent();
  sheet.getRange('H86:H102').clearContent();
  sheet.getRange('H105:H119').clearContent();
  sheet.getRange('H122:H133').clearContent();
  sheet.getRange('H136:H146').clearContent();
  sheet.getRange('H149:H160').clearContent();
  sheet.getRange('H163:H174').clearContent();
  sheet.getRange('H177:H188').clearContent();
  sheet.getRange('H191:H202').clearContent();
  sheet.getRange('H205:H216').clearContent();
  sheet.getRange('H219:H230').clearContent();
  sheet.getRange('H247:H257').clearContent();
  sheet.getRange('H260:H269').clearContent();
};

1\ how to combine these ranges in simple formula?

2\ Is there a way to add exception like: (clear contents of all cells except Column M and Column A and cells containing text "Ward" or "room"or problem list" or "recommendations"?

Best Answer

Solved by using getRangList , assign any range list to a variable then use these variables anywhere

var ranges=sheet.getRangeList(['B5:I21' ,'B24:I36' , 'B39:I53' , 'B56:I68',  'B71:I90' , 'B93:I109' , 'B112:I126' , 'B129:I140' , 'B143:I157' , 'B160:I174'  ,'B177:I188' , 'B191:I204' , 'B207:I220' , 'B223:I234' , 'B237:I248','B251:I262','B265:I275','B278:I287','B5:I21']);

ranges.clearcontent();

Code

var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActive();

function onOpen() {
  app.createMenu("Ranges")
    .addItem("Set ranges", "setRanges")
    .addItem("Delete ranges", "deleteRanges")
    .addToUi();
}

function setRanges() {
  var text = app.prompt("Give string", "Like A1:A10, Sheet2!A4:B5, This is a test!A:A", app.ButtonSet.OK_CANCEL); 
  if(text.getSelectedButton() == app.Button.OK) {
    var ranges = text.getResponseText().split(",");  

    for(var i = 0, iLen = ranges.length; i < iLen; i++) {
      var value, range, index, sname, split, sh;
      value = ranges[i].trim(), split = value.split("!");

      switch(split.length) {
        case 1:
          range = value;
          sh = ss.getActiveSheet();
          break;
        case 2:
          range = value;
          sh = ss.getSheetByName(String(split[0].trim()));
          break;
        default:
          index = value.lastIndexOf("!");
          range = "'" + value.slice(0, index) + "'!" + split[split.length - 1].trim();
          sh = ss.getSheetByName(String(value.slice(0, index)));
          break; 
      }

      try {
        sh.getRange(String(range))
          .protect()
          .setDescription("Range" + i);
      } catch(e) {
        throw "Range: " + (i) + " doesn't exist";
      }
    }
  }
}

function deleteRanges() {
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var j = 0, jLen = protections.length; j < jLen; j++) {
    protections[j].remove();
  }
}

Explained

The first two variables are usable throughout the code. The onOpen() function creates a menu item upon file opening and adds two items. The setRanges() function initially creates a popup box (prompt) to capture the string. The response is then split using the comma as delimiter.

Now we need to iterate though the different ranges in order to prepare the protected ranges. Within the iteration, the response is validated once more by splitting it using the exclamation mark as delimiter. This will yield either one or more results. The switch method will determine the buildup of the sheet name and the range.

Now that the sheet has been defined, the range (getRange()) can be protected (protect()) and the try / catch allows for some error handling.

Screenshot

enter image description here

Note

This script can also cope with the following sheet name examples:

This is a test
Test!Test

Example

I've created an example file for you: setting up multiple protected ranges
Add this script in the script editor (Tools > Script editor) and press the bug button: enter image description here
Now you can use the script.

Best Answer

Related Solutions

Google-sheets – How to Modify Matched Cells Across Multiple Sheets with Google Script

Google Sheets – How to Set Multiple Protected Ranges with One Input

Code

Explained

Screenshot

Note

Example

Related Topic