如果表格中的日期早于一周，则使用Google Sheet脚本隐藏行 [英] Google Sheet script to hide rows if date in column older than a week

问题描述

我想要一个脚本，如果F列中的日期早于一周，则该脚本将隐藏行.我以为我找到了一个，但是它也隐藏了列的标题(第2行)，这没有用.

I'd like a script that hides rows if the date in column F is older than a week. I thought I found one but it hid the headers of the columns (row 2) as well which is no use.

我不希望它隐藏空行，因为它们会不断更新.

I don't want it to hide empty rows as they will be continuously updated.

非常感谢.

推荐答案

此函数检查列中的每个单元格以确保它是日期，然后当它检测到7天之前的日期时将隐藏行.当然，您可以更改它，以便您可以根据需要选择天数.它要求您选择当前默认为"A"的列.我添加了菜单，只是为了让您更轻松地进行测试.

This function checks each cell in a column to insure it is a date and then it will hide rows when it detects a date older that 7 days. Of course, you can change it so that the number of days is an argument if you like. It requires you to select a column which is currently defaulted to 'A'. I included the menu just to make it simpler for you to test.

希望这会有所帮助.

 function onOpen()
    {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('My Tools')
            .addItem('Hide Rows','hideRowsDate')
            .addToUi();
    }

    function hideRowsDate(column)
    {
      var column = (typeof(column) !== 'undefined') ? column : 'A';
      var day = 86400000;
      var today = new Date().getTime();
      var rng = SpreadsheetApp.getActiveSheet().getRange(column + ':' + column);
      var rngA = rng.getValues();
      for(var i = 0; i < rngA.length ;i++)
      {
        if(isDate(rngA[i][0]) && (((today - new Date(rngA[i][0]).getTime())/day) > 7 ))
        {
          SpreadsheetApp.getActiveSheet().hideRows(i + 1);
        }

      }
    }

    function isDate (x)  
    { 
      return (null != x) && !isNaN(x) && ("undefined" !== typeof x.getDate); 
    }

这篇关于如果表格中的日期早于一周，则使用Google Sheet脚本隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！