Steam Library Spreadsheet part 2: Getting more specific

This is a follow up to part 1 of my little mini-tutorial for Google Sheets combined with the why and how of my steam library spreadsheet.

The primary goal of the spreadsheet and filtering down the large data set of my Steam library is for a theoretical home made arcade cab device I will probably eventually finish.

The criteria I’m looking for includes games that have gamepad support, can be controlled only with the D-Pad (or “HAT”), don’t require dual analog sticks, can launch without any intermediary “launch game” screen (to make launching via frontend easier), something that actually makes a good arcade game and a few other details.

To put it another way: the game Assault Android Cactus seems like it would make an absolute perfect arcade game but requires dual analog sticks so it would not make my list.

Lets move on to the next step: now that we’ve filtered the data down to only “games” it’s time to filter those games down to only the ones that support a gamepad. This is supposed to be for an arcade cab after all.

Fortunately once the FILTER function has it’s required range of cells, in the form of the named range RawData, we can add as many conditions as needed after that. All that is needed is a comma and a condition specifying the value of “gp”, as seen in the new formula below.

=unique(filter(RawData,'Raw Data'!D2:D5="Game",'Raw Data'!C2:C5="gp"))

As you see, the filter function has two conditions, separated by a comma.

This will result in 3 rows of data out of the 4 rows I’m using for demonstration purposes (as one one of those games was a keyboard/mouse only or “kb” category).

So, switching back to my actual game spreadsheet with the 1000+ rows of raw data, we can use the above demonstration of the unique/filter functions to further shorten our list of target games one attribute at a time. I created a new sheets tab for each incremental narrowing down of the list.

The next criteria was my subjective and arbitrary score between 1 and 10 on the “likelihood of playing” which I determined by trying each game in my initial data set for a few minutes at a time.

=UNIQUE(filter(RawData,'Raw Data'!D2:D1999 ="Game",'Raw Data'!C2:C1999 ="gp",'Raw Data'!E2:E1999 > 6))

As you see a third condition has been added, again separated by a comma.

Lets look at the other criteria I used on my games spreadsheet:

• Will it run on low-end hardware? (the hypothetical base of my arcade device)
• Does the game launch right in or is there an intermediary screen requiring a mouse click or other acknowledgement that can’t be worked around?
• A final assessment on whether game would make a good arcade game, play-wise (Android Assault cactus would get a “yes” to this but the control scheme would filter it out)
• Will the game run on the low-res/small LCD I’m planning on using? The cab was going to use a relatively small 4:3 LCD screen, possibly running at only 1024×768. Which some developers don’t even test against.

Skipping ahead to this last tab, will all the possible criteria, here is what we have.

=UNIQUE(
   filter(
      RawData,
      'Raw Data'!D2:D1999 ="Game",
      'Raw Data'!C2:C1999 ="gp",
      'Raw Data'!E2:E1999 > 6,
      left('Raw Data'!I2:I1999)="y",
      NOT(isblank('Raw Data'!I2:I1999)),
      not(mid('Raw Data'!F2:F1999,9,3)="not"),
      NOT(isblank('Raw Data'!H2:H1999)),
      NOT(left('Raw Data'!H2:H1999)="n"),
      NOT(left('Raw Data'!H2:H1999, 2)="y:"),
      NOT(left('Raw Data'!H2:H1999)="("),
      left('Raw Data'!G2:G1999)="y"
   )
)

See this why I went one criteria at a time across a bunch of different Sheets tabs. Mess up one character or quote and the whole thing doesn’t show up and just says “error”.

There are a few function I didn’t cover in here. The line

NOT(isblank('Raw Data'!H2:H1999))

for instance refers to column H which is my final assessment of whether or not the game would make a good arcade game. Since not every game has been assessed I haven’t filled in every cell in the column yes. The line above therefore removes those blank lines: First it returns true/false and whether the cell is blank then the NOT inverts that to get a list of cells that are not blank.

Speaking of control schemes, you might notice from that really long “final” criteria list that there isn’t necessarily anything that directly tell you dual stick games like
Assault Android Cactus will be filtered out. This is because in that “controller support” column in addition to “gp” and “kbmouse” I also have “gp: dual” for gamepad support but requiring dual sticks. This criteria:

'Raw Data'!C2:C1999 ="gp"

Will return only the lines that contain those characters. After all as long as I’m manually assessing all these games I may as well take note of these details. Maybe I want to setup a media center PC along with steam and emulation. Knowing which games are going to work would be nice to know. And that is where a game like Assault Android Cactus would certainly shine: on a big screen TV launched from frontend software like BigBox or Steam big picture mode and played with an actual dual stick gamepad.

As another example we have this criteria:

left('Raw Data'!G2:G1999)="y"))

Column G, about compatibility with old low-res 4:3 LCDs, I made further notes to myself (like y: sometimes “out of range” err). It seems some games are more compatible with such screens than others.

I used the left function to start from the left the cell value and see if this returns true to have the letter “y”. In other words I’m asking the question does the cell start with the letter y?.

If you go through the long unique/filter list line above one condition at a time (separate the lines at the commas) it will become more obvious how it works.

From the start file of “Raw Data” to the final criteria tab I managed to go from 1023 lines down to 70 entries. It’s true I now have a large portion of the entries set to “pending” that might otherwise show up on that list.

Luckily with filters I can go in and get only the entries with the status of pending for any specific category like gamepad support if I wanted to.