How to read data using Google query language and update using sheets api, when query doesn't return row numbers?

Question

What I want is to search one row in Google Spreadsheet and Update the value of one column. (When we have a large number of rows)

By looking at the main requirement, it seems easy. Yes of course. There are easy and straight forward ways to do that. But, when we have more data(rows) in spreadsheets around 100,000 rows, the usual methods are very slow. I was able to search for data using Google Query Language and it is a very efficient way (less than 1 sec for more than 50,000 records)

Now Google offers batch update mechanism, and we have to set the range in order to update the data.

But if we use the query api to search the data, we will not get the row number and we don't know where to update. Google offered two independent solutions, but how to combine these solutions efficiently? (Especially for large number of records).

Also, Are there any alternate solutions I missed?

Answer 1

The easiest way is to add a column with row numbers. You can then use query to retrieve the rows, which will contain the row numbers as well.

Another way is to use text finder, whose performance is comparable/better than query.