Fetch rows based on specific conditions

[deepak-terse]

I'm using google sheets as a database for one of the projects, with the records are less likely to change once they are inserted.

I am going to maintain a copy of the google sheets locally in SQL Lite. So, I want to have a mechanism where I will be checking for updated rows and only fetch those rows than fetching all rows. Coz, fetching all rows will be too resource-intensive if rows go over 100 or 1000. For this, I need to maintain the last updated date for all records which I will compare with that in the google sheet.

For this mechanism to work, I need to query the google sheet to fetch rows that match a certain condition. So, is there a way to achieve this with this or any other approach?

[a-marenkov]

Hi! Thanks for checking out gsheets library!

Unfortunately, there is no support for such functionality.

How come fetching all rows will be too resource-intensive?

There is method for fetching all rows in one request.

You can simply update all data once on each application start.

[deepak-terse]

Yes, there is a method to fetch all rows in one request, which I have been using right now. But, I am using google sheets as a dictionary database. so, the number of rows might easily go over millions as we add more records. So, querying each time over a million record for a single record will take a longer time to load.

I know that Google sheets are not designed to use as a complete database and might have limitations w.r.t. I/O operations. And I should be using a proper database instead.

I am just playing around with Google sheets and checking out its capabilities to see what kind of applications could be build using Flutter - gsheets

[JanRegent]

I am using google sheets as a dictionary database. so, the number of rows might easily go over millions as we add more records. So, querying each time over a million record for a single record will take a longer time to load.

I am using =query(..) in A1 cell of other temp sheet (pasted programatically)
And do allRows on temp sheet..

[deepak-terse]

That's a good workaround but it might not work in my case. I will explain why? My approach is as follows:

1. I will have a local copy of the sheet data in my flutter app SQLite database with lastUpdatedAt let's say 5th June, which means my database is updated until 5th June.

2. While querying the google sheets, I will be sending the lastUpdatedAt field as let's say filterDate

3. Then I want to fetch the records from the google sheets where lastUpdatedAt > filterDate. This will return only those rows that were modified after 5th June.

4. Once I receive all the records successfully, I will update the lastUpdatedAt locally as current date.

Now, the challenge over here is that the lastUpdatedDate might be different for different users, so I can't use the query() method over here.

[JanRegent]

lastUpdatedDate might be different for different users, so I can't use the query() method over here.
My understandig of =query() is

• filtered subcollection is collection of pointers to source rows
• update of source row is refreshed to subcollection
  try it

[JanRegent]

Do you have a working snippet or documentation on how to do it with gsheets.

sample code in Dart

String query = '=query('${selectRow.fromSheet}'!A:$lastDataCol, "select $selectedColumsLetters where $whereColumnLetter ${whereExp.whereOperator} '${whereExp.whereValue}' ")';

toSheet ??= spreadsheet.worksheetByTitle(selectRow.toSheet);
await toSheet.clear();
await toSheet.values.appendRow([query]);