Document usage of ReadSpreadsheet

[viroulep]

At the moment this is more a question for which the answer should likely be public: is there any basic example out there of a basic spreadsheet format and the result it provides in person's properties when imported?

I've been shared some private example usage of compscript, but the data in spreadsheets have been appropriately omitted, and now I'm left with a few questions on the format I should target (because importing data dynamically sounds like a great idea for Euros ;)).

Let's take for instance the following data (csv, but separated by ';' intentionally, because of how I think values are parsed in the code):

identifier:string:wca_id;identifier:integer:user_id;property:list:scramble-events;property:string:team-kind;ignore:Some nice thing
2008VIRO01;;3x3,2x2;teams;titi
2008PIAU01;;5x5,2x2;teams;toto
;61698;;teams;tutu
2018GODI01;;;data-entry;tata

Reading from the source code, I would guess this results in adding the properties "scramble-events" and "team-kind", respectively holding an array of string and a string, that I would be able to query using respectively ArrayProperty("scramble-events") and StringProperty("team-kind")?
The "Some nice thing" column would simply be ignored.

Could you please confirm using this format is enough to identify whom to attach the data to based on the wca_id/user_id fields? (and does it matter if both of them are filled?)

What happens if one of the columns' header doesn't have the ignore:/identifier:/property: prefix?

For the record I still intend to run a test competition with a simplified example of basically everything, that could serve as a publicly available showcase of compscript (and for which I could make the spreadsheet used public, hence "fixing" this "issue".

[viroulep]

I've played around a bit, and it turns out (quite logically) the identifiers need to exist in the person's data; I'll send a PR with a few changes related to authentication soon, but basically this seems to work as I wanted:

identifier:string:wcaId;identifier:number:wcaUserId;property:list:scramble-events;property:string:team-kind;ignore:Some nice thing
2008VIRO01;;3x3,2x2;teams;titi
2008PIAU01;;5x5,2x2;teams;toto
;61698;;teams;tutu
2018GODI01;;;data-entry;tata
2016LOPE37;;;orga;

Which can be used in a script like this (I've intentionally put the spreadsheet link as it is public):

ClearWCIF(true)

#https://docs.google.com/spreadsheets/d/1-TK-X69OjwYAnCzM_UfUCua1KAEQpU3zdJRdCAmz5Yw/edit?usp=sharing
ReadSpreadsheet("1-TK-X69OjwYAnCzM_UfUCua1KAEQpU3zdJRdCAmz5Yw")

Persons((StringProperty("team-kind") == "teams"))

ArrayProperty("scramble-events", 2008VIRO01)
Persons(In("3x3", ArrayProperty("scramble-events")))

Persons((StringProperty("team-kind") == "orga"))

[timreyn]

Hey Philippe,

You've got it! Thanks for putting up with the lack of documentation :) A few details:

• If there are multiple identifiers, it looks for which person matches the most. In our form we use 3 identifiers (name, email, WCA ID); these are all three that people should know. I didn't ask for WCA User ID since people don't use theirs regularly. So if I filled out "Timmm Reynolds / [email protected] / 2005REYN01", that should work; whereas if I put "Philippe Virouleau / [email protected] / 2005REYN01", that would fail because it would be a tie between 2 competitors.
• The output of that function is a list of rows that it failed to parse, so you can go back in and fix them.
• While testing on a dev export, this frequently failed because it doesn't have people's email addresses. So I made the error handling pretty resilient. But on the prod site, it fails a lot less often because it's using real email addresses -- that's another matching point :)
• In our form, we had two header rows -- one for the formulaic header, one that Google Forms added
  
• Columns without a header (or without one of the expected prefixes) are ignored.
• In our form we used the following headers:
  identifier:string:email
  identifier:string:name
  identifier:string:wcaId
  property:number:attending (on a scale of 1-5 are you attending)
  property:number:group-id (pick a group of people you'd like to be on a team with; the number goes in this column)
  property:number:percent-judge (note that the code is resilient to these not adding up to 100)
  property:number:percent-scrambler
  property:number:percent-runner
  property:list:puzzles-to-scramble
  property:string:staff-team-form (the human-readable version of "what kind of staff are you", e.g. "Core Staff (including Stage Leads)". There was a separate "staff-team" property derived from this, that made it less verbose)

Let me know if you have any more questions :)

[viroulep]

Thanks a whole lot for the answers and explanations!
It does explain some details of the code I didn't understand on the first read :)

I intend to do a proper competition example at the end of the month on a "small" competition to test out of few stuff for euros; I'll make sure to not include non public information and to share/document everything publicly and it should be more than enough for this issue :)