Google Apps脚本通过格式转换表格 [英] Google Apps Script to VMerge tables WITH FORMATTING

问题描述

有谁知道是否有一个Google应用程序脚本可以完成VMerge的功能，但是可以保持被合并在一起的表格的格式？ （在Google Spreadsheets中）

VMerge是一种可以用作自定义公式的脚本，但是我可以触发自己的脚本也能很好地执行。

任何帮助都将倍受赞赏。

解决方案

VMerge需要数组的值作为参数，因此不知道引用哪些单元格来创建这些数组。当用作自定义公式时，表单解析器在将所有范围参数传递给VMerge之前将其解析为其值。此外，参数可能是硬编码的，或者是查询或返回范围的其他函数的结果。由于这一点，修改VMerge将单元格格式复制到新的合并表是不可行的。

进一步复杂化，自定义函数不能修改它们之外的单元格附加到它们，它们只能返回值或数组值。来自第37期的评论：

<2>作为单元函数使用的脚本不允许做复杂的事情
像连接到其他API或设置其他的值细胞。作为单元函数使用的脚本
只允许返回一个值。

所以你必须为您从脚本调用的函数。以下函数将多个范围连接到给定锚点处的新表中。因为我开始尝试使这个自定义函数可以从表单中调用，所以参数是a1Notation中的范围的字符串表达式。 （它很容易被重构，直接处理Range对象。）

新范围的Anchor预计是一个单元格。例如：


可以加入任意大小的一个或多个范围 - 每个范围可以直接放置在前一个的下方。 ul>

VJoin（D1，A1：B）; - 所有列A& B在D& E

VJoin（Sheet2！A1，Sheet1！C9：E10，Sheet1！A14：B15）; - 工作表1中的两个不同范围已加入并复制到工作表2中。

以下是代码：

  / * 
 *从给定锚点开始，将多个来源的范围垂直连接到一个新表
 *中。值和格式被复制。 
 * 
 * @param {a1Notation} anchorA1连接表的锚点。 
 * @param {a1Notation}来源一个或多个来源范围。 
 * / 
函数VJoin（anchorA1，sources）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var anchor = sheet.getRange（anchorA1）; 
 var anchorSheet = anchor.getSheet（）; //如果anchorA1不在活动工作表上
 var nextAnchor = anchor; 
 
 for（var i in arguments）{
 //参数应该是包含a1Notation的字符串。 
 if（i == 0）continue; //第一个参数是anchorA1，跳过它。 
 if（arguments [i] .constructor == String）{
 var source = sheet.getRange（arguments [i]）; 
 var destination = anchorSheet.getRange（nextAnchor.getRow（），nextAnchor.getColumn（），
 source.getNumRows（），source.getNumColumns（））; 
 //复制所有值&格式化到新的位置。 
 source.copyTo（目的地）; 
 //通过移动锚点来准备下一个范围
 nextAnchor = sheet.getRange（nextAnchor.getRow（）+ source.getNumRows（），
 nextAnchor.getColumn（））; 
} 
 else {
 throw new Error（Expected String contains a1Notation。）
} 
} 
} 
  

Does anyone know if there is a Google apps script out there that does what VMerge does but keeps the formatting of the tables being merged together? (in Google Spreadsheets)

VMerge is a script that can be used as a custom formula but a script that I can trigger myself will do just fine too.

Any help would be much appreciated.

解决方案

VMerge expects arrays-of-values as parameters, and therefore does not know what cells were referenced creating those arrays. When used as a Custom Formula, the sheet parser resolves all range parameters into their values before passing them to VMerge. Additionally, the parameters may be hard-coded or be the result of Queries or other functions that return ranges. Because of this alone, it's not feasible to modify VMerge to copy cell formats to the new merged table.

Complicating things further, Custom Functions cannot modify cells outside of the one they are attached to, they can only return values or arrays of values. From comment in Issue 37:

2) Scripts used as cell functions are not allowed to do complex things like connect to other APIs or set the values of other cells. Scripts used as cell functions are only allowed to return a value.

So you're going to have to settle for a function you call from scripts. The following function will join multiple ranges into a new table at a given anchor point. Because I started out trying to make this a custom function callable from a sheet, the parameters are string expressions of ranges, in a1Notation. (It could easily be refactored to deal directly with Range objects.)

The "Anchor" for the new range is expected to be a cell. One or more ranges of any size may be joined - each will be positioned directly below the previous.

Examples:

• VJoin("D1","A1:B"); - All of columns A & B duplicated in columns D & E
• VJoin("Sheet2!A1","Sheet1!C9:E10","Sheet1!A14:B15"); - Two different ranges in Sheet 1 joined and copied to Sheet 2.

Here's the code:

/*
 * Vertically join the ranges from multiple sources into a new table
 * starting at the given anchor point. Values and formatting are copied.
 *
 * @param {a1Notation} anchorA1 Anchor for joined table.
 * @param {a1Notation} sources One or more source ranges.
*/
function VJoin(anchorA1,sources) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var anchor = sheet.getRange(anchorA1);
  var anchorSheet = anchor.getSheet();  // in case anchorA1 is not on the "active sheet"
  var nextAnchor = anchor;

  for (var i in arguments) {
    // Arguments are expected to be Strings, containing a1Notation.
    if (i == 0) continue; // First argument was anchorA1, skip it.
    if (arguments[i].constructor == String) {
      var source = sheet.getRange(arguments[i]);
      var destination = anchorSheet.getRange(nextAnchor.getRow(), nextAnchor.getColumn(),
                                       source.getNumRows(), source.getNumColumns() );
      // Copy all values & formatting to new location.
      source.copyTo(destination);
      // Prepare for next range by moving our anchor
      nextAnchor = sheet.getRange(nextAnchor.getRow() + source.getNumRows(), 
                                  nextAnchor.getColumn());
    }
    else {
      throw new Error ("Expected String containing a1Notation.")
    }
  }
}

这篇关于Google Apps脚本通过格式转换表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！