Run Formatting Script Across Multiple Google Sheets Tabs – Google Apps Script

google sheetsgoogle-apps-script

I have an onEdit script I'm using to protect/force formatting on a single tab within a google sheets document – and it works perfectly.

The issue I have is that I'd like the same script to work on multiple tabs of my choosing, but I'm not sure how to go about that. To begin with, I'd like it to work on a tab titled 'WEEKEND' but I expect I'll add more tabs as and when I need them. I know you can only use the onEdit function once, but I don't know how to set up an working array that allows me to replicate the script across other tabs. Here's the script I'm using. 

    }
function onEdit(e){          
  var ss = SpreadsheetApp.getActive()
  var sheet =ss.getActiveSheet()
  if(sheet.getSheetName() == "NEWS"){
  var entryRange = e.range
  var range  = sheet.getRange(1,entryRange.getColumn(),1,entryRange.getNumColumns())              
  Logger.log(entryRange.getA1Notation())
  range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns()+entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows()+entryRange.getRow()-1)
  Logger.log(entryRange.getColumn())
  if(entryRange.getColumn() == 10){                                 
    if (entryRange.getRow() != 1){                                 
    e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
    }
  }
  }
}

function setFormat(){
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getActiveSheet()
  var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 2, dataRange.getNumRows())
  }

Best Answer

You have to make a copy of your onEdit function, to change the name of the copy and to replace the following line code:

var sheet =ss.getActiveSheet()

by something like the following one:

var sheet =ss.getSheetByName(name);

where name is a variable that has being assigned with the sheet name to use. Also you should add a way to set the value of name, but there are many ways to do this.

Then you could run the script from the Script editor or to add a custom menu.

Related Topic