如果表格中的日期早于一周,则使用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屋!