How I made the ELCv4 Author Metadata Form

Simple tasks are explained in this note, in the next section. More complex tasks are linked to other notes expanding on them.

This was the FIRST version. For updates, go here (Weblink).

G-Sheets:

• Create a filter tab on our entries/reviews document (“ELC4 Submission Reviews [23 June]”) with only the accepted entries.
  • We had a main tab (“ReviewData”) that included all entry information, all review information, and, most importantly a column where we note whether the work was REJECTED or ACCEPTED (column AO).
  • Create a new tab (I named it “LyleTab” so it wouldn’t be likely to be messed with by other editors).
  • Copy and paste the header row from ReviewData to LyleTab.
  • In the first data cell (A2):

=FILTER(ReviewData!A1:AO450, ReviewData!AO1:AO450=“ACCEPT”)

    - AO is the reject/accept tab, so this filter tells GS to look at the ReviewData tab and only bring in rows that have ACCEPT in the AO column.
- It’s important that this is a filter, instead of a sort/copy/paste duplicate, because it means that if the data is modified on the ReviewData tab (essentially, the “master” data), it will automatically update on the LyleTab tab, as well as the Metadata sheets.

• Create a new GS document (“Metadata”) for data exchange with the form. Tabs:
  • EntryData
    • Sheet that imports the current data that we have on all accepts (which is what the LyleTab creates).
    • In the first cell (A1):

=IMPORTRANGE(“URL”,“LyleTab!A1:A”)

        - Where “URL” is the url for LyleTab.
        - This imports the Index numbers for the entries (important for the passcode).
    - Column B will be for the passcode: I labeled it (B1) “Code”.
    - In C1:

