google sheets copy formatting from active sheet to every other sheet

Question

I really need your help. After 2 days, I'm still on "ZERO".

I need a script that copies all the formats from an active sheet to every other sheet. It would be nice to have the conditional formatting, alternating colors and data validation too. I think it's not so hard, but I'm really new in script writing.

This is the sheet:

https://docs.google.com/spreadsheets/d/1mOkm_r4rngPXTkIerSQJKxRih31sM7XoZCZVnoHUc5M/edit?usp=sharing

The code so far:

enter function copyFormatting(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var copyTo = ss.getSheetByName('agent2');
var range = copyTo.getRange(1, 1, copyTo.getMaxColumns(), copyTo.getMaxRows());
sheet.getBandings()[0].copyTo(copyTo.getRange(1,1)),SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
}

function allcopyFormatting() {
var spreadsheet = SpreadsheetApp.getActive();
var allSheets = spreadsheet.getSheets();
allSheets.forEach(function(sheet){
  if(sheet.getSheetName() !== "detroit"){ // if you dont want to use the formatting on this sheet
sheet.activate();
copyFormatting(); // the function what yo like to run
  }
})
}

I was able to figure out and write some functions: clear formatting on all sheets, format row headers on all sheets, show a list of sheets, but this one is too hard for me. Even if I try to model it first with the macro recorder.

I would be very grateful, if you could help me with this issue.

Answer 1

I was able to figure it out:

function allcopyFormatting() {
var spreadsheet = SpreadsheetApp.getActive();
var allSheets = spreadsheet.getSheets();
var sampleSheet = spreadsheet.getActiveSheet();  // from the spreadsheet select the active sheet
var maxRow =sampleSheet.getMaxRows();
var maxCol =sampleSheet.getMaxColumns();
var sampleRange = sampleSheet.getRange(1,1,maxRow,maxCol);
allSheets.forEach(function(sheet){
  if(sheet.getSheetName() !== "detroit"){ // if you dont want to use the formatting on this sheet
sheet.activate();
var targetSheet = spreadsheet.getActiveSheet();  // from the spreadsheet select the active sheet
var targetRange = targetSheet.getRange(1,1);
sampleRange.copyTo(targetRange, {formatOnly:true})  // it copies the formatting
  }
})
}

Reference

• Copy To