I would like to run the following script to all but the last tab in a Google Sheet:

function ClearAll() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = 'ScriptTest';
  var sheet = ss.getSheetByName(sheetname);
  var LR = sheet.getLastRow();
  var range = sheet.getRange(1,1,LR);

As you can see it is only referencing a single tab "ScriptTest".

New contributor
Admin18 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Try this (untested)

// uncheck every checkbox in Column A of every sheet except for the last sheet

function ClearAll() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets() 
    var sheetscount = sheets.length
    for (s=0;s<sheetscount-1;s++){
        var LR = sheets[s].getLastRow();
        var range = sheets[s].getRange(1,1,LR);


  • var sheets = ss.getSheets() - this returns ALL the sheets
  • var sheetscount = sheets.length - counts how many sheets are in the spreadsheet
  • for (s=0;s<sheetscount-1;s++){
    • create a loop that will has a length of the total number of sheets minus one (i.e. that last sheet doesn't get processed.)
    • sheets[s] - this is used instead of getSheetByName(sheetname). Each time the loop restarts, the value of s increments by one, so the sheet ID starts off as sheets[0], then sheets[1]and so on.
  • Thank you so much for taking the time to help me out. Have a wonderful day!!!
    – Admin18
    Jan 12 at 18:19
  • If this answer was helpful, then you might consider “accepting” it.
    – Tedinoz
    Jan 12 at 23:34

Your Answer

Admin18 is a new contributor. Be nice, and check out our Code of Conduct.

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.