Google App脚本 - 导入2个SpreadSheets之间的所有范围格式 [英] Google App Script - Import ALL range formatting between 2 SpreadSheets

问题描述

更新：我写了for循环来修复合并的单元格和列宽问题。只需要一些边框帮助！

我正在努力将一系列单元格从一个Google电子表格复制到另一个Google电子表格。困难的部分是我试图保持所有格式。到目前为止，我已经能够保留所有格式，除了3个项目：单元格边框，各种合并单元格和列宽。任何帮助将不胜感激。提前抱歉，昨天刚刚发现Google App Script，我以前从未使用过JS。

  function importTable（）{ 
 
 //源电子表格
 var srcSpreadSheet = SpreadsheetApp.openById（SpreadSheet-ID）; 
 var scrSheet = srcSpreadSheet.setActiveSheet（srcSpreadSheet.getSheetByName（Sheet1））; 
 
 //目标电子表格
 var destSpreadSheet = SpreadsheetApp.openById（SpreadSheet-ID）; 
 var destSheet = destSpreadSheet.setActiveSheet（destSpreadSheet.getSheetByName（Test））; 
 destSheet.clear（）; 
 
 //从源表中获取数据和格式
 var range = scrSheet.getRange（1,2,24,16）; 
 
 var values = range.getValues（）; 
 var background = range.getBackgrounds（）; 
 var banding = range.getBandings（）; 
 var mergedRanges = range.getMergedRanges（）; 
 var fontColor = range.getFontColors（）; 
 var fontFamily = range.getFontFamilies（）; 
 var fontLine = range.getFontLines（）; 
 var fontSize = range.getFontSizes（）; 
 var fontStyle = range.getFontStyles（）; 
 var fontWeight = range.getFontWeights（）; 
 var horAlign = range.getHorizo​​ntalAlignments（）; 
 var textStyle = range.getTextStyles（）; 
 var vertAlign = range.getVerticalAlignments（）; 
 
 //将数据和格式放在目标表中
 var destRange = destSheet.getRange（1,2,24,16）; 
 
 destRange.setValues（values）; 
 destRange.setBackgrounds（背景）; 
 destRange.setFontColors（fontColor）; 
 destRange.setFontFamilies（fontFamily）; 
 destRange.setFontLines（fontLine）; 
 destRange.setFontSizes（fontSize）; 
 destRange.setFontStyles（fontStyle）; 
 destRange.setFontWeights（fontWeight）; 
 destRange.setHorizo​​ntalAlignments（horAlign）; 
 destRange.setTextStyles（textStyle）; 
 destRange.setVerticalAlignments（vertAlign）; 
 
 //迭代合并范围
 for（var i = 0; i< mergedRanges.length; i ++）{
 destSheet.getRange（mergedRanges [i ] .getA1Notation（））合并（）; 
} 
 
 //迭代得到源目的地的列宽
 for（var i = 1; i< 18; i ++）{
 var width = scrSheet.getColumnWidth（i）; 
 destSheet.setColumnWidth（i，width）; 
} 
 
 //迭代得到源目的地的行高度
 for（var i = 1; i< 27; i ++）{
 var height = scrSheet.getRowHeight（i）; 
 destSheet.setRowHeight（i，height）; 
} 
 
} 
  

解决方案

这个解决方法怎么样？在此变通方法中，使用Class Sheet的copyTo（）和Class Range的copyTo（）。我认为你的情况有几个答案。所以请把它想象成其中之一。

这个脚本的流程如下。

1. 使用类别表格的copyTo（）复制包含您要复制的范围的工作表。


2. 从复制的工作表复制值，公式，格式和合并使用类范围的copyTo（）到目标表。
   
   
   
   
   • 此时，使用您要复制的范围。
   
   


3. 使用类范围的copyTo（）将列宽从复制的工作表复制到目标工作表。


4. 删除模板工作表。

示例脚本：

  function importTable2（）{
 / / source 
 var sourceSheet = SpreadsheetApp.openById（SpreadSheet-ID）。getSheetByName（Sheet1）; 
 var sourceRange = sourceSheet.getRange（1,2,24,16）; 
 
 //目的地
 var destSS = SpreadsheetApp.openById（SpreadSheet-ID）; 
 var destSheet = destSS.getSheetByName（Test）; 
 var destRange = destSheet.getRange（1,2,24,16）; 
 destSheet.clear（）; 
 
 //复制
 var copiedsheet = sourceRange.getSheet（）。copyTo（destSS）; 
 copiedsheet.getRange（sourceRange.getA1Notation（））。copyTo（destRange）; 
 copiedsheet.getRange（sourceRange.getA1Notation（））。copyTo（destRange，SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS，false）; 
 destSS.deleteSheet（复制表）; 
} 
  

