How do I only search for a specific column but still show the whole row as array?-CodePudding

I currently have a search function that works but I am wanting to change it to search a specific column only as basis but still show that whole row's data the search matches. I believe I have to change the .forEach into something but I am not sure how. I tried changing the dataRange but if I do change it, it affects the whole row's data being displayed. As I want column E to be the column to be searched.

   var spreadsheetId   = "";
   var dataRange        = 'sheetName!A2:M';
   var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values;
   var arr = [];

    const validateText = (query) => {
    let regex = new RegExp(currentUser, 'i')
    return regex.test(query)
    }

    data.forEach(d => {
    if (validateText(d)) {
    arr.push(d);
      }
    })

CodePudding user response：

I believe your goal is as follows.

You want to search the value of the specific column and want to retrieve the searched row values using Google Apps Script.

Pattern 1:

When your script is modified, how about the following modification?

var column = 5; // Search column. 5 is column "E".
var currentUser = "###"; // Please set your expected value.
var spreadsheetId = "###"; //  // Please set your spreadsheet ID.
var sheetName = 'Sheet1'; // Please set your sheet name.

var [, ...data] = Sheets.Spreadsheets.Values.get(spreadsheetId, sheetName).values;
var arr = [];
const validateText = (query) => {
  let regex = new RegExp(currentUser, 'i')
  return regex.test(query)
}
data.forEach(d => {
  if (validateText(d[column - 1])) {
    arr.push(d);
  }
});
console.log(arr)

Pattern 2:

As another approach, how about the following modification?

var column = 5; // Search column. 5 is column "E".
var currentUser = "###"; // Please set your expected value.
var spreadsheetId = "###"; //  // Please set your spreadsheet ID.
var sheetName = 'Sheet1'; // Please set your sheet name.

var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
var res = values.filter(r => new RegExp(currentUser, 'i').test(r[column - 1]));
console.log(res)

Reference:

filter()