How to get rows from Google Sheets filtered by a column's value?

0
votes

I have a spreadsheet that looks like this : (this is just a snippet of my sheet, it might have 10,000 rows in the future)

spreadsheet image

What I want to do is, Get every row whose For Class (column) value = 7 - In above sample case, rows #3, #5, #8, #10 have the For Class value of 7, so I should get them.

Using this reference - https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get, I tried to get some data : (range is just Sheet3 because I want all the rows available)

trial image

But as you can see, since I haven't applied any filters, it just gives me everything - (I require that the majorDimension should be ROWS)

trial results

What should I do, in order to get all rows containing For Class = 7, keeping the majorDimension to ROWS?

2
curlgoogle-sheetsgoogle-sheets-api

2 Answers

1
votes

Issue:

There's no way to filter rows based on a column value during the Sheets API request. There's batchGetByDataFilter, but it only allows you to filter according to a specified range or according to DeveloperMetadata.

Workaround:

That said, you can easily filter that data after receiving the response from the API. I don't know what language you are using, but for example, if you were using Node, it could be something like:

const filteredRows = response["values"].filter(row => row[0] == "7");
console.log(filteredRows);

Where response corresponds to the API response from spreadsheets.values.get.

0
votes

I'm not really sure what you've got going on there. But if you just open a new sheet and place the following formula in A1 of that otherwise empty new sheet, it should produce the results I think you want:

=ArrayFormula({Sheet3!A1:F1; FILTER(Sheet3!A2:F,Sheet3!A2:A=7)})