Google Script to Clear multiple ranges.

Question

I have many spreadsheets that get filled out weekly and closed at the start of the new week. I've written a script to go through and clear out all kinds of ranges in a lot of different sheets. See code below. My question is there a better way to do this instead of having to clear ranges one section at a time and have a million clearContent functions? I cannot figure out how to write a function to clearContent where I can list many ranges all in the same function.

function startWeek() {

  var confirm = Browser.msgBox('Did you **Close the Week** first?','Pressing YES will   clear your week', Browser.Buttons.YES_NO);

 if(confirm=='no'){Logger.log('The user clicked "NO."')};
 if(confirm=='yes'){
  var sheet = SpreadsheetApp.getActive().getSheetByName('INVOICE LOG');
    sheet.getRange('A3:M47').clearContent();
  var sheet = SpreadsheetApp.getActive().getSheetByName('DAILY INVENTORY');
    sheet.getRange('C5:C8').clearContent();
    sheet.getRange('D6:I8').clearContent();
    sheet.getRange('C10:I10').clearContent();
    sheet.getRange('C13:C16').clearContent();
    sheet.getRange('D14:I16').clearContent();
    sheet.getRange('C18:I18').clearContent();
    sheet.getRange('C21:C24').clearContent();
    sheet.getRange('D22:I24').clearContent();
    sheet.getRange('C26:I26').clearContent();
    sheet.getRange('C29:C32').clearContent();
    sheet.getRange('D30:I32').clearContent();
    sheet.getRange('C34:I34').clearContent();
    sheet.getRange('C37:C40').clearContent();
    sheet.getRange('D38:I40').clearContent();
    sheet.getRange('C42:I42').clearContent();
    sheet.getRange('C45:C48').clearContent();
    sheet.getRange('D46:I48').clearContent();
    sheet.getRange('C50:I50').clearContent();
    sheet.getRange('C55:C58').clearContent();
    sheet.getRange('D56:I58').clearContent();
    sheet.getRange('C60:I60').clearContent();
    sheet.getRange('C63:C66').clearContent();
    sheet.getRange('D64:I66').clearContent();
    sheet.getRange('C68:I68').clearContent();
    sheet.getRange('C71:C74').clearContent();
    sheet.getRange('D72:I74').clearContent();
    sheet.getRange('C76:I76').clearContent();
    sheet.getRange('C79:C82').clearContent();
    sheet.getRange('D80:I82').clearContent();
    sheet.getRange('C84:I84').clearContent();
    sheet.getRange('C87:C90').clearContent();
    sheet.getRange('D88:I90').clearContent();
    sheet.getRange('C92:I92').clearContent();
    sheet.getRange('C95:C98').clearContent();
    sheet.getRange('D96:I98').clearContent();
    sheet.getRange('C100:I100').clearContent();
    sheet.getRange('C105:C108').clearContent();
    sheet.getRange('D106:I108').clearContent();
    sheet.getRange('C110:I110').clearContent();
    sheet.getRange('C113:C116').clearContent();
    sheet.getRange('D114:I116').clearContent();
    sheet.getRange('C118:I118').clearContent();
    sheet.getRange('C121:C124').clearContent();
    sheet.getRange('D122:I124').clearContent();
    sheet.getRange('C126:I126').clearContent();
    sheet.getRange('C129:C132').clearContent();
    sheet.getRange('D130:I132').clearContent();
    sheet.getRange('C134:I134').clearContent();
    sheet.getRange('C137:C140').clearContent();
    sheet.getRange('D138:I140').clearContent();
    sheet.getRange('C142:I142').clearContent();
    sheet.getRange('C145:C148').clearContent();
    sheet.getRange('D146:I148').clearContent();
    sheet.getRange('C150:I150').clearContent();
  var sheet = SpreadsheetApp.getActive().getSheetByName('FOOD INVENTORY');
    sheet.getRange('D5:F615').clearContent();
  var sheet = SpreadsheetApp.getActive().getSheetByName('LIQUOR INVENTORY');
    sheet.getRange('D6:G361').clearContent();
    sheet.getRange('E365:G520').clearContent();
    sheet.getRange('D524:G573').clearContent();
  var sheet = SpreadsheetApp.getActive().getSheetByName('DAILY SALES SHEET');
    sheet.getRange('B4:H10').clearContent();
    sheet.getRange('B12:H12').clearContent();
    sheet.getRange('B14:H20').clearContent();
    sheet.getRange('B22:H27').clearContent();
    sheet.getRange('B29:H30').clearContent();
    sheet.getRange('B33:H34').clearContent();
    sheet.getRange('B36:H38').clearContent();
    sheet.getRange('B43:H44').clearContent();
  var sheet = SpreadsheetApp.getActive().getSheetByName('LAST WEEK INVENTORY');
    sheet.getRange('E3:E9').clearContent();
  var sheet = SpreadsheetApp.getActive().getSheetByName('SAFE AUDIT');
    sheet.getRange('C3:P11').clearContent();
    sheet.getRange('C14:P18').clearContent();
    sheet.getRange('C22:P22').clearContent();
 var destination = SpreadsheetApp.getActiveSpreadsheet();
 var name = Browser.inputBox('New Week', 'Enter Pub Name & WE Date (ex. SandwichWE02-02-14)', Browser.Buttons.OK);
   destination.rename(name)

}; }

Are the cells that aren't being cleared static values (eg headers)? — AdamL
Yes, they're mostly inventory items that are counted each week. Another sheet is a sales sheet where I have all formulas protected to prevent user mistakingly deleting them — Sean
yeah clear everything once on each sheet, then set the values that should remain, which should probably be on an extra hidden template sheet. — Bryan P
That was my last resort, so thank you! I was hoping I would be able to do something like sheet.getRange('C1:F7,C9:F40'). Something along those lines, but it will not allow me to enter in more than 1 range. — Sean
It will be interesting to see if GAS releases a method for multiple, non-contiguous range selection, now that manual multi-selection is possible: plus.google.com/u/0/+GoogleDrive/posts/1JRvD9HEFJa — AdamL

Ryan Roth Ryan Roth · Accepted Answer · 2014-02-14T21:40:03

Unfortunately there is not currently a Apps Script method to clear multiple ranges with a single function call.

What you might find a little easier to manage is to instead define one or more data structures (even a simple array) containing the Ranges in question. Then you can clear them all by simply looping over the data structure and calling clearContent() on each Range. This would separate the work of keeping track of your Ranges from the work of clearing them. The data structure might also be useful in other areas of your code.

For better organization, you might also make use of the Spreadsheet.setNamedRange() and Spreadsheet.getNamedRange() functions to assign simple IDs to your Ranges. Note that you cannot give more than one Range the same name.

Google Script to Clear multiple ranges.

1 Answers