Search code examples
pythongoogle-sheetsgoogle-drive-apigoogle-sheets-apigspread

Using Python's gspread, can I read the modifiedTime without fetching the entire spreadsheet? I want to use a local cache if there was no change

I'm using gspread to load read from my spreadsheets and it's working great. However, I'm hitting Google data caps quite often, especially when I'm testing.

I created a caching method that uses a local copy if it is less than a minute old. However, when debugging, I often have to change things in the spreadsheet and see an immediate change in the execution of the code. On the other hand, because not all calls during debugging need a fresh copy, I'll hit the data caps quickly if I remove the caching.

Is there a way to check if the spreadsheet has changed before fetching it? I know there is a field modifiedTime in _properties of a spreadhseet, but it doesn't seem to have an accessor method. This suggests to me that there is a mechanism for caching hidden. Is there one.

It also seems to me that to populate modifiedTime you need to read the entire spreadsheet anyways. Is that true?

So here is the question: how do I get the last modified time of a spreadhseet without using my data cap? Preferably using only gspread?

Solution

  • Issue and workaround:

    In the current stage, it seems that the property of modifiedTime of "Method: files.get" is not correctly updated by editing cells. Ref (Author: me) So, in this case, "Method: revisions.list" is used for checking the updated time.

    In this case, I think that the answer to It also seems to me that to populate modifiedTime you need to read the entire spreadsheet anyways. Is that true? is no.

    Also, about So here is the question: how do I get the last modified time of a spreadsheet without using my data cap? Preferably using only gspread?, I think that googleapis for Python can be used with the client of gspread.

    The sample script for checking the last modified time of the Spreadsheet is as follows.

    Sample script:

    import gspread
from googleapiclient.discovery import build

client = ### # Please set your gspread client.
spreadsheetId = "###" # Please set your Spreadsheet ID.

drive = build("drive", "v3", credentials=client.auth)
revisions = []
pageToken = ""
while pageToken is not None:
    res = drive.revisions().list(fileId=spreadsheetId, fields="nextPageToken,revisions(modifiedTime)", pageSize=1000, pageToken=pageToken if pageToken != "" else None).execute()
    r = res.get("revisions", [])
    revisions += r
    pageToken = res.get("nextPageToken")
lastModifiedTime = revisions[-1]["modifiedTime"]
print(lastModifiedTime)
    • When this script is run, the last modified time of the Spreadsheet of spreadsheetId is retrieved like 2023-12-21T00:12:34.567Z.

    Note:

    Reference: