How can I make my script run on multiple sheets on Google Sheets ? I'm inserting rows with data-CodePudding

I'm writing a script that inserts a row below row 1 of each sheet and includes some data in that row. It works for the first sheet, but I need it to run for multiple sheets. Each sheet is a business manager's name (Brian, for example), and it would be helpful to have this run for all sheets. I have tried several methods from previous similar questions but have not gotten anything to work. The code is below.

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var dataSht = ss.getSheetByName("Brian");
  var lastRow = dataSht.insertRows(2,1)
  var dataRng = dataSht.getRange("A2:D2").getValues();
  dataSht.getRange("A2:B2").setFormula('=A3:B3')
}

CodePudding user response：

Try

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(function(dataSht){
    dataSht.insertRows(2,1)
    dataSht.getRange("A2:B2").setFormula('=A3:B3')
  })
}

CodePudding user response：

If you don't wish to use all sheets

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const incl = ["Brian", "others"]
  ss.getSheets().filter(s => ~incl.indexOf(s.getName())).forEach(sh => {
    sh.insertRows(2, 1)
    sh.getRange("A2:B2").setFormula('=A3:B3')
  })
}