As you know, Digi and I have built an alliance algorithm using the game's data feeds. I've stated before that I am a tech mortal, and thus decided to build our algorithm in a Google Sheet because that platform is about the extent of my tech abilities. So, that's why we've built it where we've built it.

The good news is that it is working wonderfully. We're currently working on building in a new dimension that takes into account the races of alliance leaders.

I've run into a snag, however, and am wondering if you can help me with it.

We generally use IMPORTXML in Google Sheets. We'll do one column with //playername, another with //playernameid/@id, etc., using XPATH queries. This has worked great. 

However, there is a quirk with the Player Feed. If a player isn't in an alliance, they have no alliance role ID. Example, Kumo:

KillerPoodle, on the other hand:

So, when I send the //allianceroleid/@id" data down a column next to players' names and other IDs, the data gets out of sync, because not everyone has an alliance role id.

Is there any way to tell IMPORTXML to insert a zero in place of where the alliance role ID returns whenever it confronts a player with no alliance role ID?

If not, can you think of any other way to address this? I'm kind of stumped.

Thanks!

JJ

Since all players not in an alliance have an alliance ID of zero, I did a FILTER from one sheet to another where the alliance rank column had to be >0. From there, I ran the alliancerole ids alongside that filtered list and it matched perfectly. Yay!