Getting Results Directly into Excel

[MichaelRaz]

Edit 22 September 2023

This original post was focussed on an Excel spreadsheet with code to use the older MapRun results method (html/csv) and import into a spreadsheet.  Although this result system is still in place, the newer method is using JSON.  I have produced a new Excel spreadsheet that uses the new results system.  Look for the MapRunJSON zip file in the Google Drive link here:

https://drive.google.com/drive/folders/1sMb8t4_nTuQoTo6TUNPt8g2J6dgGKY33?usp=drive_link

This spreadsheet is a bit more complicated as it allows you to modify fields while importing rather than after within the spreadsheet. Example - concatenate First and Last name.  It even allows you to predefine functions and therefore show the data already modified.  Alternatively, you can just turn off all of these and import the JSON data as is.  Instructions are in the zip file. Note that this will NOT work with Excel for MAC as the Mac Excel is missing some of the default built in libraries available in Windows.  I did work on overcoming this but it was difficult when I do not have a MAC.  Also note that Windows will initially make it difficult to use this due to the macros/code.  You need to do a couple of steps which will depend on your Windows version.

In many cases the "Official" method using Google Sheets might be easier to use unless you need some of the features in my tool.  I've edited this post as it is referenced from the MapRun web site.

Original:
Background:  Helping out with an adventure race in a few weeks that is going to use MapRunF.  I'm building an Excel application that integrates their registration/results process directly into the MapRunF results from the web.  I thought someone might find the part that scrapes the website for the results and puts them into excel handy.  There is no magic or special admin rights being used as it simply grabs the public Results web page (that you normally go through the console/events filter process to get to).

The spreadsheet is here:  No Longer available

Note that you do need to have macros enabled.  As this is an extract (slightly modified to be standalone) from my more complete solution, it is not all singing all dancing and not really optimized.  If anyone has troubles uploading the excel directly let me know and I can post the code.

Basically you need to paste the event name (a version of it - more later) into cell E1 and hit the refresh button.  This will build the full URL (which is shown on the worksheet as "Result", get the web page, pull out the headers and then populate the worksheet with the content.  In addition it adds the track URL to the last column plus embeds it into the column that contains the string "Track".  Note that all the fields are brought in as text so Excel doesn't do weird things when it thinks something is a time/date etc.
To keep it simple the Event Name is a cut and Paste from the normal results page, such as:

CombinedResults_McCarthyPark ShortAdv1 PXAC.csv
ScoreResults_Sheldon Shacklow PXAS ScoreV120.csv



but note that when pasted directly from the web site, the text is white so in excel it looks blank.  You can leave it and the first time you hit refresh it will reformat it to black, you can manually change it within excel or use paste value rather than just paste. irritating and could be handled automatically but not worth the effort.

Regular events and Score Events have different columns.  The spreadsheet will autofit based on the content but this does cause the Event cell E1 to jump from the left to the middle or back.
As a side note, the resulting full url can be used in other places to allow someone to jump directly to the results for the events so handy when posting on your own website.  Similarily, the track url less the last bit &pID=50wxyz will go straight to the RouteGadget Page for the event (the last bit is the individual ID).

If interested the app I've put together does the following:
 
1. Stores the registration information including team names, participants, class and their MapRunF name (which will not match the team name in most cases).
2. Grabs/Refreshes the MapRunF results as they become available
3. Matches the MapRunF names from the registration to the results server (highlights those that are not included in the registration - likely spelled wrong)
4. Parses out the MapRUnF Server results (breaks out the control points from the time penalties, removes extra and S1 F1 controls from the control list to get correct control counts and populates the Registration page with the latest results
5. Allows scorer to adjust scores if needed, finalize results etc
6. Calculates a sort field that includes points and time to allow a full ranking.
7. Ranks by overall and the individual race classifications (not the same as MapRunF classifications) and provides results for printing
8. bunch of other bits and pieces.

We'll see how well it works!