注：

• 在运行此脚本之前，请设置 SpreadSheet-ID 。

参考文献：

• 班级表格的copyTo（）


• 类范围的copyTo（）

<如果我误解了你的问题，请告诉我。我想修改它。

Update: I wrote for loops to fix the merged cell and column width issues. Just need some help with the borders!

I'm working on copying a range of cells from one Google Spreadsheet to another Google Spreadsheet. The difficult part is that I'm trying to keep ALL formatting. I have been able to preserve all of the formatting thus far, except for 3 items: cell borders, various merged cells, and column width. Any help would be greatly appreciated. Sorry in advance, just found out about Google App Script yesterday and I've never used JS before.

function importTable() {

  // Source spreadsheet
  var srcSpreadSheet = SpreadsheetApp.openById("SpreadSheet-ID");
  var scrSheet = srcSpreadSheet.setActiveSheet(srcSpreadSheet.getSheetByName("Sheet1"));

  // Destination spreadsheet
  var destSpreadSheet = SpreadsheetApp.openById("SpreadSheet-ID");
  var destSheet = destSpreadSheet.setActiveSheet(destSpreadSheet.getSheetByName("Test"));
  destSheet.clear();

  // Get data and formatting from the source sheet
  var range = scrSheet.getRange(1, 2, 24, 16);

  var values = range.getValues();
  var background = range.getBackgrounds();
  var banding = range.getBandings();
  var mergedRanges = range.getMergedRanges();
  var fontColor = range.getFontColors();
  var fontFamily = range.getFontFamilies();
  var fontLine = range.getFontLines();
  var fontSize = range.getFontSizes();
  var fontStyle = range.getFontStyles();
  var fontWeight = range.getFontWeights();
  var horAlign = range.getHorizontalAlignments();
  var textStyle = range.getTextStyles();
  var vertAlign = range.getVerticalAlignments();

  // Put data and formatting in the destination sheet
  var destRange = destSheet.getRange(1, 2, 24, 16);

  destRange.setValues(values);
  destRange.setBackgrounds(background);
  destRange.setFontColors(fontColor);
  destRange.setFontFamilies(fontFamily);
  destRange.setFontLines(fontLine);
  destRange.setFontSizes(fontSize);
  destRange.setFontStyles(fontStyle);
  destRange.setFontWeights(fontWeight);
  destRange.setHorizontalAlignments(horAlign);
  destRange.setTextStyles(textStyle);
  destRange.setVerticalAlignments(vertAlign);

    // Iterate through to put merged ranges in place
  for (var i = 0; i < mergedRanges.length; i++) {
    destSheet.getRange(mergedRanges[i].getA1Notation()).merge();
  }

  // Iterate through to get the column width of the source destination
  for (var i = 1; i < 18; i++) {
    var width = scrSheet.getColumnWidth(i);
    destSheet.setColumnWidth(i, width);
  }

  // Iterate through to get the row heighth of the source destination
  for (var i = 1; i < 27; i++){
    var height = scrSheet.getRowHeight(i);
    destSheet.setRowHeight(i, height);
  }

}

解决方案

How about this workaround? In this workaround, copyTo() of Class Sheet and copyTo() of Class Range are used. I think that there are several answers for your situation. So please think of this as one of them.

The flow of this script is as follows.

1. Copy a sheet including the range you want to copy as a template using copyTo() of Class Sheet.
2. Copy values, formulas, formats and merges from the copied sheet to the destination sheet using copyTo() of Class Range.
   • At that time, the range you want to copy is used.
3. Copy column width from from the copied sheet to the destination sheet using copyTo() of Class Range.
4. Delete the template sheet.

Sample script :

function importTable2() {
  // Source
  var sourceSheet = SpreadsheetApp.openById("SpreadSheet-ID").getSheetByName("Sheet1");
  var sourceRange = sourceSheet.getRange(1, 2, 24, 16);

  // Destination
  var destSS = SpreadsheetApp.openById("SpreadSheet-ID");
  var destSheet = destSS.getSheetByName("Test");
  var destRange = destSheet.getRange(1, 2, 24, 16);
  destSheet.clear();

  // Copy
  var copiedsheet = sourceRange.getSheet().copyTo(destSS);
  copiedsheet.getRange(sourceRange.getA1Notation()).copyTo(destRange);
  copiedsheet.getRange(sourceRange.getA1Notation()).copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
  destSS.deleteSheet(copiedsheet);
}

Note :

• Before you run this script, please set SpreadSheet-ID.

References :

• copyTo() of Class Sheet
• copyTo() of Class Range

If I misunderstand your question, please tell me. I would like to modify it.

这篇关于Google App脚本 - 导入2个SpreadSheets之间的所有范围格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！