Filter by id

[Pgs Ss]

Hi everybody.
I have a spreadsheet, it's first column is id column.
I want to gey all rows tha have id=1 (In SQL: select * from  Users  where id=1)

I tried this code but doesn't work, what am I doing wrong?

function filterRows() {

  // Get the sheet by name

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const ws = ss.getSheetByName("Users")

 

  // remove filter

  const f = ws.getFilter()

  if(f!=null){ws.getFilter().remove()}

  // Define the range to filter (in this example, column B)

  const range = ws.getRange('B1:B');

 

  // Create the filter criteria (in this example, the value '1' in the first column)

  const criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo('1').build();

 

  // Create the filter and apply it to the range

  const filter = range.createFilter();

  filter.setColumnFilterCriteria(2, criteria);

 

  // Get the filtered rows

  const filteredRows = range.getValues().filter(row => row[0] === '1');

 

  // Log the filtered rows to the console

  console.log(filteredRows);

  filter.remove();

}

[Brett Grear]

I'm not sure because I haven't tried it but you've put the Param for .whenTextEqualTo() to a string. Should you remove the ' ' and make it an int?

[Brett Grear]

Also, what is the purpose of filtering in sheets if you just remove it straight away. Why not get the data as an array and filter in apps script?

[Pgs Ss]

I tried it, makes no difference.

[Pgs Ss]

I suppose it's better to filter the data in the source (the sheet), it before retreiving it.

[Brett Grear]

Not if you want your function to run quickly. The only reason to filter in sheet is if you want it to stay that way.

const range = sheet.getDataRange().getValues();

const filtered = range.filter(row=>{

  return row[1]==1;

});

console.log(filtered)

Sorry if there's any typos. I'm on my phone and autocorrect hates code.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/x87vPxQ5a_A/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/876eb914-2b79-40cc-9754-475abd36c84fn%40googlegroups.com.

[Brett Grear]

Or try doubles equals == instead of triple in your filter function 

[Pgs Ss]

1.Yes I want it to be quick of course, so I abandon the sheet filering. How shouls I do it?
2. Double equals didn't do anything

[Pgs Ss]

This seems to work only in the opposite way than what I want (gets me all roaws BUT the one that is equl to '1'):

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users")

  var data = sheet.getRange("A:A").getValues().flat().filter(a => a != '1')  

  console.log(data)

[Brett Grear]

What is it you want your code to do eventually? All it does right now is log the filtered range then delete the filter?

What is logged when you run currently or is there an error message?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/dc38831e-ab8b-430c-b00d-3b242fc72f31n%40googlegroups.com.

[Pgs Ss]

I want to log the rows that have in theis first column value=1.

It now logs the values (not the range) of the rows that have in the first column value<>1.
There is no error, nor when I rerun the code, which means the filter is not applied on the sheet.
(I 'm referring to my last code)

[Pgs Ss]

Ok, I found it:

function getRowsEqualToOne() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Meals");

  const data = sheet.getDataRange().getValues();

  const rowsEqualToOne = [];

 

  for (let i = 0; i < data.length; i++) {

    if (data[i][0] == "1") {

      rowsEqualToOne.push(data[i]);      

    }

  }  

 

  console.log(rowsEqualToOne)

}

But, is it the 'correct' (=most efficient) way to do it?

[Brett Grear]

Close enough! Although you may need  data[i][1] if your data is in column b

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5f6c7eae-7c5c-45d1-845b-ca6f06c09232n%40googlegroups.com.