如何知道电子表格单元是否使用Google应用程序脚本进行合并(How do I know if spreadsheet cells are merged using google apps scrip

如何知道电子表格单元是否使用Google应用程序脚本进行合并(How do I know if spreadsheet cells are merged using google apps script)

在Google文档电子表格中。如果单元格A1和A2合并，是否有一种方法来确认它们是合并的，使用谷歌应用脚本？

GAS https://developers.google.com/apps-script/class_range#merge中有合并功能

但是没有函数或例子说明如何检查单元格是否合并。

getValues等只是为单元格返回一个空字符串。例如，这不起作用。

function testMerge() { var spreadsheet = SpreadsheetApp.openById('Z3ppTjxNUE........'); var sheet = spreadsheet.getSheets()[0]; var range = sheet.getRange("A3:A4"); var values = range.getValues(); var formulas = range.getFormulasR1C1(); var formulasA1 = range.getFormulas(); range = sheet.getRange("A4"); range.setValue("a"); range = sheet.getRange("A3:A4"); var values2 = range.getValues(); var formulas2 = range.getFormulasR1C1(); var formulasA12 = range.getFormulas(); var count = range.getHeight(); }

In a Google docs spreadsheet. If cells A1 & A2 are merged, is there a way to confirm they are merged, using google apps script?

There is a merge function in GAS https://developers.google.com/apps-script/class_range#merge

But there is no function or example that shows how to check if cells are merged.

getValues etc just returns an empty string for the cell. e.g. this does not work.

function testMerge() { var spreadsheet = SpreadsheetApp.openById('Z3ppTjxNUE........'); var sheet = spreadsheet.getSheets()[0]; var range = sheet.getRange("A3:A4"); var values = range.getValues(); var formulas = range.getFormulasR1C1(); var formulasA1 = range.getFormulas(); range = sheet.getRange("A4"); range.setValue("a"); range = sheet.getRange("A3:A4"); var values2 = range.getValues(); var formulas2 = range.getFormulasR1C1(); var formulasA12 = range.getFormulas(); var count = range.getHeight(); }

最满意答案

range.isPartOfMerge（）

看起来.isPartOfMerge()方法已实施（2016年9月11日）。

所以你可以检查一个范围是否合并为：

function testMerge() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Sheet1"); var range = sheet.getRange(1,1,2); // example for cells A1 and A2 Logger.log(range.isPartOfMerge()); }

Google文档 https://developers.google.com/apps-script/reference/spreadsheet/range#ispartofmerge

旧方法

在这里发布另一种我比所接受的答案更喜欢的方法。

如果您尝试使用GAS将值存储在合并范围的单元格中，但第一个单元格除外，它将不会存储它。

因此，我们可以利用这一点并尝试存储该值并检查它是否已存储，并使用与此类似的函数：

我已经运行了一系列快速测试，并且相应地返回了true / false ，但是我没有时间来覆盖这个限制：如果您有像"A1:F1"这样的合并范围，并且您检查了范围"A1:G1" （所以，多一列），即使G1不是合并范围的一部分，它也会返回true ，因为它只检查下一列作为引用第一个绑定行/列。

range.isPartOfMerge()

It seems the .isPartOfMerge() method was implemented (11 sept 2016).

So you can check if a range is merged as such:

function testMerge() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Sheet1"); var range = sheet.getRange(1,1,2); // example for cells A1 and A2 Logger.log(range.isPartOfMerge()); }

Docs https://developers.google.com/apps-script/reference/spreadsheet/range#ispartofmerge

OLD APPROACH

Posting here another approach that I like more than the accepted answer.

If you try to use GAS to store a value in a cell of a merged range, except the first cell, it will not store it.

As such, we can exploit this and attempt to store the value and check if it was stored, using a function similar to this one:

I've ran a series of quick tests and it returned true/false accordingly, but there is a limitation that I didn't have time to cover: If you had a merged range like "A1:F1" and you checked the range "A1:G1" (so, one more column), it will return true, even if G1 is not part of the merged range - because it checks only the next column using as reference the first bound row/column.

更多推荐