Google-sheets – How to modify REGEXEXTRACT to import total hours played on Steam

You can do this with IMPORTXML.

In A2:

=IMPORTXML("http://steamcommunity.com/id/PerfectPlays", "//span[@class='actual_persona_name']")

In B2: either

=IMPORTXML("http://steamcommunity.com/id/PerfectPlays", "//div[@class='recentgame_quicklinks']/h2")

which will return "108.9 hours past 2 weeks", or

=REGEXEXTRACT(IMPORTXML("http://steamcommunity.com/id/PerfectPlays", "//div[@class='recentgame_quicklinks']/h2"),"[^ ]* [^ ]*")

where REGEXEXTRACT cuts the string down to "108.9 hours".

If you are going to do this for several account, you may want to have a column for Account Id, e.g., PerfectPlays. For example, if this column is C, the first parameter of IMPORTXML would be

"http://steamcommunity.com/id/"&C2

This is more complicated from your other question about ImportXML with XPath, because the table on the page with ?tab=recent is dynamically formed by a script. The table does not exist in the page source which ImportXML accesses.

Inspecting the source, one can see the data embedded in a script:

<script language="javascript">
...  "name":"Dota 2","last_played":1433455140,"hours":"97.3", ....

So, to get it:

1. Get all scripts on the page with XPath //script (it's also possible to get only the 12th that we need with (//script)[12], but then the import will break when they add or remove some other script).

2. Extract the number of hours with REGEXEXTRACT. This took me two commands because the regex library that Google Spreadsheets use is pretty limited; it does not support either lookaheads or lookbehinds.

3. Add any text to the number that you want.

In a single line, this is done by

=REGEXEXTRACT(REGEXEXTRACT(CONCATENATE(IMPORTXML("http://steamcommunity.com/id/PerfectPlays/games/?tab=recent", "//script")),"Dota\s+2[^{]*\d*\.\d+"),"[0-9.]*$")&" hrs last two weeks"

For readability, here is the linebroken and indented version.

=REGEXEXTRACT(
  REGEXEXTRACT(
    CONCATENATE(
     IMPORTXML("http://steamcommunity.com/id/PerfectPlays/games/?tab=recent", "//script")
    ),
    "Dota\s+2[^{]*\d*\.\d+"
  ),
  "[0-9.]*$"
) & " hrs last two weeks"