• Goog sheets pain

    lion mclionhead04/12/2024 at 08:44 0 comments

    As usual with cloud/web app/server based software, goog sheets has shown more & more problems over time.  The biggest problem for PDF merge is the authentication glitches.

    Noted if it prompts for a login during a resync, it logs in successfully but fails to update any values.  You have to resync again.  It hasn't been easy to test since there's no easy way to make the login expire.

    Other than that, goog sheets doesn't allow entering multiple lines in a cell.  Openoffice allows entering multiple lines with ctrl-enter.  Multiple lines in goog sheets can be fenegled by pasting all the lines at once.  Then the problem becomes selecting or editing multiple lines, which entails copying the entire cell into another text editor.

    Browser based text editing has always sucked a bag of dicks.  PDFMerge & goog sheets both suffer in their text boxes.  Writing a spreadsheet program from scratch would be a big deal.  It's hard to imagine something as platform independent as the current system being any less bug ridden.  If it was just for Linux using scratch X11 calls, it would be solid.  These glitches are why bare X11 programs continue to persist.  The API is just abstracted enough to be more effective than a framework for a lot of tasks but just primitive enough to make a full GUI extremely laborious.

  • Search & replace

    lion mclionhead11/11/2023 at 20:05 0 comments

    Managed to do a tax return with it.  Running a new server.py for every form is a bit tedious but it's better than before. 

    1st big bug of the year was a failure to resync the values.  It stopped when it hit the 1st blank cell because if a get_cell returned no "values" tag, it assumed it needed to log in again.  The test needs to use something else to log in, but the goog doesn't provide any "values" tag for an empty cell. For now, it assumes if the "range" & "majorDimension" tags exist, the cell is empty.

    If it returns nothing, Mozilla retries 5 times before returning an error code.  It has to return __LOGIN or __BLANK so there's no way a sheet can contain those values.  Then, it draws a really small bounding box if there's no text or a space.  In the end, it writes BLANK when a cell is empty.


    The mane unknown is how many linked spreadsheet values it supports before it gets too slow.  It's really slow by 4.  The mane problem is goog sheets requires a unique GET for every link. 

    The world's most hated form showed it's already way beyond the old days.  It's still unknown of the context menu should always show PASTE OVER TEXT if an overlay is highlighted or only if the click is directly over a highlighted overlay.  Sometimes you want to copy multiple overlays by clicking outside the selection & sometimes you want to paste new text while something is selected.  It might just need both paste options in 1 menu.  The problem then becomes what ctrl-v does if something is highlighted.  It's less confusing if ctrl-v overwrites the selection.  Text editors deal with the problem with a single paste option.

    The biggest problem is when moving data in the spreadsheet, you have to manually search the form for every occurrence of that data & repaste it.  The number of occurrences can be ponderous in a tax form.  It almost defeats the purpose of synchronizing the spreadsheet.

    For now, it got a search & replace wedged in.  It just replaces all the cells at once. 

  • Keyboard shortcuts

    lion mclionhead11/11/2023 at 05:07 0 comments

    The trick with this is some keyboard shortcuts should stay the browser defaults, some shouldn't, & some can't override the defaults at all.  Only experience can tell.  There's no way to override ctrl-n.  There are a lot of corner cases.

    With zoom, an intuitive behavior is to press fit width which makes the button the active widget, then press ctrl-+/ctrl-= to zoom. That just zooms the button instead of the canvas.

    Another intuitive behavior is copying a text item & immediately pressing ctrl-v which overwrites the already selected text instead of creating new text. If multiple text objects are selected, it overwrites all of them with 1 of the text items.  The problem is it can't match the clipboard items with the selected objects so you have to remember to deselect before ctrl-v.

    Kind of a mess with all the keyboard shortcuts.  The idea is to make it as easy as possible to get started. 

    It would be nice to know the DPI of the current zoom in print mode. It could print the DPI next to all the zoom menu items.  The internet says PDF has no notion of DPI.  In reality, pdfjslib always seems to assume 72fpi for 100% zoom.  The menu just has to hard code 72 * the zoom factor.

    Some heroic javascript hacking got it to right justify the DPI.  With that, this program was just about done, pending ongoing bug fixes.  The real test wouldn't be until the W2 forms came in.  Kind of an insane amount of effort to do so little.

  • Row/Column shifting

    lion mclionhead11/09/2023 at 22:23 0 comments

    The least valuable & final planned feature was the row/column shifting.  These 4 operations of such lethal cunning were the only way to synchronize with shifting operations in the spreadsheet.  Goog sheet labels the columns letters & the rows numbers.  After Z, it goes to AA, AB, AC.  For even the most insane financial calculation, the lion kingdom would only go to Z so that was the plan. 

    Thus, there was a simple test form.  Helas, the shifting operations get really slow.  There definitely needs to be an hourglass or status symbol.

    After so many years of writing web apps, the lion kingdom finally remembered the tool box should go on top.  Somehow, the browser tripped up that convention.  It more easily allows the canvas to fill the window.

    Maybe lions got nostalgic for early 80's graphics programs. 

    There's bugger all footage of those early graphics programs, but up to at least 1983, they tended to put menus & command lines on the bottom.  The way the Images Paint System drew a palette right over the bottom looks hacky now but it was pretty slick in the day.  PDFMerge is about as primitive as those early graphics programs.

    Another idea was keyboard shortcuts for undo, redo, save, delete, cut, copy, paste, next page, prev page, zoom, select all.  Key trapping in javascript is a difficult thing.  You have to seek out every place a key could be trapped & manually disable it, replacing it with your own event handler.  Text boxes need different event handlers than canvases.  It's not like there's a concept of a top level event handler & returning true traps the event.

    It looks like goog sheet itself only traps pgup, pgdn in some widgets but not all.

  • Print

    lion mclionhead11/09/2023 at 07:47 0 comments

    For the print mode, it just draws the current page as a normal image.  The user can right click & save it as a PNG image at the current zoom level, adjust the zoom, navigate 1 page at a time.  

    The mane problem was handling mouse wheel zooming when the scroll bars were enabled.  It has to set new scrollbar positions in addition to computing a new X & Y from the previous scrollbar positions.  Another problem was indicating the current zoom level.  It was decided to just not support mouse wheel zooming in viewer mode, for now.  It's supposed to just save a PNG image.

  • cut, copy, paste operations

    lion mclionhead11/08/2023 at 03:27 0 comments

    The mane problem with clipboard operations is what goog sheets copies can either be a link or plain text.  What pdfmerge needs to copy is the relative X, Y, font size, the text, the link, multiple text objects.  Thus all the paste operations need to handle 2 cases: when the clipboard contains a JSON of text objects & when it contains plain text.  A paste operation for a new text object needs to create all the copied objects in the same relative positions.  A paste operation overwriting a single text area needs to write the 1st text in the clipboard.

    Thus we have ways of copying multiple text objects with the same relative positions to other positions & pages.

    Replacing just the text in an existing object with a copy of another object.

    Replacing the text in an existing text object with copied plain text.

    Pasting plain text as a new text object.

    Of course links to goog sheet cells can still be pasted as new text objects or pasted over existing text objects.

    It can't paste a range of goog sheet cells.  That would entail a whole suite of formatting commands to get the right line spacing.

  • Undo & redo

    lion mclionhead11/06/2023 at 00:37 0 comments

    It might be good enough without it, but the undo stack was the last big requirement.  The print function may or may not be a buster.  Another really nice but unessential feature is a grid.

    With the login sequence forcing reloads, the undo stack becomes eternal & server resident.  This would be a memory resident undo stack on the server.  Kill the server & lose the undo stack like a regular program.

    Lions have always saved an undo & redo stack.  In this case, the last redo buffer would also go on disk as a saved project.  Helas, when the user undid a level, the saved project would no longer be the current redo buffer & the browser would have to show a modified flag.  The big problem is the program has to save the project to handle a login attempt, which erases the undo history.  The mane things which cause a login attempt without the user editing anything are a page reload & the resync button.  It's almost useless beyond 1 level of undo.

    Thus began the battle of the apply buttons.

    Text entry & font size need apply buttons to propagate text from the user entry to the PDF & they need an edit button to propagate text from the PDF to the user entry.  It's a compromise a lot of PCB editors make to keep every keypress from expanding the undo stack.  Automatically updating the PDF while typing in text doesn't allow entering replacement text & later selecting a destination for it.  Automatically copying the PDF to the user entry upon selection created the problem of unintentionally overwriting previously entered replacement text.

    The mane reason font size & text contents need apply buttons is the chance of keypresses getting dropped because the program is busy saving undo buffers & redrawing.  It's not a problem to drop scroll wheel events but all the keypress events needs to be processed.

    A final reason for having to apply the text contents is the chance of a change in the text triggering an open auth login.  That would be very disruptive.

    The only automatic update is a mouse wheel change to the font size.  Undoing mouse wheel events for the font size is the hardest undo operation.  The undo operation is too slow to undo every incremental font size change but some clever programming can compress all the incremental size changes into a single undo & redo buffer.

    All this yielded a somewhat complicated system of edit & apply buttons.  Maybe there are more automated ways of handling text entry with asynchronous polling loops.

    That just leaves print, row, column, cut, copy operations.

    Finally, the killer bug is when a fetch fails, it kills the async chain & the busy flag is never unset.  The undo buffer is definitely hosed if there's a network error but there's a good chance the document is still preserved.

    Every fetch call needs a

    .catch(e => {

    to avoid crashing.

  • Service account blues

    lion mclionhead10/26/2023 at 18:41 0 comments

    Managing the oauth consent prompts was turning into an impossible dream.  The mane problem was the number of places the text drawing could be interrupted & the browser could have to be redirected to a consent prompt, then the text drawing could have to resume.

    The internet recommended using a service account for applications that used a server.  The general procedure for creating the service account is given on


    The trick is it generates a JSON file for the service account.  You have to copy the client_email from the JSON file to the access list for the spreadsheet.  That user has to be set to viewer mode.

    Helas, a similar oauth dance is required to get an access token for the service account, except the job of the consent prompt can be performed by the server without interrupting the browser. 

    Both the goog & the GPT drew blanks for this procedure.  There just aren't a lot of animals writing programs in their dorm rooms for accessing the goog.

    There was a buried php blob.


    After translating to python, this failed with 1 of 2 errors:

      "error": "invalid_grant",
      "error_description": "java.security.SignatureException: Invalid signature for token


    curl output={"error":"invalid_grant","error_description":"Invalid JWT Signature."}

    The only clue was + being replaced by spaces in the response.  == was replaced by \u003d\u003d

    The --trace-ascii [filename] option causes curl to save all the TCP data to a file, showing all the + & = were properly sent.


    After many dead ends, the service account idea got abandoned.  It actually is more laborious for the user to deal with the JSON file & access list, hence why they're probably not used.  The consent prompt only requires the user's existing goog account & the developer console.

    The problem with the consent prompt is the need to go into a login sequence in the middle of a redraw operation.  The easiest workaround, since it's a rarely used program by 1 lion, is to just save the project & deal with the login sequence when reading a spreadsheet fails.  99% of the effort is preventing an infinite loop if the login fails.  It requires multiple reloads.  Reload #1 loads the consent prompt.  Reload #2 loads the page with a code= argument in order to load the access token.  Reload #3 deletes code= from the URL so a user reload works.  A state variable in the project tells whether it's in a login sequence or a user reload so it doesn't get stuck reloading.

    Maybe there's a way to do it with popups or multiple html files.

    Every javascript program involving network calls becomes a nest of async calls with callbacks.  They tried to solve this with the promise notation, which lions believe just replaces the callback with a then() block.  If you want a busy flag to be set before the 1st async call & unset after the last async/promise in the chain, you're out of luck.  There's no mention of the 1st then() waiting for the last nested async.  The lion kingdom settled on just calling everything with await instead of using callbacks or promises.  The language doesn't stop you from forgetting to use await if the function is async though.

    Most language features these days are marketing tools for selling the language, yet programmers still have yet to believe languages are subject to the same business economics as beer & pizza.  For a 1 user program where no-one cares about the browser freezing, the mandate for network calls to be async has no purpose besides a marketing tool to sell the language.

    That got it to finally pasting & syncing...

    Read more »

  • Text editing

    lion mclionhead10/19/2023 at 07:24 0 comments

    The mane problem is it needs to show both spreadsheet cells & arbitrary text so a text editing widget is required.  Making a custom text editing widget with the full WYSIWYG, arbitrary font, highlighting, clipboard, arbitrary positions on the PDF was an unpleasant prospect.   The text editing in goog sheets is an impressive feat of javascript.  Lions just don't have the budget to create such a thing & it's just not a heavily used enough app.

    Much easier would be Gimp's original text tool, which required text entry in a standard GTK window while it drew the formatted text on the image.  There would be a compromise where the user highlighted text areas on the PDF but only edited text in the editing area.

    Then, basically you have a drawable with the full res PDF baked on & a bunch of drawables for each full res text object which can quickly be drawn on.  This can be zoomed & scrolled.

    According to the GPT, off screen buffers in javascript are canvases.  You've got  document.createElement('canvas'); for creating unlimited buffers.

    Then they're drawn on the same as on screen canvases & blitted like images.

    1 problem is PDF.js can't change zoom on a single page without fetching & decoding the entire file again.  It seemed acceptable for files up to 13MB.  

    A tedious process led to a minimal set of text editing commands.  The key word is minimal, yet even the minimal set is pretty comprehensive. Lions remembered the fascination with being able to draw a lasso box, move groups of objects, drag a document view around, 40 years ago.  What was remarkable then is now minimal functionality.

     The mane trick with this is differentiating user text from PDF text.  The lion kingdom settled on drawing boxes around user text.  It relies on a print mode to hide the boxes.  Selected text objects are red.   An artifact is that it can have text outside the PDF.  The most common way of changing the font size is wheeling over the size text.  The font is hard coded.

    The wrench in this plan is the need to reload to renew an access token.  The easiest solution is to automatically save after the access token fails & before loading the consent URL.  Most programs asynchronously save every few seconds, but that would entail tons of locking.  A minimal program can just require the user to save.

  • File handling

    lion mclionhead10/17/2023 at 07:33 0 comments

    The immediate problem was a lot of goofy restrictions on local file access from inside the browser.  It's hard to believe a system with so many fabricated rules is the standard for applications.

    PDF.js famously can't read pdf files from a hard coded local filename.  There are some diabolical workarounds on the internets.  

    Another problem is full paths to local files aren't available in the browser.  There's no way a project file could reference a local PDF file with an absolute path or have a list of recently opened files.

    The standard solution is to have the server manetain a virtual filesystem of its own.  POST a PDF file to the server & have PDF.js then read the copy on the server.  POST & GET project files on the server.  The trick with this is not being able to store PDFs & project files in convenient locations on the hard drive. 

    The quick & dirty way is passing a PDF file & project file to the server command line.  This requires running different servers on different ports to edit multiple PDF files.  Ideally 1 server would support multiple browsers, but it's not unthinkable to run a different server for every file & have the server automatically pick an unused port.  It would be minimal.  It would be a pain to have to remember the PDF file to load the project though. 

    There's a way to get PDF.js to read a byte array.  ChatGPT says replace pdfjsLib.getDocument('LM124.pdf') with pdfjsLib.getDocument({ data: pdfData })  If the javascript loaded the project file from the file dialog & then extracted the PDF from it, the problem would be restoring its state after the confirm dialog, given the different starting URL's.

    More likely, the project file will be passed to the server command.  Then the browser would POST a PDF file to copy to the project file on the server & GET the text from the server.  The program state would be stored on the server.  Cookies would cause multiple browser windows with different projects to share the same state.  Confirmed it by loading ultramap in multiple browsers.  The problem is having to provide the right port number to the browser.

    The project file would thus need the raw PDF, the text entries, the undo entries, & the current state of the browser.  The server would be all the storage while the browser would be the view controller.

    Manetaneing a 2nd copy of the PDF in a server project file ended up so dreadful, ended up just requiring an absolute path in a textbox which the project file could reference.  It might be better to make the path relative to the project file location or just store it as a bunch of PNGs on the server.  This would create either a bunch of PNG files or a project file nightmare.  The server still needs to have the latest state of the project file for page reloads.  There can't be a manual save button.