1

How would I create an automatically updating Google spreadsheet containing information about Recent Activity in Steam?

Table must look like here: Table example

But the hours column must automatically updating current hours from here: Recent Activity

Steam acc: http://steamcommunity.com/id/PerfectPlays

1 Answer 1

3

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
7
  • Thx u a lot. But can i ask why i have 2 errors? i.gyazo.com/9bbabac8bb1710102adf2276bececb6d.png i.gyazo.com/ff368c40c6d17eed511c6ec837e64f6b.png
    – user95432
    Jun 5, 2015 at 0:13
  • @user95432 Try to use a semicolon (;) instead of a comma (,): =IMPORTXML("http://steamcommunity.com/id/PerfectPlays"; "//span[@class='actual_persona_name']")
    – Jacob Jan
    Jun 5, 2015 at 8:18
  • @Jacob Jan Tuinstra Last question. How can I get to auto-update a this info to another sheet? For example this table with "Steam acc" and "Hours" on sheet with name "123" but i want some of this information (not all, only what i need) about hours on other sheet. What should I write in cell on other sheet?
    – user95432
    Jun 5, 2015 at 11:57
  • @user95432 You can use the QUERY formula to do that. Add the following in sheet 123: =QUERY('Other Sheet Name'!A:G; "SELECT A, D, G")
    – Jacob Jan
    Jun 5, 2015 at 12:01
  • @Jacob Jan Tuinstra Understood thx u! Sry for disturbing u again, but its possible take information about recent activity not from here (steamcommunity.com/id/PerfectPlays), but from here (steamcommunity.com/id/PerfectPlays/games/?tab=recent) and only about "Dota 2" hrs last two weeks?
    – user95432
    Jun 5, 2015 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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