Looping a script through ALL sheets-CodePudding

I have a simple script which is running on a sheet. however, I will be adding more sheets to the spreadsheet in the future, and would like to run the script against ALL sheets.

So at the moment I need a seperate function for each sheet. In the function I make sheet1 active, and have a seperate function which has the same functionality for sheet 2 ,3 ,4 etc. however, that doesnt seem a very elegant solution and isnt easy to maintain.

Does anyone know how I could create a simple loop, that would detect the number of sheets in my spreadsheet, and then run my script for each of the sheets. For reference, mny full script is below, currently harded for sheet2.

Many thanks guys

function pricesearchsheet2() {
  
  
  var date = new Date();
  var currenthour = date.getHours();
  var currentmonth = date.getMonth();
  var sheetrow = 1
  var targetcell = 1

// set the target cell based on the current hour of the day
  if (currenthour == 0) {
  var targetcell = "B6";
}
if (currenthour == 1) {
  var targetcell = "C6";
}
if (currenthour == 2) {
  var targetcell = "D6";
}
if (currenthour == 3) {
  var targetcell = "E6";
}
if (currenthour == 4) {
  var targetcell = "F6";
}
if (currenthour == 5) {
  var targetcell = "G6";
}
if (currenthour == 6) {
  var targetcell = "H6";
}
if (currenthour == 7) {
  var targetcell = "I6";
}
if (currenthour == 8) {
  var targetcell = "J6";
}
if (currenthour == 9) {
  var targetcell = "K6";
}
if (currenthour == 10) {
  var targetcell = "L6";
}
if (currenthour == 11) {
  var targetcell = "M6";
}
if (currenthour == 12) {
  var targetcell = "N6";
}
if (currenthour == 13) {
  var targetcell = "O6";
}
if (currenthour == 14) {
  var targetcell = "P6";
}
if (currenthour == 15) {
  var targetcell = "Q6";
}
if (currenthour == 16) {
  var targetcell = "R6";
}
if (currenthour == 17) {
  var targetcell = "S6";
}
if (currenthour == 18) {
  var targetcell = "T6";
}
if (currenthour == 19) {
  var targetcell = "U6";
}
if (currenthour == 20) {
  var targetcell = "V6";
}
if (currenthour == 21) {
  var targetcell = "W6";
}
if (currenthour == 22) {
  var targetcell = "X6";
}
if (currenthour == 23) {
  var targetcell = "Y6";
}
if (currenthour == 24) {
  var targetcell = "Z6";
}

SpreadsheetApp.getActive().getSheetByName("Sheet2");
// Capture the data from FUTBIN according to the URL set in cell A1
  SpreadsheetApp.getActive().getRange(targetcell).setValue('=--SPLIT(REGEXEXTRACT(JOIN(",", IMPORTDATA(B1)),"LCPrice"":""(.*)"), CHAR(34))')
}

CodePudding user response：

You can enclose the code in a for loop, and iterate through the sheets:

// RETRIEVE SHEET NAMES
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();

// ITERATE THROUGH SHEETS
for (let item in sheets) {
    let sheetName = sheets[item].getSheetName();
    //
    // Your code here
    // ... 
    // ss.getSheetByName(sheetName);
    // ...
    //
}

Also, for what it's worth, you could use a switch/case statement instead of so many if statements.

CodePudding user response：

Something like this:

...

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (var sheet of sheets) {

  sheet.getRange(targetcell)
    .setValue('=--SPLIT(REGEXEXTRACT(JOIN(",",IMPORTDATA(B1)),"LCPrice"":""(.*)"), CHAR(34))')

}

Probably all the script can be boiled down to this:

function pricesearchsheet2() {

    var date = new Date();
    var currenthour = date.getHours();
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

    for (var sheet of sheets) {
        sheet.getRange(6, currenthour   2)
            .setValue('=--SPLIT(REGEXEXTRACT(JOIN(",",IMPORTDATA(B1)),"LCPrice"":""(.*)"), CHAR(34))')
    }
}

(6, currenthour 2) gives you B6 for hour 0, C6 for hour 1, D6 for hour 2, etc...