Google Script to Clear multiple ranges.

2019-04-13 10:03发布

问题:

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)

}; }

回答1:

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.