1

I'm having trouble editing this code to get it to edit multiple sheets at the same time. It works for whatever is active, but I'd like to disable that and have it work on each tab named after each month and when I'm trying to use getSheetByName(), it doesn't seem to work. Here's the code that does work:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Reset sheet')
    .addItem('Remove all metrics', 'userActionResetByRangesAddresses')
    .addToUi();
}

function userActionResetByRangesAddresses() {
  var sheet1 = SpreadsheetApp.getActiveSheet();
  var rangesAddressesList = [
    'D4:E53',
    'I4:I53',
    'M4:O53',
    'D60:E109',
    'I60:I109',
    'M60:O109',
    'D116:E165',
    'I116:I165',
    'M116:O165',
    'D172:E221',
    'I172:I221',
    'M172:O221',
    'D228:E277',
    'I228:I277',
    'M228:O277'
  ];
  resetByRangesList_(sheet1, rangesAddressesList);
}

function resetByRangesList_(sheet1, rangesAddressesList) {
  sheet1.getRangeList(rangesAddressesList).clearContent();
}
  • You need to share with us an example. It's hard to understand what names do your sheets have? – contributorpw Sep 11 at 17:16
  • Can you provide the information about each tab named after each month? – Tanaike Sep 12 at 2:23
  • Yes, I have 12 tabs named 'January' , 'February', and so on in my spreadsheet. I'm trying to have the above code wipe the data clean on each of those tabs with just one click. hope that makes sense. – Jonathan Sep 12 at 8:19
  • Thank you for replying. I proposed a modified script. Could you please confirm it? If I misunderstood your question and that was not the result you want, I apologize. – Tanaike Sep 13 at 1:06
1
  • You want to reflect your script for 12 sheets from "January" to "December".
    • 12 sheet names are 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'.

If my understanding is correct, how about this modification?

Modified script:

Please modify the function of userActionResetByRangesAddresses() as follows.

function userActionResetByRangesAddresses() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // var sheets = ss.getSheets(); // If all sheets in the Spreadsheet are 12 sheets from "January" to "December", you can use this line.
  var sheetNames = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
  var sheets = sheetNames.map(function(name) {return ss.getSheetByName(name)});
  var rangesAddressesList = ['D4:E53', 'I4:I53', 'M4:O53', 'D60:E109', 'I60:I109', 'M60:O109', 'D116:E165', 'I116:I165', 'M116:O165', 'D172:E221', 'I172:I221', 'M172:O221', 'D228:E277', 'I228:I277', 'M228:O277'];
  sheets.forEach(function(sheet) {
    if (sheet) resetByRangesList_(sheet, rangesAddressesList);
  });
}

References:

  • This worked! You are the absolute best. Thanks so much! – Jonathan Sep 13 at 8:24
  • @Jonathan Welcome. Thank you for letting me know. I'm glad your issue was resolved. If your question was solved, please push an accept button. Other people who have the same issue with you can also base your question as a question which can be solved. And I think that your issue and solution will be useful for them. If you don't find the button, feel free to tell me. stackoverflow.com/help/accepted-answer – Tanaike Sep 13 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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