如何仅更改原始页“ Pasco”上function checkDate(row)
的(true)行的背景颜色?这可能吗?
关于脚本的一点点:
通过function getDateRange()
输入日期范围,检查“ Pasco”工作表中的所有行是否通过function checkDate(row)
满足该日期范围。如果它确实满足日期范围(true),则function filterRows()
实质上过滤“ Pasco”工作表中的行,并将其移至另一张“ Pasco副本”中。
提出问题的另一种方式是,如何获得工作表“ Pasco”中所有“ true”的行的范围。如果未按日期对“ Pasco”进行排序,则可能意味着多个范围,对吗?确定范围后,即可轻松更改背景。
如果要测试脚本,请创建两个表,“ Pasco”和“ Pasco副本”。在“ Pasco”中,从第2行开始,在第I列(第8列)下放置一些日期。查看正在执行的过滤。每次运行都会删除/创建“ Pasco复制”。
谢谢您的时间=)
var globalStartDate;
var globalEndDate;
function getDateRange(){
var startui = SpreadsheetApp.getUi();
var startprompt = startui.prompt('Start Date', 'Enter a date in m/d/y format', startui.ButtonSet.OK_CANCEL);
var startdate = new Date(startprompt.getResponseText());
var startdatemilliseconds = startdate.getTime();
Logger.log(startdate);
Logger.log(startdatemilliseconds);
globalStartDate = startdatemilliseconds;
var endui = SpreadsheetApp.getUi();
var endprompt = endui.prompt('End Date', 'Enter a date in m/d/y format', endui.ButtonSet.OK_CANCEL);
var enddate = new Date(endprompt.getResponseText());
var enddatemilliseconds = enddate.getTime();
Logger.log(enddate);
Logger.log(enddatemilliseconds);
globalEndDate = enddatemilliseconds;
}
function checkDate(row) {
Logger.log(row[8].getTime() <= globalEndDate && row[8].getTime() >= globalStartDate);
return (row[8].getTime() <= globalEndDate && row[8].getTime() >= globalStartDate); // Check column H
}
function filterRows() {
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = Spreadsheet.getSheetByName('Pasco');
var sheetdelete = Spreadsheet.getSheetByName('Copy of Pasco');
Spreadsheet.deleteSheet(sheetdelete);
Spreadsheet.setActiveSheet(sheet1);
Spreadsheet.duplicateActiveSheet();
var headers = 1; // # rows to skip
var sheet2 = Spreadsheet.getSheetByName('Copy of Pasco');
var range = sheet1.getDataRange();
var data = range.getValues();
var headerData = data.splice(0,headers); // Skip header rows
getDateRange();
var filteredData = data.filter( checkDate );
var outputData = headerData.concat(filteredData); // Put headers back
Logger.log(filteredData)
sheet2.clearContents(); // Clear content, keep format
// Save filtered values
sheet2.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
}
最佳答案
抱歉,我没有时间阅读您的代码并给您完整的答案,但是您可以添加循环以遍历工作表并将每行的背景色设置为“ true”。
在下面的脚本中,我假设A列为“ true”。
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var data = sheet.getRange(1, 1, sheet.getLastRow()).getValues();
var lastCol = sheet.getMaxColumns();
for (var i = 0; i < data.length; i ++){
if(data[i][0] == true){
sheet.getRange(i + 1, 1, 1, lastCol).setBackground('Yellow');
}
}
}
编辑
在过滤器行函数中调用
getDateRange()
后,插入此代码。 var lastCol = sheet1.getMaxColumns();
for(var i = headers; i < data.length ; i++){
if(data[i][8].getTime() <= globalEndDate && data[i][8].getTime() >= globalStartDate){
sheet1.getRange(i, 1, 1, lastCol).setBackground('Yellow');
}
}
您的过滤器行功能现在应如下所示:
function filterRows() {
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = Spreadsheet.getSheetByName('Pasco');
var sheetdelete = Spreadsheet.getSheetByName('Copy of Pasco');
Spreadsheet.deleteSheet(sheetdelete);
Spreadsheet.setActiveSheet(sheet1);
Spreadsheet.duplicateActiveSheet();
var headers = 1; // # rows to skip
var sheet2 = Spreadsheet.getSheetByName('Copy of Pasco');
var range = sheet1.getDataRange();
var data = range.getValues();
var headerData = data.splice(0,headers); // Skip header rows
getDateRange();
var lastCol = sheet1.getMaxColumns();
for(var i = headers; i < data.length ; i++){
if(data[i][8].getTime() <= globalEndDate && data[i][8].getTime() >= globalStartDate){
sheet1.getRange(i + headers, 1, 1, lastCol).setBackground('Yellow');
}
}
var filteredData = data.filter( checkDate );
var outputData = headerData.concat(filteredData); // Put headers back
Logger.log(filteredData)
sheet2.clearContents(); // Clear content, keep format
// Save filtered values
sheet2.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
}
关于javascript - 如何仅更改“true”的行的背景颜色?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42103059/