我怎么知道电子表格单元格是否使用谷歌应用程序脚本合并 [英] How do I know if spreadsheet cells are merged using google apps script

问题描述

在 Google 文档电子表格中.如果单元格 A1 &A2合并了，有没有办法使用谷歌应用程序脚本确认它们已合并?

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

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

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 等只是为单元格返回一个空字符串.例如这不起作用.

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 日).

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());
}

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

旧方法

在此发布另一种比公认的答案更让我喜欢的方法.

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

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

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:

  function isMerged(range){
  try {
    range.getDataSourceUrl()
  } catch (err) {
    throw "isMerged function only works with valid GAS Range objects"
  }
  var sheet = range.getSheet();
  var rangeData = range.getValues();

  var testValue = "testing merge";

  if (rangeData.length == 1 && rangeData[0].length == 1) return false; // we have a single cell. Can't possible be merged :)

  if (rangeData.length == 1) {
    // We have a single row. Which means we're testing a row with multiple columns
    var mainCell=range.getA1Notation().split(":")[0];
    var rowNumber= sheet.getRange(mainCell).getRow();
    var nextColNumber = sheet.getRange(mainCell).getColumn()+1;
    var row = rangeData[0];
    var oldValue = row[1]; // for testing purposes, we're chosing the next possible column

    sheet.getRange(rowNumber,nextColNumber).setValue(testValue);
    if (sheet.getRange(rowNumber,nextColNumber).getValue() !== testValue) {
      return true;
    } else {
      sheet.getRange(rowNumber,nextColNumber).setValue(oldValue);
      return false;
    };
  } else if (rangeData[0].length == 1) {
    // We have multiple rows and a single column.
    var mainCell=range.getA1Notation().split(":")[0];
    var nextRowNumber= sheet.getRange(mainCell).getRow()+1;
    var colNumber = sheet.getRange(mainCell).getColumn();
    var oldValue = rangeData[1][0]; // for testing purposes, we're chosing the next possible row

    sheet.getRange(nextRowNumber,colNumber).setValue(testValue);
    if (sheet.getRange(nextRowNumber,colNumber).getValue() !== testValue) {
      return true;
    } else {
      sheet.getRange(nextRowNumber,colNumber).setValue(oldValue);
      return false;
    };
  } else {
    // We have multiple rows and multiple columns
    var mainCell=range.getA1Notation().split(":")[0];
    var nextRowNumber= sheet.getRange(mainCell).getRow()+1;
    var nextColNumber = sheet.getRange(mainCell).getColumn()+1;
    var oldValue = rangeData[1][1]; // for testing purposes, we're chosing the next possible row and next possible column

    sheet.getRange(nextRowNumber,nextColNumber).setValue(testValue);
    if (sheet.getRange(nextRowNumber,nextColNumber).getValue() !== testValue) {
      return true;
    } else {
      sheet.getRange(nextRowNumber,nextColNumber).setValue(oldValue);
      return false;
    };
  }

  // if none of these checks worked, something's fishy. Either way, return false
  return false
}

我进行了一系列快速测试，结果相应地返回了 true/false，但有一个限制，我没有时间来介绍:如果你有一个像 "A1:F1" 这样的合并范围并且你检查了范围 "A1:G1"(所以，多一列)，它会返回 真，即使 G1 不是合并范围的一部分 - 因为它只检查下一列使用第一个绑定行/列作为参考.

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.

这篇关于我怎么知道电子表格单元格是否使用谷歌应用程序脚本合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！