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();
}
Improve this question
4
  • You need to share with us an example. It's hard to understand what names do your sheets have?
    – oshliaer
    Commented Sep 11, 2019 at 17:16
  • Can you provide the information about each tab named after each month?
    – Tanaike
    Commented Sep 12, 2019 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
    Commented Sep 12, 2019 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
    Commented Sep 13, 2019 at 1:06

1 Answer 1

Reset to default
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:

Improve this answer
2
  • This worked! You are the absolute best. Thanks so much!
    – Jonathan
    Commented Sep 13, 2019 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
    Commented Sep 13, 2019 at 11:53

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.