Spreadsheet Editor

[seth-shaw-unlv]

For bulk-editing/creating records our staff would like a spreadsheet-like experience with one row per record. Fields with multiple values (e.g. subjects) will need a delimiter. (Our staff are used to the CONTENTdm's semicolon delimiter convention although Drupal's entity autocomplete uses commas.)

There are a number of options out there, as noted by Fancy Grid's awesome-grid, so the question becomes "which one?"

A few features that we would like to see include:

• "fill down" option for quickly duplicating row values
• "read only" cells (so we don't accidentally change node ids when doing updates)
• image display support for showing thumbnails for the item the row corresponds to
• auto-complete (admittedly, we probably won't get this for free, but some sort of plugin support so we can build our own)
• auto-complete/drop-down support for multiple values
• grouping (for complex objects)

Early on I was keen on Handsontable, but they recently changed from an MIT license to a commercial license as of version 7.x. I've received confirmation from their sales team that we could use their "non-commercial license key", however we would need to be very clear that the workbench is not 100% open source and can't be re-released commercially without paying. We could take the Sakai approach and simply fork their 6.2.2 release (the last using MIT), or rely on Sakai's fork.

It may be better to just stick with another open source project. Options I'm considering are DataTables and x-spreadsheet.

[seth-shaw-unlv]

Scratch DataTables. It requires a paid extension for editing data.

[mjordan]

@seth-shaw-unlv excellent feature. This could just write the CSV file out, which workbench could then use.

[seth-shaw-unlv]

Current contender is jExcel (MIT license).

[mjordan]

@seth-shaw-unlv this will get you all the terms in a vocabulary (here using the islandora_models vocab as our example):

curl -H 'Accept: application/vnd.api+json' http://admin:islandora@localhost:8000/jsonapi/taxonomy_term/islandora_models

The editor UI could populate pick lists from these values.

[seth-shaw-unlv]

@mjordan can the ingester take a base64 encoded jpeg as a column value (to populate a thumbnail)? jExcel can store an image in base64 as a cell value which is useful for metadata creators as they work with a large spreadsheet view of items. We could have the workbench generate thumbnails from the master files it finds and populate the spreadsheet with them.

[mjordan]

So instead of a filename, the cell value would be a base64 string? I don't see why not, as long as the string was escaped for CSV. Then, when it comes time to do the ingest, workbench could use something like this to detect if the cell value is base64 and if so decode it for pushing up into Islandora.

[seth-shaw-unlv]

The cell value would look something like data:image/jpeg;base64,/9j/4AAQSkZJRgAB.... So, basically, if our cell value begins with 'file:' we would expect a directory path but if it begins with 'data:' we would expect a mime-type, semicolon, encoding, comma, then the encoded data.

[mjordan]

Could we skip the 'file' for ordinary filenames? Otherwise, users of the command-line tool would need to add that. In other words, only add 'data:' if it's encoded data.

[seth-shaw-unlv]

Fine by me. I'm just brainstorming ideas at this point.

[mjordan]

I'd like to make the command-line tool usable on its own, without a GUI. But, also I want to make it as easy to fit a GUI on top of as possible. 😄

[seth-shaw-unlv]

It looks like jExcel is going to be a good option. I've created a repo as a scratch pad to play with how it works until we have the Electron app skeleton in place.

You can checkout the work in progress by cloning the repo and then opening the spreadsheet.html in your browser. It will even populate a subjects field dropdown if you have an Islandora instance running at localhost:8000 with some subject, geo_location, people, family, and/or corporate_body terms. Clicking the floppy-disk icon will download whatever you have in the spreadsheet as a CSV to your computer.

[mjordan]

I will check this out tonight!

[mjordan]

Got my CORS configured. Spreadsheet loads well, but I am experiencing some strangeness with my JSON:API. It is suddenly (for no apparent reason) returning a "Some resources have been omitted because of insufficient authorization." message and not allowing the admin user to GET stuff. Very strange, and unrelated to your spreadsheet app. Please stand by while I figure this out. Maybe @dannylamb could test in the meantime.

[mjordan]

Web Console indicated that the Access-Control-Allow-Origin header was missing from my CORS config, so I added it (in web/sites/default/default.services.yml), did a drush cr, but the error in Web Console remains. Here's the CORS section of my default.services.yml file:

   cors.config:
     enabled: true
     # Specify allowed headers, like 'x-allowed-header'.
     allowedHeaders: ['Access-Control-Allow-Origin', 'x-csrf-token','authorization','content-type','accept','origin','x-requested-with']
     # Specify allowed request methods, specify ['*'] to allow all possible ones.
     allowedMethods: ['*']
     # Configure requests allowed from specific origins.
     allowedOrigins: ['*']
     # Sets the Access-Control-Expose-Headers header.
     exposedHeaders: false
     # Sets the Access-Control-Max-Age header.
     maxAge: false
     # Sets the Access-Control-Allow-Credentials header.
     supportsCredentials: false

Is there anything else I need to do?

[seth-shaw-unlv]

Copy `default.services.yml` to `services.yml` then enable CORS in `services.yml` then `drush cr`. (Out for USA holiday.)

…

On Thu, Jul 4, 2019, 6:57 AM Mark Jordan ***@***.***> wrote: Web Console indicated that the Access-Control-Allow-Origin header was missing from my CORS config, so I added it (in web/sites/default/default.services.yml), did a drush cr, but the error in Web Console remains. Here's the CORS section of my default.services.yml file: cors.config: enabled: true # Specify allowed headers, like 'x-allowed-header'. allowedHeaders: ['Access-Control-Allow-Origin', 'x-csrf-token','authorization','content-type','accept','origin','x-requested-with'] # Specify allowed request methods, specify ['*'] to allow all possible ones. allowedMethods: ['*'] # Configure requests allowed from specific origins. allowedOrigins: ['*'] # Sets the Access-Control-Expose-Headers header. exposedHeaders: false # Sets the Access-Control-Max-Age header. maxAge: false # Sets the Access-Control-Allow-Credentials header. supportsCredentials: false Is there anything else I need to do? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#8?email_source=notifications&email_token=AHD4PJEECKM6BKLL5RFRMVLP5X6T5A5CNFSM4H4TSSOKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZHPQLQ#issuecomment-508491822>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHD4PJCVFDGTUTZBP6JGD63P5X6T5ANCNFSM4H4TSSOA> .

[mjordan]

Frickin sweet:

[dannylamb]

Ermehgerd.... gotta try this out!

[dannylamb]

Haha, yep! Enabled CORS and turned on the JSON:API module and blammo. Worked just fine.

Can't wait to see where this leads.

[mjordan]

I think we should tell people over in Islandora/documentation#1172 about this, it's so kule.

[mjordan]

@seth-shaw-unlv do you want me to proceed with generating a thumbnail and encoding it in base64 as described in #8 (comment)? I could then add that string to a field in the CSV named file_tn or similar.

[seth-shaw-unlv]

@mjordan, I'm planning on generating the thumbnail before it gets to the ingester because it will be useful to have while users are doing data entry in the spreadsheet.

[mjordan]

OK, thanks, let me know if you want me to do anything.

[seth-shaw-unlv]

Ok. @davidbasswwu mentioned trying to wrap the workbench in an Electron App for us but I haven't seen any updates on that since late June. You want to give it a go? Once that is in we can try to wire together the parts we have so far.

Other work tasks have my attention at the moment, so I probably won't get back to the spreadsheet editor for another week.

[mjordan]

@seth-shaw-unlv let me give it a try, at least to the point where I can run workbench and show some output.

[davidbasswwu]

Sorry for the lack of updates; I’ve had to focus on a different project and am taking this week off. As I started working on it, I realized that it’s probably going to be a lot easier and less complex if it’s all written in NodeJS (and not use Python), but I’ll know more once I have time to dive back into it. - David

[mjordan]

@davidbasswwu no sweat. I'm going to hack around a bit over the next few days and will report back here. We can regroup when you're back, if you have time.

[mjordan]

I've got a very basic proof of concept Electron app that executes the Python workbench script and outputs the results. User fires up the app:

Then clicks on the "Run Islandora Workbench" button, and when workbench completes executing, they see this:

The Electron app hard codes the path and arguments to the script and uses the node python-shell module to execute it:

 const ipc = electron.ipcMain
  ipc.on('asynchronous-message', function (event, arg) {
    let {PythonShell} = require('python-shell');
    let options = {
      pythonOptions: ['-u'],
      args: ['--config', '../../workbench/test.yml']
    }
    PythonShell.run('../../workbench/workbench', options, function (err, results) {
      if (err) throw err;
      output = results.valueOf().join('<br />')
      event.sender.send('asynchronous-reply', output)
    });
  })

A pretty basic proof of concept but it works! Code is at https://github.com/mjordan/islandora_workbench_desktop.

[seth-shaw-unlv]

Sweet! Hopefully I can get back to this by the end of the week and start integrating the spreadsheet editing.

[mjordan]

I've changed this a bit so that the output from workbench appears in realtime and not all at the end. See details over at the repo.

I'll start working on config file editor functionality. Shouldn't be that difficult.

[mjordan]

Got this to the point where user selects the path to workbench and the configuration file for the task via standard file open dialogs. I created a (disabled) menu option for "Edit CSV file" as a placeholder. Maybe not the best UI design but I thought I'd put it in there.