=IMPORTRANGE(“URL”,"LyleTab!H1:M”)

        - Where “URL” is the url for LyleTab.
        - This imports all other entry data needed (columns H-M).
- DATA - set up according to [Recording Game play data in Twine](evernote:///view/1406573/s12/8e0eec71-dcf6-42b5-a986-6d18caf27127/8e0eec71-dcf6-42b5-a986-6d18caf27127/) ([Weblink](https://www.evernote.com/l/AAyODuxx3PZCtamGbRjK8nEnGluPMJPpfAI))
    - NOTE: make sure to create a Timestamp column!!! This is necessary for sorting the most recent submissions tot he UpdatedMetaData tab.
- UpdatedMetaData
    - *Reason for this sheet: I wanted to enable the authors to return to the form and check/update their data if necessary. Doing this, however, would mean that there will be multiple rows where the passcodes match. On subsequent updates, therefore, the Twine form was pulling in ALL data from ALL rows, resulting in multiples. So the title would show as “TitleTitleTitle”. I attempted filtering through the GS query I used to pull in the data to create variables, but I couldn’t find any G-query that would do it and still work with Sheetrock. So this is the solution.*
    - Copy and paste the header row from DATA.
    - In A2:

=sortn(sort(DATA!A2:AF842,32,false),9^9,2,2,true)

        - I used this tutorial to accomplish this: [Extract the Earliest or Latest Record in Each Category Based on Timestamp in Google Sheets](https://infoinspired.com/google-docs/spreadsheet/extract-the-earliest-or-latest-record-in-each-category/). Her explanation, in brief:
        - ![](:/a9770e95341f4c93a5e0a71119286a18)
        - In my sheet, DATA, 32 is the column number of my Timestamp. The range is effectively the whole sheet (except the index numbers…because they are not information the author uses or knows about, they aren’t used in Twine, and so don’t come back in the data).
- Note: I put a “Protect Sheet - Only I can edit” lock on all of these sheets, and once I was sure they were working properly, I hid all the data columns. In order for GS and Twine to talk to each other, the Gsheets have to be public. Twine doesn’t hide its code, so ostensibly someone could find the URLs in the Twine file, and have a look at all the entry data, and even mess with the spreadsheets. I don’t want that. Someone could *still* do it (export the data to a new sheet, and they can see the hidden columns), but they’d have to be both savvy and dedicated, so I think these are reasonable measures to take for the convenience of using GS+Twine for a short time period.

• Create unique passcodes for each accepted entry.
  • Reason for this: We originally thought we could just give authors the index numbers to use as “passcodes”, but then I had a look at it and realised we had two problems:
    • 1) The index numbers are integers going from 1 to 400 or so, rather than text strings such as 001. To check passcodes, I always use a Javascript code that checks whether the html table Sheetrock returns from G-sheets for that column INCLUDES the passcode. That means any single-digit number will ALWAYS return true, and thus is not a unique passcode.
    • 2) It would be pretty easy for someone to figure this out, and start entering numbers starting at 1 and increasing, to see all of the works that we have accepted. Since that information isn*’*t public yet, and we expressly wanted a passcode system to prevent it, this would defeat that purpose.
  • In the Metadata-EntryData tab, B2 (the first cell in the “Code” column):

=IF(ISBLANK(A2),(LEFT(CONCATENATE(A2, (REGEXREPLACE(C2,“\s”,))),8)))

    - Explaining from the middle expression and going out:
        - (REGEXREPLACE(C2,"\s”,)): Takes the contents of cell C2 (the titles column) and removes all spaces.
        - (A2, ... ): Combines the contents of A2 (Index numbers) and combines them with the contents of C2 with the spaces removed.
            - This is effectively a nice, space-less string.
        - (LEFT(CONCATENATE ... ,8): Concatenates (shortens) the space-less string to the first 8 items from the left.
        - IF(ISBLANK(A2),, ... ): Checks if A2 is blank. If it is, it leaves B2 blank (that’s the “nothing” between the commas). If it is NOT blank, it fills in B2 with the max-8 letter string from above.
- This creates a unique passcode for each entry based on its index number plus title, that we can email to the authors when we request their data, and that is highly unlikely to grant accidental access.

• Import data from Metadata-UpdatedMetaData tab to SubmissionReviews-MetaData, so that all our data is accessible from one document and is kept automatically up to date.
  • Similar to creating the EntryData tab above. Create a new tab in “ELC4 Submission Reviews [23 June]” called “MetaData”.
  • In A1:

=IMPORTRANGE(“URL”, "UpdatedMetaData!A1:AF”)

    - Where URL is the url of the UpdatedMetaData tab in the Metadata document, and the range is the whole sheet.

Twine:

• Log-in: checks the passcode against the Metadata EntryData tab AND the UpdatedMetaData tab.
  • This is the same as Log-in Page that Pulls codenames IDs from Google Sheet (Weblink), with a couple of updates:
    • In Twine’s Javascript, I created two instances of Sheetrock: One to check the passcode against column B in the EntryData tab (var “response”), and the other to check to see if it is ALSO present in column B of the UpdatedMetaData tab (var “$response”), and the other to check to see if it is ALSO present in column B of the UpdatedMetaData tab (var “$revCk”). If the latter, it directs the author to Twine-UpdatedInfo instead of Twine-CurrentInfo. This is because authors can submit updates multiple times, and I wanted to be showing them the most recent version.
      • In the log-in page, I then created conditionals that send the authors to the correct passage.
    • In the log-in page, instead of using Twine’s *textbox *function for passcode entry as I normally do, I used an html input box. This is because I was doing something with the GS data I have never done before: not only did I want to compare the codes, I wanted to pull in data from GS as variables. THIS WAS HARD, because I had to single out ONLY the data matching the row of the passcode the author enters. I was unsuccessful at the task in many iterations because the javascript doesn’t seem to want to read the Twine variables, but I COULD get it to read the id of this textbox (more on that in the CurrentInfo passage below).
      • This also means that in the “submit” button, you must use the JS “getElementById” to create the Twine variable.
    • I also included my now-standard “wait for Google to respond” repeat sequence because I wound up on my phone as a hotspot at 2am when the internet went down, and started getting “$response is undefined” errors because of slow connections. This doesn’t normally happen with the codename stuff, but apparently on slownet it does!

Please enter the code:

<<button “Submit”>> <<repeat .5s>> <<if $response != undefined && $revCk != undefined>>         <<set $code = document.getElementById(“code”).value>>         <<if $code == “”>>         <<replace “#textbox-reply”>>
            Please enter your code.
            <>         <<elseif revCk.includes($revCk.includes($code)>>         <<goto [[updatedInfo]]>>         <<elseif response.includes($response.includes($code)>>         <<goto [[currentInfo]]>>         <>             <<replace “#textbox-reply”>>
                Incorrect. Please try again.
            <>         <> <> <> <>     <>

• CurrentInfo: shows the author the current information we have on their entry (from the Metadata EntryData tab).
  • Create all the variables needed from the Google Sheet: Creating Twine variables from Google Sheets cells (Weblink).
  • Each item of data has a checkbox for the author to check what they want to correct.

<<checkbox “$title” $title ‘’>> Title: $title

<<checkbox “$urlx” $urlx ‘’>> Link to work: $urlx (If you have updated the work since you submitted, please copy and paste an updated URL - a weblink if it is online, or a dropbox/G-Drive/WeTransfer link if it is not.)

    - Putting the <label> tags around the whole thing ensures a click anywhere on the line/text will check the box.
    - Checkbox: <<checkbox “$variable” UNCHECKED CHECKED>>
        - Variable is declared. If the box is unchecked, it will set the variable to be what it already was. If the box is checked, the variable is reset to nothing (‘’).
    - Span class: defined for “instructions” in the CSS.
- The passage’s submit button checks for variables that equal ‘’ to send the user to the Corrections passage, and for existing items that don’t meet what we want (e.g., length of the work’s description) even if they didn’t check the box.

<<set $descArr = $desc.split(" ")>> //Splits text data into an array where there are spaces, so that a word count can be offered.

… <<button “Continue”>> <<if $descOldArr.length > 250 or $descOldArr.length < 200>> <<set $desc = ‘’>> <>

<<if $title == ‘’ or $urlx == ‘’ or $date == ‘’ or $creator == ‘’ or $desc == ‘’ or $vid == ‘’ or $imgx == ‘’ or $lang == ‘’ or $loc == ‘’ or $plat == ‘’ or $prog == ‘’ or $nat == ‘’>>

<<goto [[corrections]]>>     <>     <<goto [[newData]]>>     <>     <>

• UpdatedInfo: IF the author updates their information multiple times, this shows the author the current information we have on their entry (from the Metadata UpdatedMetaData tab).
  • Set up the same as CurrentInfo, but reading from the UpdatedMetaData tab. Leads to CheckInfo instead of Corrections.
• Corrections: gives editable fields for the data elements marked for correction.
  • Lists all of the information so they can see it, but only offers edits for the areas they marked.
    • Checks to see if the variable is empty (‘’). If it is, gives a textbox that, once filled, will define the variable. If not, it just shows the current value of the variable.

Title: <<if title is ''>><<textbox "$title is ''>><<textbox "$title" ‘’>><>$title<>

- For some elements, such as the author description, I needed to give both an option for the author to correct if they marked it, but also to check it against what we wanted in terms of word count.

Author note: <<if $desc is ‘’>>

<<textarea “$desc” $desc>> //Pre-fills the textarea with the current variable for ease of editing longer text.

<<button "Word Count”>> // Gives the user the ability to check their word count. Could also do with a repeat sequence.

<<set $descArr = desc.split(" ")>> // <<replace "#wc">>KaTeX parse error: Expected 'EOF', got '#' at position 33: … // <<replace "#̲wc">>descArr.length<><>

Current word count: $descArr.length

We are aiming to provide author’s notes of 200-250 words. Please revise your description of the work so that it fits within this remit.

<>$desc<>

• NewData: collects the new metadata about the work that we have identified as desirable, which wasn’t collected in the entry phase.
• Demographics: collects new demographic data for EDI purposes (also allows a default “decline”).
• CheckInfo: shows the author all of the updated information from this session. Like CurrentInfo, they can check items they want to correct.
  • UpdatedInfo, unlike CurrentInfo, leads straight to this passage, bypassing Corrections, NewData, and Demographics, since we already have those from their previous session.
• Corrections2: gives editable fields for the data elements marked for correction. Leads back to CheckInfo in a correction cycle until the author is satisfied.
• End: requires the author to accept the ELO Code of Conduct and that they have properly attributed everyone whose work appears in theirs. Submits the data back to the google sheet.
  • Write data from Twine form into the Metadata DATA tab - set up according to Recording Game play data in Twine (Weblink).
• Thanks: gives a final message to confirm submission.