如何根据Google文件电子表格中的当前日期为行设置背景颜色? [英] How to set background color for a row based on current date in Google Docs Spreadsheet?
问题描述
我有一个Google文件电子表格,其中A列为日期(A1:2013-11-22,A2:2013-11-23,A3:2013-11-24等)。我想自动突出显示 - 为行设置一个背景颜色,A列中的哪个位置是今天的日期。
我希望我需要一个脚本,恕我直言,这不可能在Google Docs SpreadSheet中用条件格式化来完成。 / p>
任何想法如何操作?谢谢!
我修改了一个来自Serge的例子(谢谢,Serge!),日期位于A列。具有日期的行具有清除背景色,其他行完好无损。 Bonus:自定义菜单,用于在活动工作表上运行脚本。
/ *检查单元格格式* /
函数isValidDate(d){
if(Object.prototype.toString.call(d)!==[object Date])
return false;
return!isNaN(d.getTime());
$ b / *检查单元格格式* /
函数colorRow()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
customOnOpen(sh);
$ b $ *设置背景 - 主函数,sh是一个表单* /
函数customOnOpen(sh){
var headers = sh.getRange(1 。,1,sh.getLastRow())的GetValues();
var today = new Date()。setHours(0,0,0,0);
for(var n = 0; n var date = new Date(headers [n] [0])。setHours(0,0,0,0 );
Logger.log('Test row'+ n);
if(date == today){
Logger.log('Set bg at'+ n);
sh.getRange(n + 1,1,1,sh.getMaxColumns())。setBackground('yellow');
}
else
{
if(isValidDate(headers [n] [0])){
Logger.log('Clear bg at'+ n);
sh.getRange(n + 1,1,1,sh.getMaxColumns())。setBackground(null);
}
else {
Logger.log('不是日期'+ n);
$ b函数onOpen(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
$ b $ / *准备自定义菜单* /
var entries = [{
name:设置背景,
functionName:colorRow
} ]。
sheet.addMenu(我的菜单,条目);
/ *运行两个特定工作表的功能* /
customOnOpen(sheet.getSheetByName('Sheet1'));
customOnOpen(sheet.getSheetByName('Sheet2'));
};
I have a Google Docs SpreadSheet, where in the column A are dates (A1: 2013-11-22, A2: 2013-11-23, A3: 2013-11-24 etc). I would like to automatically highlight - set a background color for a row, where in the column A is today's date. To have every day a different row highlighted.
I expect that I will need a script, IMHO this is not possible to be done with conditional formating in Google Docs SpreadSheet.
Any idea how to do it? Thanks a lot!
I have modified an example from Serge (thanks, Serge!), dates are in the A column. Rows with a date have background color cleared, other rows are intact. Bonus: a custom menu to run a script on active sheet.
/* check for a cell format */
function isValidDate(d) {
if ( Object.prototype.toString.call(d) !== "[object Date]" )
return false;
return !isNaN(d.getTime());
}
/* check for a cell format */
function colorRow()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
customOnOpen(sh);
}
/* set the background - main function, sh is a sheet */
function customOnOpen(sh) {
var headers = sh.getRange(1,1,sh.getLastRow()).getValues();
var today = new Date().setHours(0,0,0,0);
for(var n=0;n<headers.length;++n){
var date = new Date(headers[n][0]).setHours(0,0,0,0);
Logger.log('Test row '+n);
if(date==today){
Logger.log('Set bg at '+n);
sh.getRange(n+1,1,1,sh.getMaxColumns()).setBackground('yellow');
}
else
{
if (isValidDate(headers[n][0])){
Logger.log('Clear bg at'+n);
sh.getRange(n+1,1,1,sh.getMaxColumns()).setBackground(null);
}
else{
Logger.log('Not a date at'+n);
}
}
}
}
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
/* prepare the custom menu */
var entries = [{
name : "Set background",
functionName : "colorRow"
}];
sheet.addMenu("My menu", entries);
/* run the function for two specific sheets */
customOnOpen(sheet.getSheetByName('Sheet1'));
customOnOpen(sheet.getSheetByName('Sheet2'));
};
这篇关于如何根据Google文件电子表格中的当前日期为行设置背景颜色?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!