Google电子表格：根据单元格值隐藏列 [英] Google spreadsheets: Hide columns based on cell value

问题描述

在我的Google电子表格A中，我使用TRANSPOSE和IMPORTRANGE公式的组合来导入日历电子表格B中的数据，以填写事件的工作时间表。因为在每个日期有3个事件插槽，并不总是被填满，我收到了很多过时的列。

In my google spreadsheet A, I use a combination of the TRANSPOSE and IMPORTRANGE formulas to import data from calendar spreadsheet B, in order to fill out a working schedule for events. Because on each date there are 3 event slots, which are not always filled, I am getting a lot of obsolete columns.

表格布局：

Table layout:

row1:  01-01-2013  01-01-2013  01-01-2013  02-01-2013
row2:  Event_ID    Event_ID    Event_ID    Event_ID
row3:  Event_name  Event_name  Event_name  Event_name

第1行和第2行包含自动生成的日期和event_ID，所以这些从不为空。细胞nr。 3在没有事件添加到该槽时显示为空，但事实上，在那里有一个CONTINUE公式，用于从单元格A1继续importrange-formula。

Rows 1 and 2 contain auto-generated dates and event_ID's so these are never empty. Cell nr. 3 displays empty when there was no event added to that slot, but in fact there is a CONTINUE-formula in there to continue the importrange-formula from cell A1.

I正在寻找一个脚本来自动隐藏在哪个单元格中的柱子。 3不包含导入的数据。

I´m looking for a script to automatically hide the colums in which cell nr. 3 doesn't contain imported data.

没有理解关于JavaScript的事情（但愿意学习），我试图合并来自现有脚本的部分，但此时我不能管理这些代码......

Not understanding a thing about JavaScript (but willing to learn), I have tried to combine pieces from existing scripts but at this point I cannot manage to make any sense of these codes...

推荐答案

以下代码可以解决这个问题：

The following code does the trick:

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "Hide columns", functionName: "hideColumn"},
    {name: "Show all columns", functionName: "showColumn"}];

  // add to menu
  ss.addMenu("Check", menu);
}

function hideColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn()+1;

  Logger.log(lastCol);

  // itterate through columns
  for(var i=1; i<lastCol; i++) {
     if(data.getCell(3, i).getValue() == '') {
        sheet.hideColumns(i);
     }
  }
}

function showColumn() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn();

  // show all columns
  sheet.showColumns(1, lastCol);
}

1. 打开文件时创建菜单


2. 第一个菜单选项隐藏第三行为空的所有列


3. 第二个选项显示所有隐藏列
   

1. Creates menu upon opening file
2. First menu option, hides all columns with row 3 being empty
3. Second option, shows all hidden columns

请参阅示例文件以准备观看它的实际操作：根据单元格值隐藏列

See example file I prepared to watch it in action: Hide columns based on cell value

这篇关于Google电子表格：根据单元格值隐藏列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！