Google Spreadsheets复制值和格式脚本问题 [英] Google Spreadsheets Copy Value and Formats Script Issues

问题描述

我有4个独立的谷歌电子表格（由我自己拥有）。我试图寻找很多，并发现了各种建议，但他们都没有正常工作。我们团队中的各个成员进行更新。我想要构建一个组合的电子表格，该电子表格会自动更新它们所更改的值（单元格的值和颜色，因为我们会对各种项目进行颜色编码）。

我试过 Importrange （可行，但不带格式），还尝试 copyValuesandFormatting 和 copyFormatting 。我提出的问题是使用 CopyValues和Formatting，它只会填写第一个选项卡，而脚本的下一部分将不起作用。有谁知道我的问题可能是什么？我一直在努力的脚本部分在下面（只是试图让2张表合并起来，这样做不起作用）。

 函数copyValuesandFormatting（）{
 var ss = SpreadsheetApp.openById（OtherSheetURL1）; 
 var sourceSheet = ss.getSheetByName（Jayson）; 
 var ss2 = SpreadsheetApp.openById（CombinedSheetURL）; 
 var targetSheet = ss2.getSheetByName（Jayson）; 
 var fromRange = ss.getRange（A1：Z50）; 
 var toRange = ss2.getRange（A1：Z50）; 
 var values = fromRange.getValues（）; 
 var fontColors = fromRange.getFontColors（）; 
 var backgrounds = fromRange.getBackgrounds（）; 
 var fonts = fromRange.getFontFamilies（）; 
 var fontWeights = fromRange.getFontWeights（）; 
 var fontStyles = fromRange.getFontStyles（）; 
 
 toRange.setBackgrounds（backgrounds）; 
 toRange.setFontColors（fontColors）; 
 toRange.setValues（values）; 
 toRange.setFontFamilies（fonts）; 
 toRange.setFontWeights（fontWeights）; 
 toRange.setFontStyles（fontStyles）; 
 
 
 $ b函数copyValuesandFormatting（）{
 var ss3 = SpreadsheetApp.openById（OtherSheetURL2）; 
 var sourceSheet = ss3.getSheetByName（Robin）; 
 var ss4 = SpreadsheetApp.openById（CombinedSheetURL）; 
 var targetSheet = ss4.getSheetByName（Robin）; 
 
 var fromRange = ss3.getRange（A1：Z50）; 
 var toRange = ss4.getRange（A1：Z50）; 
 var values = fromRange.getValues（）; 
 var fontColors = fromRange.getFontColors（）; 
 var backgrounds = fromRange.getBackgrounds（）; 
 var fonts = fromRange.getFontFamilies（）; 
 var fontWeights = fromRange.getFontWeights（）; 
 var fontStyles = fromRange.getFontStyles（）; 
 
 toRange.setBackgrounds（backgrounds）; 
 toRange.setFontColors（fontColors）; 
 toRange.setValues（values）; 
 toRange.setFontFamilies（fonts）; 
 toRange.setFontWeights（fontWeights）; 
 toRange.setFontStyles（fontStyles）; 
 
 
} 
  

解决方案

看起来我可以在每张原始工作表中使用CopyTo，以便今天上午工作。

 函数copyTo（{
 //获取和格式化数据范围，从表格到表格。
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var target = SpreadsheetApp.openById（DestinationURL）; 
 var source_sheet = ss.getSheetByName（Jayson）; 
 var target_sheet = target.getSheetByName（Jayson）; 
 var source_range = source_sheet.getRange（A1：Z100）; 
 var target_range = target_sheet.getRange（A1：Z100）; 
 //然后写入设置中定义的值
 var values = source_range.getValues（）; 
 var values = source_range.getValues（）; 
 var fontColors = source_range.getFontColors（）; 
 var backgrounds = source_range.getBackgrounds（）; 
 var fonts = source_range.getFontFamilies（）; 
 var fontWeights = source_ra nge.getFontWeights（）; 
 var fontStyles = source_range.getFontStyles（）; 
 target_range.setValues（values）; 
 target_range.setBackgrounds（backgrounds）; 
 target_range.setFontColors（fontColors）; 
 target_range.setValues（values）; 
 target_range.setFontFamilies（fonts）; 
 target_range.setFontWeights（fontWeights）; 
 target_range.setFontStyles（fontStyles）; 
 //用于检查单元格和格式范围中的数据的最后一行
 var lastRow = source_sheet.getLastRow（）; 
  

I have tried to search a lot and found various suggestion but none of them are working correctly for me.

I have 4 separate google spreadsheets (owned by myself) that various members in our team update. I want to build a combined spreadsheet that automatically updates the values they change (Values and Color of the cell as we color code various items).

I have tried Importrange (works but does not bring formatting), as well as also trying copyValuesandFormatting and copyFormatting. The issue I come up with is with CopyValuesandFormatting is that it will only fill out the first tab, and the next part of the script will not work. Does anybody know what my issue may be? The part of the script that I have been working on is below (only got through trying to get 2 of the sheets to combine and that would not work).

function copyValuesandFormatting() {
     var ss = SpreadsheetApp.openById("OtherSheetURL1");
     var sourceSheet = ss.getSheetByName("Jayson");
     var ss2 = SpreadsheetApp.openById("CombinedSheetURL");  
     var targetSheet = ss2.getSheetByName("Jayson");
     var fromRange = ss.getRange("A1:Z50");
     var toRange = ss2.getRange("A1:Z50");
     var values = fromRange.getValues();
     var fontColors = fromRange.getFontColors();
     var backgrounds = fromRange.getBackgrounds();
     var fonts = fromRange.getFontFamilies();
     var fontWeights = fromRange.getFontWeights();
     var fontStyles = fromRange.getFontStyles();

    toRange.setBackgrounds(backgrounds);
    toRange.setFontColors(fontColors);
    toRange.setValues(values);
    toRange.setFontFamilies(fonts);
    toRange.setFontWeights(fontWeights);
    toRange.setFontStyles(fontStyles);

} 

function copyValuesandFormatting() {
    var ss3 = SpreadsheetApp.openById("OtherSheetURL2");
    var sourceSheet = ss3.getSheetByName("Robin");
    var ss4 = SpreadsheetApp.openById("CombinedSheetURL");
    var targetSheet = ss4.getSheetByName("Robin");

    var fromRange = ss3.getRange("A1:Z50");
    var toRange = ss4.getRange("A1:Z50");
    var values = fromRange.getValues();
    var fontColors = fromRange.getFontColors();
    var backgrounds = fromRange.getBackgrounds();
    var fonts = fromRange.getFontFamilies();
    var fontWeights = fromRange.getFontWeights();
    var fontStyles = fromRange.getFontStyles();

    toRange.setBackgrounds(backgrounds);
    toRange.setFontColors(fontColors);
    toRange.setValues(values);
    toRange.setFontFamilies(fonts);
    toRange.setFontWeights(fontWeights);
    toRange.setFontStyles(fontStyles);


}

解决方案

It looks like I was able to use CopyTo in each one of the original sheets to make it work this morning.

function copyTo( {
  //gets and formats data ranges, to and from sheets.
   var ss = SpreadsheetApp.getActiveSpreadsheet(); 
   var target = SpreadsheetApp.openById("DestinationURL");
   var source_sheet = ss.getSheetByName("Jayson");
   var target_sheet = target.getSheetByName("Jayson");
   var source_range = source_sheet.getRange("A1:Z100");
   var target_range = target_sheet.getRange("A1:Z100");
   //gets then writes values defined in setup
   var values = source_range.getValues(); 
   var values = source_range.getValues();
   var fontColors = source_range.getFontColors();
   var backgrounds = source_range.getBackgrounds();
   var fonts = source_range.getFontFamilies();
   var fontWeights = source_range.getFontWeights();
   var fontStyles = source_range.getFontStyles();
   target_range.setValues(values);
   target_range.setBackgrounds(backgrounds);
   target_range.setFontColors(fontColors);
   target_range.setValues(values);
   target_range.setFontFamilies(fonts);
   target_range.setFontWeights(fontWeights);
   target_range.setFontStyles(fontStyles);
   //for checking last row with data in cell and formatting range
   var lastRow = source_sheet.getLastRow();  

这篇关于Google Spreadsheets复制值和格式脚本问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！