google-apps-script - 如何使用脚本确定一列是否隐藏在 Google 电子表格中？

Question

我正在尝试为 Google 电子表格编写一个函数，用于定位组的第一列和最后一列。然后它会隐藏该组，或者如果它已经隐藏则显示它。

但是，我无法找到确定列是否隐藏的方法。我在 Google 的类(class)表页面上找不到任何内容 https://developers.google.com/apps-script/reference/spreadsheet/sheet ，而且我还没有找到与 Excel 的 .hidden 等效的

即使隐藏，getColumnWidth(column) 也会返回未隐藏的列宽。

这是我的代码:

function hideShowColumns(startCol, endCol) { 
  //endCol is one column past the last data set that should be hidden
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transposed Data");
  //import the data from the Column Headers
  var colHeaderData = sheet.getRange(1, 1, 2, sheet.getMaxColumns()).getValues(); 
  var startColNum = 0;
  var endColNum = 0;

  // This section searches for the column names in the header rows and returns their column number
  for (var i =0; i < 2; ++i) {
    for (var j = 0; j < colHeaderData[0].length; ++j) {
      if (colHeaderData[i][j] == startCol) 
        startColNum = j;
      if (colHeaderData[i][j] == endCol) 
        endColNum = j;
    }
  }

  //This is the wrong command getColumnWidth doesn't change if column is hidden
  if (sheet.getColumnWidth(startColNum + 1) != 0) {
    sheet.hideColumns(startColNum + 2, endColNum - startColNum - 1);
    Logger.log(sheet.getColumnWidth(startColNum + 2));
    return;
  }

  //This is the wrong command getColumnWidth doesn't change if column is hidden
  if (sheet.getColumnWidth(startColNum + 1) == 0) { 
    for (var j = startColNum + 1; j < endColNum - 1; ++j) {
      sheet.unhideColumn(j);
      Logger.log(sheet.getColumnWidth(startColNum + 2));     
    }
    return;
  }
}

谢谢您的帮助!

Answer 1

新的(截至 2018 年)api 是: isColumnHiddenByUser(columnPosition)

返回给定的列是否被用户隐藏。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// Columns start at 1
Logger.log(sheet.isColumnHiddenByUser(1));