TinyWebDB - Encrypted - on a Spreadsheet



Following on from THIS, where I used a php server to deliver a tinywebdb like experience, I then thought about providing much the same in a different way, but this time using google apps script, a web app, and a spreadsheet. The encryption element is slightly different, requiring a pin code of between 1 and 8 digits (no letters), but the data on the spreadsheet is encrypted, and again of no use to anyone who does not have the pin code. In testing I have been using a five digit pin. The user creates their own spreadsheet with a unique name, for their own use, although, at this time, and because of the way the web app is set up, the spreadsheet is still owned by me...of no use to me as I do not have the pin code! One day I will get around to figuring out how a user can set up their spreadsheet / other files, on their own google drive, but Google have made this difficult at the moment.

This approach is not designed for "big data", but for appropriate small scale use by individuals requiring an encrypted but accessible data storage solution.


REQUIREMENTS

  • The google apps script published, for anyone, even anonymous to use

  • The FOLDERID, the folder should be set to "anyone can find and view" - this FOLDERID should be added directly to the script in this example

  • User to set a pin code on first run, this should be one time only, and they should only use this one pin code to access their encrypted data

  • User to create their own spreadsheet on first run

  • The web app uses GET, so all web component calls should be with the Web1.GET request

  • A good understanding of the manipulation of the web component gotText event


KNOWLEDGE

  • The web app provides: STORE, GET, GET ALL TAGS, GET ALL DATA, DELETE TAG (and value), EMPTY, and CREATE

  • Web App calls can also be run in a computer browser, using the correct syntax

  • Equipped with the pin code, it is possible to decrypt the data on a/the spreadsheet using the custom functions used at the bottom of the project - encdec(), base64Decode()

  • When the user "empties" the database, a copy of all entries, still encrypted, is made to a hidden Backup sheet on their spreadsheet. Users can, of course, call back all data decrypted and save to their device

  • Things could be setup for the user to make a copy of their spreadsheet to their own google drive, amending the SHEETID in the web calls (their folder would need to be read/write for the web app)

  • A google sheet cell currently has a character limit of 50,000 characters, so storage of base64 encoded images or other files would not be appropriate

  • All returns from the database are in json array format for conversion to AI2 lists, using a similar model to that in the tinywebdb component

  • Again, no need to introduce a "lock" as the spreadsheet is intended for a single user. The web app should cope with multiple calls.

SHEET

What things could look like on the spreadsheet (the data is from use of two different pin codes....)

BLOCKS

(example barebones)