0

I have this link with a lot of info:

https://steamcommunity.com/market/pricehistory/?currency=1&appid=570&market_hash_name=Exalted%20Fractal%20Horns%20of%20Inner%20Abysm

I want to import all the data to a google sheet

I am using IMPORTDATA Function but I think it doesn't support all the lenght of the data.

This is the code:

=IMPORTDATA("https://steamcommunity.com/market/pricehistory/?currency=1&appid=570&market_hash_name=Fractal%20Horns%20of%20Inner%20Abysm")

I am getting this error:

Error Could not fetch url: https://steamcommunity.com/market/pricehistory/?currency=1&appid=570&market_hash_name=Fractal%20Horns%20of%20Inner%20Abysm

George
  • 1,196
  • 1
  • 2
  • 10
Jean Lock
  • 13
  • 3
  • The link from `steamcommunity` doesn't return any data but `[]`. Tried other sample links online with `IMPORTDATA` function & it works. Perhaps this is isolated to the `steamcommunity`, can you confirm if you get the data when accessing the link manually on your end? – SputnikDrunk2 Nov 11 '22 at 01:09
  • yea, when I access the link the data shows – Jean Lock Nov 11 '22 at 02:40
  • I see. [This is what I get](https://imgur.com/a/Qn2x986) when I'm trying to replicate it on my end. – SputnikDrunk2 Nov 11 '22 at 02:56

1 Answers1

0

I found an excellent workaround for this!

Instead of using the Steams Market API, I used =IMPORTDATA() to import source code data from the item page itself. Next, I use =INDEX() to get the code line where the Historical Market Data lies (For CSGO items, Stickers/Containers are on line 712 and Weapon skins are on line 1406). Then, =TRANSPOSE() to rotate the data from horizontal to vertical.

=Transpose(INDEX(IMPORTDATA("https://steamcommunity.com/market/listings/730/"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","%20"),"(","%28"),")","%29"),"|","%7C")),if(C1,1406,712)))

This formula takes the item name (C2 in my spreadsheet) as it shows on the market ("Sticker | Battle Scarred (Holo)"), and substitutes the ASCII into code that makes URLs work (ex: Sticker%20%7C%20Battle%20Scarred%20%28Holo%29).

Edit: You'll need to find the source code line for Dota itmes. Right click on the item page a click "View page source" and use Ctrl+F and search for "var line1".

The Code line you find wont be the exact line you need. This is because the source code have blank lines and the fromula doesn't include those blank lines. So, with some trial and error, you find the right line.

Also, you will need to the URL to load Dota Items
https://steamcommunity.com/market/listings/730 < Change the 730 to 570.

Edit 2: I tested that item and the resulting array for that form wasw too large for the spreadsheet. I'll work on a fix for this and add a date range feature.

avariant
  • 2,234
  • 5
  • 25
  • 33
Cat Eyes
  • 1
  • 1