https://gspread.readthedocs.io/en/latest/ is great, but it's limited in what kinds of things it can set (formatting, notes, etc.). One other option is to create an endpoint on script.google.com which can access a much richer SpreadsheetApp API: https://developers.google.com/apps-script/reference/spreadsh... . You can POST to it using an auth token from the same service account oauth creds (though you need to add drive and drive.scripts to your scopes), and it can run arbitrary JS to translate reads and writes from the JSON payload/response into API calls.

As another note, we realized that far more useful than using Google Spreadsheets as the canonical backing database, was to be able to bidirectionally synchronize it with our primary database. That way, users who wanted to annotate entities in spreadsheet form could do so in GSheets, always working with up-to-date data, and keeping track of "I updated a.x in the spreadsheet, but a.y was updated upstream, so merge the two." Here were the semantics of our integration:

    Returns a list of updates between last_synced_data and sheet.
    Subsequently, if upstream_data is provided, then load it into the sheet,
    adding rows on the end as needed, or merging if there is a match in the merge_key column
    (note that any updates to the live sheet data since the last sync
    override any upstream data, and those live updates are returned without changing the live sheet).

The caller would then be responsible for taking the returned list of updates and cleaning it for the database, as well as maintaining a record of what the state of the last sync was. Essentially we maintain enough information to do a three-way merge. We've since built internal applications that allow real-time spreadsheet-like interactions in a much more domain-specific manner, but it definitely did the job for quite a while.

If there's interest in seeing open-source code for all of this, we could definitely extract from our corporate repo (we're https://www.belstone.com/ ). Let me know!