Scrabble Success with Google Sheets and Maprun Thanks Peter and Michael

[WithaMap]

I thought Orienteering by forming words based on Scrabble would be fun.  I had no idea what I was getting into and with no programming or spreadsheet skills I knew I could not do this without help.  I really wanted to see this happen.     I  posted on here a couple of weeks ago asking if there was a way to do this. Peter quickly answered YES and Michael took it from there.     The 2 main problems with this as it would have to be done with Score-O/Scatter format and the format does not really allow multiple punches with the same QR/NFC/GPS or even e-punches.   The other problem was that results come out in numbers.  So how can that all be translated into having your results come out in words complete with time and spelling penalties?    Michael Raz used Google Sheets to do the translation and kept at this for 2 weeks with me doing various tests and eventually he came up with a version that works!  It basically works the same as a Score-O but instead of control numbers at the location of the control, you would have letters (each letter contains a value based on Scrabble).   At the control location would be the letter to confirm you are at the right control.   When downloading or finishing, the Google Spreadsheet does the conversions and done instantly.   Total net points wins the race. (After spelling and time limit penalties subtracted)    Michael can go into more details on how it works.    Michael Raz deserves all the credit for this.  To me, it opens up a bunch of new possibilities and I can't wait to try this out with some willing folks!  

[MichaelRaz]

For those interested in how GoogleSheets was used:



The MapRun API for results (JSON) is described here https://maprunners.weebly.com/access-to-results.html  and an example GoogleSheets is discussed here exporting_maprun_results_into_a_spreadsheet_v1.pdf (weebly.com) .  I had previously developed and shared an Excel spreadsheet with a very extensive (maybe overly so) ability to import and process the JSON results.  The downside to this is that it only works on a windows platform while  Google Sheets are not tied to a platform.  When Bruce posted about wanting to do a Scrabble type event, the first and major issue was that duplicate punches are sorted to the end as ‘Extra’ punches so I offered to do up a quick Excel sheet that would process these and “move” them to the proper order based on Punch Time. I then decided to give Google Sheets a try.  In general, there are two main ways to modify data in a spreadsheet – through built in functions within the cells and scripting/coding.  Google Sheets uses a form of JavaScript (which I have never used before) so this looked like fun to try.  I’ll state clearly that the code could most certainly be improved, in part as I was learning, and as this was an iterative process while Bruce evolved the scoring rules. The one downside to Google Sheets is that the code runs in the Google Cloud so it is not very fast but I’m sure some of that is the way I coded things.

The two Google sheets involved are
Scrabble Demo: https://docs.google.com/spreadsheets/d/1So64RHFk9piFeL8toedaeK0g3949xpTzMIioj4KQumE/edit?usp=sharing The main sheet that imports the results and processed the Scrabble.

Scrabble Results Demo:  https://docs.google.com/spreadsheets/d/1ajKXgqSghPmEaBDsPO_oLHPD8MtyRB-roCowz-s86Qg/edit?usp=sharing A sheet that presents the final results to the Public (this is kept separate as Google Sheets does not allow for sharing rules per tab so, to keep the main sheet safe, this Results sheet is linked to the main.

Either/both of these demo versions are available to view through the links provided.  If you want to play with either, make a copy for yourself (but you will need to go through the annoying steps to authorize the scripts – details below)

The main Scrabble sheet has four main tabs (I kept a couple of the old ones tagged on the end):

