我能够通过在这个线程中使用Tanaike的方法搜索列来过滤出特定用户在第一次加载时显示的数据。搜索功能工作良好,但问题是,用户也可以搜索其他人的记录在谷歌工作表中,只要他们搜索的值存在于电子表格中。我正在想办法再加一个&&条件,只显示只匹配当前用户的用户名(在C列中)和搜索值的行。
function displayOwnRecordSearch(currentUser, searchedValue){
var spreadsheetId = "";
var sheetName = "";
var column = 3;
var [, ...data] = Sheets.Spreadsheets.Values.get(spreadsheetId, sheetName).values;
var arr = [];
if(searchedValue !== undefined) {
const validateText = (query) => {
let regex = new RegExp(searchedValue, 'i')
return regex.test(query)
}
data.forEach(d => {
if (validateText(d)) {
arr.push(d);
}
});
}else{
const validateText = (query) => {
let regex = new RegExp(currentUser, 'i')
return regex.test(query)
}
data.forEach(d => {
if (validateText(d[column - 1])) {
arr.push(d);
}
});
}
return arr;
}
在您的情况下,如何使用fiter
如下?
修改脚本:
function displayOwnRecordSearch(currentUser, searchedValue) {
var spreadsheetId = "###";
var sheetName = "###";
var columnForCurrentUser = 3; // Column "C".
var columnForSearchedValue = 5; // Column "E". This is from https://stackoverflow.com/q/75066197 (your previous question)
var [, ...data] = Sheets.Spreadsheets.Values.get(spreadsheetId, sheetName).values;
var arr = data.filter(r => (new RegExp(currentUser, 'i')).test(r[columnForCurrentUser - 1]) && (new RegExp(searchedValue, 'i')).test(r[columnForSearchedValue - 1]));
// console.log(arr); // You can check the value in the log.
return arr;
}
在这次修改中,根据您之前的问题,我使用
var columnForSearchedValue = 5;
作为searchedValue
的列。或者,我认为
var [, ...data] = Sheets.Spreadsheets.Values.get(spreadsheetId, sheetName).values;
也可以修改如下。var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName); var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
参考:
- 过滤器()