1

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
  • Can you clean up your code here and in the answer to shorten the list of cells to just 3? There's no point in listing more. Commented Nov 6, 2023 at 19:07

1 Answer 1

7

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 you have read our privacy policy.

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