将Google电子表格中的表格单元格与应用程序脚本拆分（拆分） [英] Split (break apart) merged table cells in google spreadsheet with an apps script

问题描述

操作（复制/移动）包含合并单元格的范围时，我总是收到错误您的粘贴与合并单元格重叠。请取消合并单元格，然后重试。但是当试图使用 Range＃breakApart 取消范围中的单元格时，我得到另一个错误：选定的单元格不能合并。这更加令人困惑，因为我我不是在试图合并任何东西，我只是试图将细胞分开。

解决方案

结果 breakApart 只有当它被调用的范围包含所有合并的单元格时才起作用，而不仅仅是合并区域的一部分。

这不会太糟糕。一旦你意识到API没有办法获得合并单元格的范围，问题就开始了。所以解决这个问题的最好方法就是轻轻地扩展你的范围，直到没有错误发生：

  var breakRange = myRange; （;;）{
尝试{
 breakRange.breakApart（）; 
） 
休息; 
} catch（e）{
 breakRange = mySheet.getRange（
 breakRange.getRowIndex（），
 breakRange.getColumnIndex（），
 Math.min（
 breakRange.getHeight（）+ 5，
 mySheet.getMaxRows（） -  breakRange.getRowIndex（）+ 1 
），
 Math.min（
 breakRange.getWidth（） +5，
 mySheet.getMaxColumns（） -  breakRange.getColumnIndex（）+ 1 
）
）; 
 
 
 $ / code $ / pre 
 
（我已经添加了五行/列，而不是因为获得范围似乎是一个相当昂贵的过程）。只要您扩展搜索区域时没有添加额外的合并单元格，这项工作就会很好。
 
 
 
在电子表格中合并单元格时，Google应用程序脚本API至少在以下几个方面被打破： 
 
 
 
 
无法在a中获得一组合并单元格的范围sheet / range。
 
 
应该有一种方法来查看哪些单元格已被拆分（ breakApart 仅返回原始范围 ）。这使得拆分合并，然后让每个单元格包含原始合并单元格的内容是不可能的。
 
 
尝试使用 breakApart  in仅包含合并单元的一部分的范围仍然应该拆分整个合并而不是抛出异常。
 
 
总而言之，目前的API只适用于脚本作者自己完全控制其布局和使用的电子表格。
 
When operating (copying/moving) ranges containing merged cells, I always get the error "Your paste overlaps with merged cells. Please unmerge the cells and try again". But when trying to unmerge the cells in the range using Range#breakApart, I get another error: "The selected cells cannot be merged.", which is even more confusing since I’m not trying to merge anything, I’m only trying to break the cells apart.
解决方案
Turns out breakApart only works when the range it is called on encompasses all merged cells, not just parts of the merge area.

This would not be too bad. The problem starts once you realize that there is no way in the API to get the ranges of merged cells. So the best way to solve this (that I’ve found so far) is just to gently extend your range until no error occurs:
var breakRange = myRange;
for(;;) {
  try {
    breakRange.breakApart();
    break;
  } catch(e) {
    breakRange = mySheet.getRange(
      breakRange.getRowIndex(),
      breakRange.getColumnIndex(),
      Math.min(
        breakRange.getHeight()+5,
        mySheet.getMaxRows()-breakRange.getRowIndex()+1
      ),
      Math.min(
        breakRange.getWidth()+5,
        mySheet.getMaxColumns()-breakRange.getColumnIndex()+1
      )
    );
  }
}
(I’ve added five rows/cols instead of one since getting ranges seems to be a rather expensive process). This works quite well so long as you don’t have additional merged cells being added as you expand your search area.

When it comes to merged cells in spreadsheets, the Google Apps Scripts API is broken fundamentally in at least the following ways:

There is no way to get an array of ranges of merged cells in a sheet/range.
There should be a way to see which cells have been split (breakApart only returns the original range "for chaining"). This makes it impossible to split merges and then have each cell contain the content of the original merge cell.
Trying to use breakApart in a range that encompasses only a part of a merge cell should still break apart the entire merge instead of throwing an exception.
All in all, the API as it currently stands only works for spreadsheets whose layout and usage the script author herself has complete control over.

                        
这篇关于将Google电子表格中的表格单元格与应用程序脚本拆分（拆分）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！