1

How would I create an automatically updating Google spreadsheet containing information about Recently Played in Steam?

Table must look like here: enter image description here

A2 taking information about account name from here: http://steamcommunity.com/id/PerfectPlays with this code: =IMPORTXML("http://steamcommunity.com/id/PerfectPlays", "//span[@class='actual_persona_name']")

B2 must take information about recentle played from here: http://steamcommunity.com/id/PerfectPlays/games/?tab=recent But only from Dota 2 game. Can someone help with code for B2?

3

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.