Scrabble:  Main Tab that initially copies over the results from the MapRun tab, does the initial process of scores based on the control reordered and time penalties, then allows manual validation and modifications to the Words found including putting incorrect spelling words in brackets (which will then be ignored in re-scoring), and finally marking the line as Final (which locks it and allows it to show up on the Scrabble Results Demo sheet. Included as well is the Sort by points then time function.  There are others things that go on here, for example if you run the Import MapRun Results function again, it will not overwrite current rows with identical ID’s. Note that this is importing not from the MapRun server but the MapRun tab in this worksheet.

MapRun:  This tab imports and decodes the results for the specified event name from the actual MapRun server.

Final Scrabble: With the assumption that the working Scrabble Tab is not what you plan to show for the final results, this tab is customized to show the specific columns you want and any specific view changes (note that here the first and last name are combined into one cell).  This is also the tab the standalone Scrabble Results worksheet imports.

Letters: This tab contains the lookup table linking control codes to letters to points.  In addition, a lot of other variables are set here mostly to enable easier changes to the layouts without impacting any of the code.  This is not tidy but makes modifications easier.

General Workflow simplified:
Put the Event Name in MapRun!B2.  This page can be refreshed at any time for new results.  Couple of examples you can use are 'Scrabbled Test Oct24' and 'ScrabbleTest3'

In the Scrabble tab, click on Import MapRun Results which will copy over any new results from the MapRun tab.
Click Process Results which will do the initial processing of results. It will overwrite any changes you have made so be careful.  It will NOT overwrite any changes if the line has been marked FINAL.
Review entries, validate spelling, put brackets around misspelled words then run Update Points Only.  This will update the points (unless the line is marked as FINAL). Mark as Final when complete.
When all entries have been validated and marked FINAL, you can sort the results.
That’s it.

The Final Scrabble tab is updated automatically as you make changes on the Scrabble Tab so unless you want to modify the contents (columns) this should not need any work.

Scrabble Results Worksheet:  As noted, Google sheets does not allow a specific Tab to be shared so if the main Scrabble worksheet was shared to show final results, then all the tabs are visible. To get around this, the Results worksheet is shared with a view only link (and can be provided in the event documentation. This worksheet can have as many event tabs as you wish and the GOOGLE SHEET Event URL (not the MapRun event name) is pasted in the noted cell.  There are two functions being used.  A2 has =IMPORTRANGE(B1 ,"FinalScrabble!A1:G6") which gets all the header and notes from the Final Scrabble Tab in the selected sheet (so this include the header row).  A8 has =FILTER(IMPORTRANGE(B1,"FinalScrabble!A7:G200"), INDEX(IMPORTRANGE(B1,"FinalScrabble!A7:H200"),0,8)=true) which gets the data but filters for only those rows marked as Final.  So once this is set up and published, any changes from the main Scrabble worksheet will flow through automatically in near real time.

I do have a “short” video to run through the workings for that that are more visual https://drive.google.com/file/d/1V6QoD5CVOLIQ2m2oBCXolh6kKOSdLqBq/view?usp=sharing 

In conclusion (finally), this is a fairly niche application but a good demonstration of how the GoogleSheets mechanism can be used to handle events with non standard scoring.  I would suspect many such situations could be handled simply in the spreadsheet with little or no coding (other than what is already there for the JSON import).  Add to this the ability to provide a link to a separate Results page when you set up an event, this can be very handy.

Annoying Security stuff:
When you make a copy and try to run any code (for these sheets and the original provided by MapRun) you will be prompted to allow access.  It is meant to be not so obvious if you want to say YES. Starts with some message like “do you want to authorize…”  OK.  Select Account – select your google account.  Select Advanced.  Select the script package (this is still names like the original “Go To POC….”.  Scroll down to the bottom and Click “Allow”.

[WithaMap]

For anyone interested in setting this type of event up on Maprun, this is pretty simple now that Michael has done all the Google Sheets work.   I am planning on using a Maze with QR Codes and Scrabble Tile images that will go on the Map and then Magnets with the same scrabble tile (with QR Code) in the correct location. <br> This does not have to include a Maze for setup.   It can be setup anywhere.  Even larger spaces and possibly part of an event,  indoors or out.  I also have 15 other Maze Puzzles but those are point to point and much simpler to build then Scrabble. <br>  Course design was in Purple Pen but as far as the participants go they will not see any circles or numbers. No Control descriptions needed.<br>   In Purple Pen,  I first design the course and then make a duplicate course for the printed version where I delete all the circles and numbers.  You could get away without the duplicate but found it easier to remember where all the images go in Purple Pen.   The images on the printed maze are just put in with the Purple Pen's "Add special item" feature.   I usually create the image as a transparent png file and then use Michaels QR Code generator for all the QR Codes.  The Spaces and Double up have added point value but you do not get 20 points for doubling up on "Z" or anything else for that matter.   Feel free to message me if you need more explanation.    
 

[James-O]

This is really interesting to see, I have run a number of scrabble stages in the UK doing something very similar.

Using a score course and the results api I wrote a short php script to publish and interpret the results.

An example output is here: http://homelessdiamonds.org.uk/ukc/scrabble/

The formatting is a bit shonky but it gives instant results and gives competitors an explanation of their score as it developed.

If anyone wants to take a look and the code and enhance it I'm happy to share. Also interested in what other ideas for formats people have for courses?

[MichaelRaz]

Hi James,

I would like to see the code if you want to share.  More than anything, I'm still very new at PHP so always interested in seeing how others do things (I'm not sure I would want to share the code on my apps page maprun.raz-family.ca - it works but there are some interesting approaches.  I think I would have done the scrabble for withamap in php rather than google sheets but for two reasons - firstly, in sheets, I could hand it over to withamap and he can then make changes to things like scoring himself so I'm not hosting anything.  Secondly, on my hosting site I have primarily a web hosting plan only and it blocks the JASON port that is used for the MapRun results.  I had written an app to convert results into CSV and then I spent a while trying to figure out why it worked just fine from my home but failed when I published it to my site.  I think Peter is thinking about changing this as it could end up impacting people behind organizational firewalls etc, but I doubt it is high on his priority list.

Thanks,
Michael

[James-O]

Absolutely, I'll stick it on a repo and share a link when I'm back at my mac.

It comes with the usual health warnings around amateur developers' code, I certainly would not want you to copy what I have as "good practice", but it works.

I've actually made a bunch of different orienteering games with varying degrees of complexity, I'll try and share a few to give you some ideas...

One of my favourite features is compiling two results together to act as a single competitor. In the scrabble example that means pairs working together to spell words, going to different checkpoints, but ensuring they get the right order!

[James-O]

Michael - emailed you direct with a copy of the php

[WithaMap]

I did try this out for a small selection of Guinea pigs and Michael's Google Sheets method was a success.
I did not want to set up the Maze so made a micro map of some old tennis courts that had about 28 points that I could use.  The response was overwhelming.   I realized that after I set it up, the various combinations are endless.   I simply set it up and then tell people the rules.   The first one I setup was scrabble in 3 minutes.   That was too short so then I told people that we are now going to have 6 minutes to spell US Cities.   Since some people had time to look up cities on their phones while waiting, I decided to never let any competitors know the theme before the start.  For the last one, I told them that they need to spell out other competitors first names.   If they did not know their names, then they would have to ask while running by them.   This would serve as a perfect icebreaker for a group.    Turns out that many people in the sample group knew each other but people still loved the concept.

I think the next event, I will increase the time and come up with some more complex puzzles such as a spelling bee.    
I really don't know if the Php version is better but right now, it seems like this works exactly as I imagined and is super simple to change around.