Improving user-friendliness and extensibility about policies-database

[jpolka]

Spitballing here: as far as I know Wordpress keeps a nice revision history, has an editor that will be comfortable/familiar to many users (even more so if the dashboard is simplified), and permits user roles to be customized. How terrible of an idea would it be to use custom fields to make a custom template for policy listings? Evidently this allows instructions to be offered on the edit page. Posts could be created via import from a csv....

[dhimmel]

@cameronblandford how far is the frontend away from a web-interface that displays the actual policy data? I think contributions will increase when the effect of the contribution is more visible (i.e. an actual webpage gets updated). Also hopefully a working website will bring in more viewers and hence potential contributors, as well as providing more convenient direct links to edit YAML policies.

A user-friendly form-like interface allowing users to view and change existing fields.

It seems like it should definitely be possible to convert between the YAML files and a WYSIWIG-style editor. Perhaps there's even a way for a bot to commit changes upon a use clicking save and opening a PR for us to review. The big question here is whether it's worth our limited development time. How much of the current lack of contributions is due to the difficulty of using GitHub versus the difficulty of learning how to contribute to a new thing. However, we make this, there will be some sort of learning curve.

We should make sure potential contributors know that they can open issues if they get stuck. For example, they could say "I want to add this URL to this journal", and we could make the pull request on their behalf. Heck they could even tweet that.

How terrible of an idea would it be to use custom fields to make a custom template for policy listings?

I don't know enough about wordpress to know whether it'd be feasible or not and what obstacles you may encounter with this approach. My two biggest concerns are that it would take considerable time to implement and would lead to less structured / clean data and contribution history. And after making those sacrifices, contributions may not increase. A good place to start if investigating other designs would be to find some existing projects that use a similar setup.

Ideally a way for the less-technical among us to add new fields to all records, and create new child policies (and specify which journals belong to them)

These are difficult technical problems... we have a workable solution for adding new fields and have not implemented a solution to adding new policies. I don't think the barriers to adding new fields are too high. New fields should be added only after considerable peer review and consensus among the maintainers. Adding new fields is big enough of a decision that it's OKAY that it requires a maintainer to edit the YAML schema and rebuild the existing YAML files. Without extreme care, you will start to get duplicate fields... really only a few people know the structure well enough to assess whether a new field should be added. We encourage anyone to suggest changes to the schema, but these changes should all be discussed and carefully considered.

I will continue to think about how we want to enable custom policies. We can discuss this in a separate issue.

[jpolka]

Thanks @dhimmel - you make very good points about the benefits to high barriers to changing the underlying structure.

I've been playing around with scripts in Google Apps...

This sheet is connected to a form. When the form is submitted, it triggers a script that calls the Google Form API to generate a prefilled link for the response that has just been submitted to the form. (We can of course create these links for data that we enter directly into the sheet as well). The script also copies the contents of the log (minus OLD versions of the entries, by "Name") to the tab called "Latest Version."

We could get notified of new form submissions. If they look bad, we can manually delete the entry (or change the millennium on the time stamp), and copy the old row back into "Latest Version."

We can also create a very simple searchable/sortable table in Wordpress based off of "Latest Version," choosing which columns to display. This seems to take a few more minutes to update. You can see a very rough version of that by scrolling down here. I haven't yet played around with the urls and formatting and stuff to make the table prettier. And it could display some actual info in the form too.

Of course, I think @cameronblandford 's front end could probably do this better!

We would be collecting emails/contributor info for every edit. If there were some kind of dispute/question as to where a field came from, people would be able to find out where it originated by checking the full response log. We could probably also display a list of contributors without too much effort.

What do you think about this general approach?

[dhimmel]

What do you think about this general approach?

I think one question is whether the method can ensure high quality standardized information. It seems like the form can provide standardized information, i.e. you can provide predefined options and more complex dependencies. The lack of review before changes are deployed would be a worrying factor to me.

I'm not against alternative designs, but I don't have the time to contribute towards them... I am happy to continue maintaining and developing the current design. I won't be offended or mad if you choose to switch, so I'd suggest doing whatever you think is best. Perhaps prototype the form alternative a bit more.

There is also the question of how the forms versus YML files will hook into the frontend.

[dhimmel]

@jpolka the draft form looks nice. Once it's no longer a DRAFT, we can update the README of this repository to mention that it's not actively accepting policy updates and that interested contributors should use the Google form.

[jpolka]

2018-06-26 update: added 3rd sheet (url and script below updated)

I just started migrating existing policy records into the Sheets/Forms database. You can edit and add records here! This should autoupdate, but you might need to refresh your browser window.

Note that there are fields to add a "Policy ID" and a "Parent Policy" that appear on a hidden form page. These are currently not used as @tonyR-H evaluates what ontology to use moving forward. Here is the current version of the script that is triggered on form submit:

