How do I run a Google Apps script on all sheets in my spreadsheet file?

Question

PROBLEM:

I've seen similar questions and solutions but can only get my script to run on the first sheet in my Spreadsheet file. I've adapted the code from only applying on a specific sheet, but maybe I've missed something when I was doing that..

The code:

/** 
 * Sets background color to strikethrough-formatted cells
 */
function set_color_strikethrough(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  for (i=0; i<sheets.length; i++) {
    var sheet = ss.getSheets()[i];  // current sheet
    var sheetName = sheet.getName();
    console.log("sheetname: ", sheetName);
    var lr = sheet.getLastRow()
    var lc = sheet.getLastColumn()
    var range = sheet.getRange(2,1,lr,lc);
    var results = range.getFontLines();

    for (var i=0;i<lr;i++) {
      for (var j=0;j<lc;j++ ) {
        if(results[i][j] == "line-through"){
          var color = sheet.getRange(i+2,1,1,lc).setBackground("orange")
        }
      }
    }
  }
}

---

UPDATE (solution):

Thanks to @Tanaike for pointing out that the i counter variable was misused, a simple mistake that was overlooked. Working code:

/** 
 * Sets background color to strikethrough-formatted cells
 */
function set_color_strikethrough(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  for (k=0; k<sheets.length; k++) {
    var sheet = sheets[k];  // current sheet
    var lr = sheet.getLastRow()
    var lc = sheet.getLastColumn()
    var range = sheet.getRange(2,1,lr,lc);
    var results = range.getFontLines();

    for (var i=0;i<lr;i++) {
      for (var j=0;j<lc;j++ ) {
        if(results[i][j] == "line-through"){
          var color = sheet.getRange(i+2,1,1,lc).setBackground("orange")
        }
      }
    }
  }
}

Answer 1

I would like to modify as follows.

Modification points:

• In your script, the for loop using the variable of i is used in the for loop using i. i at the 1st for loop is changed by the 2nd for loop. By this, the loop using i doesn't correctly work as you expect. I think that this is the reason of your issue.
• var sheet = ss.getSheets()[i]; can be modified to var sheet = sheets[i];. By this, the process cost can be reduce a little.

Modified script:

When your script is modified, it becomes as follows.

function set_color_strikethrough(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  for (k=0; k<sheets.length; k++) {  // <--- Modified
    var sheet = sheets[k]; // <--- Modified
    var sheetName = sheet.getName();
    console.log("sheetname: ", sheetName);
    var lr = sheet.getLastRow()
    var lc = sheet.getLastColumn()
    var range = sheet.getRange(2,1,lr,lc);
    var results = range.getFontLines();

    for (var i=0;i<lr;i++) {
      for (var j=0;j<lc;j++ ) {
        if(results[i][j] == "line-through"){
          var color = sheet.getRange(i+2,1,1,lc).setBackground("orange")
        }
      }
    }
  }
}