Synchronize a PDF with a spreadsheset

ChatGPT was pretty useless at this.  Accessing goog sheet data begins with the goog cloud console,

https://console.cloud.google.com

A nest of thousands of acquisitions of things that were big for just long enough to make someone some money & then went nowhere.  Lions got oauth2 working in 2018 for AWS.  It seemed a lot simpler.  Reading goog sheet data from curl requires the browser to load a consent page with a URL

https://accounts.google.com/o/oauth2/auth?client_id=316481588438-4tqet7doqis04q8v4kfga3meb96ibstk.apps.googleusercontent.com&redirect_uri=http://localhost&scope=https://www.googleapis.com/auth/spreadsheets.readonly&response_type=code

https://youtube.com/watch?v=qwqJcyLQSSQ

There's a vijeo on creating several of the bits required to generate the consent URL, manely the client_id.  The consent URL is constructed from the auth_uri, client_id, redirect_uri, scope URI

https://youtube.com/watch?v=hBC_tVJIx5w

There's another vijeo showing how to get the access token with the client_id.

The app needs a scope URI for reading a spreadsheet: https://www.googleapis.com/auth/spreadsheets.readonly

The redirect_uri is the page the browser should load upon success.  It should point back to the javascript file.   It puts an authorization code in the URI which lasts only a few minutes.  The javascript program has to use the authorizaton code to immediately get an access token + refresh token.  This is where the goog starts throwing origin errors.  The token request has to be indirected through a server using curl.

The access token can then be used to read the spreadsheet for 1 hour.  This too must be done on the server.  The curl request to read a spreadsheet is

curl -X GET \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Content-Type: application/json"  \
"https://sheets.googleapis.com/v4/spreadsheets/$SPREADSHEET_ID/values/Sheet1!A1"

Helas, the Sheet1 identifier is not contained in a copied cell link.  For now, the program is going to be limited to Sheet1

Then it gives a JSON result

{

  "range": "Sheet1!A1",
  "majorDimension": "ROWS",
  "values": [
    [
       "CELL CONTENTS"
    ]
  ]
}

The internet says the app has to use a refresh token to renew the access token every hour.  There needs to be a complete infrastructure for managing the refresh tokens, access tokens, consent page in a cookie.  Sadly, the goog doesn't provide a refresh token for the lion kingdom.  Despite many random search results, the latest dance seems to be to reload the consent screen when the access token expires.  The goog then either shows the consent screen or provides another access token.

After much coersion, the lion kingdom managed to draw a spreadsheet cell on a PDF with the PDF copying changes from the spreadsheet.

--------------------------------------------------------------------------

Not counting the difficulty of accessing a goog sheet, there's an overall explosion of complexity.  It needs to handle the access tokens as transparently as possible by handling errors & transparently restoring state when the consent page exits.   It needs functions to shift cells, undo, redo, select a different font size for every cell, enter arbitrary text.  It's going to need a faster way than running curl for every cell.

Lions traditionally used ghostscript to convert a PDF to images.  It's now considered pretty germane.  For a pure browser program, the internet recommends PDF.js

https://github.com/mozilla/pdf.js

A few chatgpt prompts can create a quick demo of how to draw text on a PDF.  For editing, you want the PDF in a persistent canvas & all the text in another canvas, with the 2 composited in 1 step.  Then, the user can alter a text element without redrawing the PDF & every other text element.

The next trick was reading a goog sheet.  There's a diabolical process to get a service account key.  Then a GPT demo using curl was a fail.  GPT can generate another solution using node.js.  They generally involve many steps to create an API key & download an access token.  Helas, the GPT says accessing a goog sheet directly from a web app requires making the sheet public.

At best, it would require hard coding the service account key in the javascript or a javascript include, but if curl can supposedly access it, a very simple web server on another port could just do spreadsheet queries with its own copy of the service account key.  Any solution is going to require a simple python webserver in that case.

The glaring problem is any insertion or deletion in the spreadsheet won't be reflected in the PDF overlay.  It's almost easier to make a spreadsheet program from scratch.  There are so many javascript spreadsheet programs, it might be a favorite toy problem of programmers.  Helas, they vary in features & bugs. 

It may go down as another waste of time, but lions found no easy way to draw spreadsheet cells in arbitrary locations on images & have the image overlays automatically synchronize with the spreadsheet.   Numerical calculations, pasting of a single value in many locations are big wins a spreadsheet would bring to an image editor.

The closest might be a historic goog doc feature which allowed pasting goog spreadsheet regions in a document & then pasting an image under the text.  This feature seems to have been dropped & positioning required tedious placement of carriage returns & spaces.

There are fillable form PDF's., but these lack all the features of a spreadsheet.

To be sure, there are very few times lions need such a tool, but when they do, the routine for the last 25 years has been to load the PDF in Gimp & draw text over it.  The rapid decline of Gimp's text tool made this very painful & it also lacks the features of a spreadsheet. 

For a spreadsheet program, libre/open/star Office doesn't have any API for external programs to read its spreadsheets.  The file format wouldn't be worth decoding.  Goog sheets is the only thing lions can find.  The data has to stay online, they can delete your account whenever they feel like it, &  the goog monetizes your data to turn your apartment into an airbnb but whatever.  It should be trivial enough to change the source of the spreadsheet data later.

For the front end, lions leaned towards a server backed web application since that seems to be a current trend & is the easiest thing to share with any other animals crazy enough to use it.  It would be the most sharable thing, a python server & a javascript application, as slow as it might be.  Server queries are a complicated thing though.

A standalone javascript program might end up superior.  The user would only have to be concerned with a local project file that points to the spreadsheet & the images.  The great task would be  PDF to image conversion.