Is it possible to get rows with last modification date > certain date with Java Google Sheets API

Question

I have a sheet where data is updated every day. I am using Java Sheets API v4 to load the data in my java application, I need to do incremental loads on my sheet. Is there a way to do this efficiently?

This is my call, but this loads all data every-time, is it possible to add a filter to get rows updated after a certain date? (considering table doesn't have a timestamp column, can I use insertion/modification date of the row?)

ValueRange response = service.spreadsheets().values()
            .get(spreadsheetId, range)
            .execute();

Also in case there is a timestamp column, is there a way to filter data before loading?

The server doesn't filter, you must filter locally. "Structured queries" were removed in the v3->v4 transition. Probably what you should do is query your "key" column only, inspect that to find out which values to pull, and then use a batchget for the values in the associated rows you want to pull. You might also be able to leverage other Drive APIs to examine the audit logs and determine which rows or sheets or w/e were modified. Fundamentally though, spreadsheets are not a database so they don't care when a row was last modified. — tehhowch
@tehhowch Have you tried the batchGetByDataFilter? Is it possible to set it to where column value =x? or how to use the query language from within java sheets API — Selim Alawwa
I haven't used it; looking at the resource schema is enough to convey that you must already know the ranges you want to retrieve. You cannot execute a "query" against the Sheets API v4 endpoint. A "query" includes "where column value = x" — tehhowch

Raserhin Raserhin · Accepted Answer · 2020-01-22T10:05:52

I'm afraid there is no in-built way to actually do this through the API. As right now the cells don't hold a date modified field.

You will need to keep track of the modifications yourself and then retrieve the cells in the ranges and check against your log of modifications.

Is it possible to get rows with last modification date > certain date with Java Google Sheets API

1 Answers