Google sheets live sync

[yorschor]

Hi from reading the Documentation https://docs.getontime.no/features/import-spreadsheet-gsheet/ I thought that an authenticated sheets will sync both ways live. Is this supported? Or only the manual export and import preview that are existing currently?

[cpvalente]

Hi @yorschor, thank you for reaching out.

Once you are authenticated, the authentication process is valid for the remaining of the session (until you turn off ontime) which means you can manually trigger the read / write process.
This is a long way of saying that there is no automated synchronisation.

I would like to clarify this in the documentation, can you please point me towards the wording that lead you to believe that there is an automated process?

Some background

We have considered making the synchronisation process automated when writing this feature, the eventual decision was against it.
In my opinion, a manual process provides the most stable results and the least surprises. In case you care about the thinking, here are a few points:

Ontime is unpaid, as such we are unable to authenticate ourselves with google, and have to do it through the user account. This means that we are subject to restrictions imposed by google to free accounts. In practice this includes how easy, often and how much data we can get from google

To circumvent the above we could have a background process that gets the sheet and checks changes. Google does not tell us whether changes exist, so we need to periodically check the entire sheet. Depending on the size of your data we could run into issues with the above.

There were also concerns specific to workflows. Namely, in a production, who is the owner of the data in the google sheet, and who is the owner of the Ontime process? What would happen if the Google Sheet data is invalid or if a change affected the runtime?

I believe the Google Sheet integration is key to the usefulness of Ontime in production and would like this to be as good as possible. If you have any extra information or would like to contest the decision it would be very welcomed

[yorschor]

Hi @cpvalente thank you very much for the fast detailed response!
Ahh yeah I can see those points.

The reason why I had the hope that it was supported was because of the word sync. Importing/Exporting for me is a one way path while sync suggest something more dynamic in my mind. Of course with the authentication stying active I can see now how this could also be considered a sync option.

I for my myself would love to see an option to live sync it live. Perhaps with 3 options (Sheets to OnTime, OnTime to Sheets, Bidirectional) and a big fat warning about rate limits and potential data corruption. ^^

I can totally understand the design decision though to not bother with that.

That said is there a API point to trigger an import/export?
That would perhaps allow similar function to what I described above but the config and risk of how often and when to sync would fall to the user. In my case I already have a system in place that notifies multiple data sources to pull the newest information from sheets into OBS, Vmix and the sorts.

[alex-Arc]

That said is there a API point to trigger an import/export?

That might be something to look into
Currently it is a 3 step proses after authentication
POST -> /sheets/<sheetid>/worksheets -> returns a list of valid worksheets
POST -> /sheets/<sheetid>/read (the body contains the a import map with the chosen worksheet) -> returns a parsed rundown
PATCH -> /db you then apply the rundown to the database

Mind you these are currently internal api's maintained for the UI so they a liable to change

[yorschor]

That might be something to look into

That would be super useful 😁

Hmm ok ill give it a try. For a production next week that might be too janky though :D

I appreciate the support!

[alex-Arc]

For a production next week that might be too janky though

yes definitely