Repeat the same scripts in all sheets within one spreadsheet-CodePudding

I have been looking for a solution to my problem but without any success so far. I am a true begginer in AppsScript GoogleSheet, therefore my issue might be something really trivial for experienced users.

My issue is that I created several scripts, mostly for hide/unhide rows and columns and now I would like to implement these scripts in all existing and newly created sheets within the same spreadsheet. My spreadsheet is a form so every sheet within it is the same - so the code could potentially work on the same conditions but in different sheets.

Could I kindly ask you for your help in this matter?

Heres an example of my code:

function hideRow2()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var dropDown = sheet.getRange('B140:G140').getValue();

if(dropDown == "NIE")
{
sheet.hideRows(141,5);

}
else if(dropDown == "TAK")
{
sheet.showRows(140,6);

}

else if(dropDown == "")
{
sheet.showRows(140,6);

}
else
{
  
}

}

I have tried to implement such a code to repeat the same script:

function everysheet() {
var ss = SpreadsheetApp.getActive();

var allsheets = ss.getSheets();
var allsheets = ss.getSheets();[0].getSheetName()

allsheets.forEach(function(sheet){
if(sheet.getSheetName() !=="Arkusz4"){
  sheet.activate();
  hideRow2();
  
}
})
}

But even if there was no error from the AppsScript, the code itself didn't work.

I would be grateful for your help.

CodePudding user response：

As a quick fix you can add an argument into the function hideRow2():

function hideRow2(sheet)
{

Remove the line:

var sheet = ss.getSheets()[0];

And pass the sheet from the function everysheet() this way:

hideRow2(sheet);

Or you can replace all the code with this:

function everysheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // get all sheets except 'Arkusz4'
  const sheets = ss.getSheets().filter(s => s.getName() != 'Arkusz4');

  // loop through the sheets
  sheets.forEach(sheet => {
    let dropDown = sheet.getRange('B140').getValue();
    if (dropDown == 'NIE') sheet.hideRows(141, 5);
    else if (dropDown == 'TAK' || dropDown ==  '') sheet.showRows(140, 6);
  })
}

CodePudding user response：

If all of your sheets are the same then this should work on the active sheet as long as you are access them as a user.

function hideRow2() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var dropDown = sheet.getRange('B140').getValue();
  if (dropDown == "NIE") {
    sheet.hideRows(141, 5);
  } else if (dropDown == "TAK") {
    sheet.showRows(140, 6);
  } else (dropDown == "") {
    sheet.showRows(140, 6);
  }
}