谷歌表复制格式从活动表到其他工作表 [英] google sheets copy formatting from active sheet to every other sheet

问题描述

我真的需要你的帮助.2天后，我仍然处于零"状态.

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.

这是工作表:

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

到目前为止的代码:

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.

推荐答案

我能够弄清楚:

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
  }
})
}

参考

• 复制到

这篇关于谷歌表复制格式从活动表到其他工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！