/**
  Use Form API to generate pre-filled form URLs
  Function to build URLs from https://stackoverflow.com/a/26395487/1677912 adapted here with following modifications:
  - Added functionality for GRID and CHECKBOXGRID item types
  - Sort sheet by timestamp so most recent is at the top
  - Copy de-duplicated entries (based on unique identifier) to another sheet
  - Remove excess information and format this sheet nicely for being published to the web
 
  This function assumes the following things:
  - The unique identifier for an entry is called "Journals covered by this policy"
  - The deduplicated values go into a sheet called "Latest version"
  
  Usage
  - If triggered on form submit, switch to "Only build URL for first row."
  - If rebuilding all URLs, switch to "Skip headers, then build URLs for each row in Sheet1."
 */
function transposeBuildUrls() {
  
  //Reverse the spreadsheet by timestamps so that latest addition is on top - recorded from macro
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A:A').activate();
  spreadsheet.getActiveSheet().sort(1, false);
  
  //Clear "Latest version"
  SpreadsheetApp.getActive().getSheetByName("Latest version").clearContents();
  
  // Use Form API to generate pre-filled form URLs; code from https://stackoverflow.com/a/26395487/1677912
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Form Responses 1");
  var data = ss.getDataRange().getValues();  // Data for pre-fill
  var headers = data[0];                     // Sheet headers == form titles (questions)

  var formUrl = ss.getFormUrl();             // Use form attached to sheet
  var form = FormApp.openByUrl(formUrl);
  var items = form.getItems();
  var urlCol = headers.indexOf("Prefilled URL");   // If there is a column labeled this way, we'll update it
  Logger.log(form.getItems())

  // USE TO REBUILD ALL URLS: Skip headers, then build URLs for each row in Sheet1.
//  for (var row = 1; row < data.length; row++ ) {
  
  // ONLY BUILD LATEST URL: Only build URL for first row.
    for (var row = 1; row < 2; row++ ) {
    Logger.log("Generating pre-filled URL from spreadsheet for row="+row);
    // build a response from spreadsheet info.
    var response = form.createResponse();
    for (var i=0; i<items.length; i++) {
      var ques = items[i].getTitle();           // Get text of question for item
      var quesCol = headers.indexOf(ques);      // Get col index that contains this question
      //OLD: var resp = ques ? data[row][quesCol] : "";
      var resp = ques ? data[row][quesCol] : "";
      var type = items[i].getType().toString();
      Logger.log("Question='"+ques+"', resp='"+resp+"' type:"+type);
      // Need to treat every type of answer as its specific type.
      switch (items[i].getType()) {
        case FormApp.ItemType.TEXT:
          var item = items[i].asTextItem();
          break;
        case FormApp.ItemType.PARAGRAPH_TEXT: 
          item = items[i].asParagraphTextItem();
          break;
        case FormApp.ItemType.LIST:
          item = items[i].asListItem();
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item = items[i].asMultipleChoiceItem();
          break;
        case FormApp.ItemType.CHECKBOX:
          item = items[i].asCheckboxItem();
          // In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
          // In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
          if (typeof resp !== 'string')
          resp = resp.join(',');      // Convert array to CSV
          resp = resp.split(/ *, */);   // Convert CSV to array
          break;
        case FormApp.ItemType.CHECKBOX_GRID:
          item = items[i].asCheckboxGridItem();
          Logger.log("This CheckBoxGrid is " + item.getRows().length + " rows long.");
          // Grid will need to take as resp an array as long as # of rows. Loop through the rows and get resp for each
          var itemrows = item.getRows();
          var gridresp = [];
          for (var j=0; j < item.getRows().length; j++) {
            ques = items[i].getTitle()+" ["+ itemrows[j]+"]"; //Title of the question includes both Item name and Row name.
            quesCol = headers.indexOf(String(ques));
            resp = ques ? data[row][quesCol] : null;
            Logger.log("Resp in row " + j+ " is " + resp + " and resp.indexOf(',') is " + resp.indexOf(','));

            if (resp=="") { //If resp is blank, set it to EMPTY ARRAY (not null as for grid), and write this to the gridresp array as is.
              resp=[]
              gridresp.push(resp);
            }
            else if (resp.indexOf(',')) {
              // Convert comma-separated string into array
              resp = resp.split(/ *, */);   // Convert CSV to array
              gridresp.push(resp);
            }
            else gridresp.push(String(resp)); //If resp is not blank AND doesn't contain a comma, convert to string, then write to gridresp array
          }
          resp = gridresp;
          Logger.log("Here is the response for this question: " +resp);
          break;  
        case FormApp.ItemType.GRID:
          item = items[i].asGridItem();
          Logger.log("This Grid is " + item.getRows().length + " rows long.");
          // Grid will need to take as resp an array as long as # of rows. Loop through the rows and get resp for each
          var itemrows = item.getRows();
          var gridresp = [];
          for (var j=0; j < item.getRows().length; j++) {
            ques = items[i].getTitle()+" ["+ itemrows[j]+"]"; //Title of the question includes both Item name and Row name.
            quesCol = headers.indexOf(String(ques));
            resp = ques ? data[row][quesCol] : null;
            if (resp=="") { //If resp is blank, set it to null, and write this to the gridresp array as is.
              resp=null
              gridresp.push(resp);
            }
            else gridresp.push(String(resp)); //If resp is not blank, convert to string, then write to gridresp array
          }
          resp = gridresp;
          Logger.log(resp);
          break;

        case FormApp.ItemType.DATE:
          item = items[i].asDateItem();
          resp = new Date( resp );
          resp.setDate(resp.getDate()+1);
          break;
        case FormApp.ItemType.DATETIME:
          item = items[i].asDateTimeItem();
          resp = new Date( resp );
          break;
        default:
          item = null;  // Not handling DURATION, IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER, TIME
          break;
      }
      // Add this answer to our pre-filled URL, if the item is not set to null (see types not handled) and response is not ''
      if (item != null && resp != '') {
        Logger.log("item is " +item);
        Logger.log("is this question required? " + item.isRequired());
        var respItem = item.createResponse(resp);
        response.withItemResponse(respItem);
      }
      // else if we have any other type of response, we'll skip it
      else Logger.log("Skipping i="+i+", question="+ques+" type:"+type);
    }
    // Generate the pre-filled URL for this row
    var editResponseUrl = response.toPrefilledUrl();
    // If there is a "Prefilled URL" column, update it
    if (urlCol >= 0) {
      var urlRange = sheet.getRange(row+1,urlCol+1).setValue(editResponseUrl);
    }
  }
 
 //Copy lastest version to "Latest version" sheet - modified from https://developers.google.com/apps-script/articles/removing_duplicates
 //The column to be compared is "Policy ID"
  var sheet2 = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  var latestsheet = SpreadsheetApp.getActive().getSheetByName("Latest version");
  var displaysheet = SpreadsheetApp.getActive().getSheetByName("Display");
  var data2 = sheet2.getDataRange().getValues();
  var newData = new Array();
  for(k in data2){
    Logger.log("The value of k is: "+ k);
    var row = data2[k];
    var duplicate = false;
    for(j in newData){
      if(row[headers.indexOf("Journals covered by this policy")] == newData[j][headers.indexOf("Journals covered by this policy")]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
  latestsheet.getRange(1, 1, newData.length, newData[0].length)
      .setValues(newData);
  
  // Make link prettier in latestsheet
  var data2 = latestsheet.getDataRange().getValues();  // Data for pre-fill
  var headers2 = data[0];                     // Sheet headers == form titles (questions)
  var urlCol2 = headers2.indexOf("Prefilled URL");   // what is the column # in new sheet that contains "Prefilled URL"
  for (var n=2; n<=latestsheet.getLastRow(); n++) {
      var oururl = latestsheet.getRange(n, urlCol2+1).getValue();
    latestsheet.getRange(n, urlCol2+1).setValue("=HYPERLINK(\""+oururl+"\" , \"Click here to edit\")");
  }
  
  //Copy desireable columns to displaysheet
  var maxcol = latestsheet.getMaxColumns();
  for (var m=1; m<=maxcol; m++) {
      switch (latestsheet.getRange(1, m).getValue()) {
      case "Timestamp":
          latestsheet.getRange(1, m).setValue("Last update"); //rename the header
          latestsheet.getRange(1, m, 10000).copyTo(displaysheet.getRange(1,1));
          break;
      case "Journals covered by this policy":
          latestsheet.getRange(1, m, 10000).copyTo(displaysheet.getRange(1,2));
        break;
      case "Prefilled URL":
          latestsheet.getRange(1, m).setValue("Link to edit"); //rename the header
          latestsheet.getRange(1, m, 10000).copyTo(displaysheet.getRange(1,3));
        break;
      default: //delete all other columns
        //latestsheet.deleteColumn(m);
        //m--; // decrement m so as to check every column
        //maxcol--; // the # of columns has also decreased
        break;
    }
  } 
  //format displaysheet
  displaysheet.setColumnWidth(1, 120); //set the column with journal names wide.
  displaysheet.setColumnWidth(2, 300); //set the column with journal names wide.
  displaysheet.setColumnWidth(3, 120); //set the column with journal names wide.
  displaysheet.getRange('1:1').setFontWeight("bold"); //Make header row  bold
  displaysheet.getRange(1,4).setValue("=HYPERLINK(\"https://docs.google.com/forms/d/e/1FAIpQLSf2VsvytNSGrYLwKmsdN3SYAIYSHo71A1-RppjlyIuLcIKepw/viewform?usp=sf_link\" , \"Can't find a journal? Click to add a new record\")")
  displaysheet.setColumnWidth(4, 400);
}