2

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"?

1 Answer 1

5

Solved by using getRangeList() , 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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.