Google表格-根据第1行的值隐藏列 [英] Google Sheets - Hide Column(s) based on Row 1 value

问题描述

我在Google表格上按列整理了一堆内容.我想要一个脚本，该脚本将检查从单元格4到100的第1行，以查看是否出现字母X.如果出现字母X，则脚本应隐藏该列.

I have a bunch of content on a google sheet organised by columns. I want a script that will check Row 1 from Cell 4 to 100 to see if the letter X appears. If the letter X appears then the script should hide that Column.

然后相反的是取消隐藏所有列，而不管第1行的内容如何.我已经找到了这段代码，但是它们似乎并没有真正做任何事情，没有显示错误或错误，只是没有隐藏列

And then also the reverse of that being unhide all columns regardless of Row 1 content. I have found this snippet of code(s) but they don't seem to really do anything, there is no failure displayed or error, just doesn't hide the column(s)

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(1, i).getValue() == 'x') {
        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();
  
}

我已经设法直接在Excel中对相同的操作进行编码，并且按照下面的代码，它们可以按照我希望的方式正常工作，但是，当我将.XLSM文件上传到Google云端硬盘时，Google不喜欢命令按钮或代码作为工作表运行....有什么想法吗?下面的Excel代码效果很好

I have managed to code the same actions in Excel directly and they work exactly as I want them as per codes below, however, google doesn't like the command button or code when I upload the .XLSM file to Google Drive and run as a Sheet.... Any ideas? Excel Code below that works well

Private Sub CommandButton1_Click()
For i = 4 To 30
If Worksheets("Current Orders").Cells(1, i).Value = "x" Then
Worksheets("Current Orders").Columns(i).Hidden = True
End If
Next
End Sub

Private Sub CommandButton2_Click()
For i = 4 To 30
Worksheets("Current Orders").Columns(i).Hidden = False
Next
End Sub

推荐答案

答案:

您可以使用 TextFinder进行此操作.

function hideColumnsWithx() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var cells = sheet.createTextFinder("x").matchEntireCell(true).findAll();
  
  cells.map(x => sheet.hideColumns(x.getColumn()));  
}

此功能的概要:

• 从活动电子表格中获取第一张纸
• 找到所有完全包含字符串"x"的单元格范围
• 使用array.map()
隐藏从TextFinder返回的每一列

• Get the first sheet from the Active Spreadsheet
• Find all cell ranges which contain exactly the string "x"
• Hide every column that is returned from the TextFinder using array.map()

希望对您有帮助！

• 类TextFinder-matchEntireCell(Boolean)|应用脚本Google Developers
• 类表-hideColumns(columnIndex | Apps脚本| Google Developers
• 类范围-getColumn()|应用脚本Google Developers
• Array.prototype.map() -JavaScript | MDN

• Class TextFinder - matchEntireCell(Boolean) | Apps Script | Google Developers
• Class Sheet - hideColumns(columnIndex | Apps Script | Google Developers
• Class Range - getColumn() | Apps Script | Google Developers
• Array.prototype.map() - JavaScript | MDN

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