Gspread - Change Listener?

Question

I currently run a daemon thread that grabs all cell values, calculates if there's a change, and then writes out dependent cells in a loop, ie:

def f():
    while not event.is_set():
        update()
        event.wait(15)
Thread(target=f).start()

This works, but the looped get-all calls are significant I/O.

Rather than doing this, it would be much cleaner if the thread was notified of changes by Google Sheets. Is there a way to do this?

Baty

Answer 1

I was able to get this working by triggering an HTTP request whenever Google Sheets detected a change.

On Google Sheets:

function onEdit (e) {
  UrlFetchApp.fetch("http://myaddress.com");
}

Python-side (w/ Tornado)

import tornado.ioloop
import tornado.web

class MainHandler(tornado.web.RequestHandler):
    def get(self):
        on_edit()
        self.write('Updating.')

def on_edit():
    # Code here
    pass

app = tornado.web.Application([(r'/', MainHandler)])
app.listen(#port here)
tornado.ioloop.IOLoop.current().start()

I don't think this sort of functionality should be within the scope of gspread, but I hope the documentation helps others.

Baty

Answer 2

I rephrased my comment on gspread GitHub's Issues:

Getting a change notification from Google Sheets is possible with help of installable triggers in Apps Script. You set up a custom function in the Scripts editor and assign a trigger event for this function. In this function you can fetch an external url with UrlFetchApp.fetch.

On the listening end (your web server) you'll have a handler for this url. This handler will do the job. Depending on the server configuration (many threads or processes) make sure to avoid possible race condition.

Also, I haven't tested non browser-triggered updates. If Sheets trigger the same event for this type of updates there could be a case for infinite loops.

Twylatwyman