Jump to content

Welcome to Rune Tips, the first ever RuneScape help site. We aim to offer skill guides, quest guides, maps, calculators, informative databases, tips, and much more to help you get the most from the Massive Online Adventure Game, RuneScape, by Jagex Ltd © 2009.

Report Ad

Welcome to Forum.Tip.It
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. If you already have an account, login here - otherwise create an account for free today!
Photo

How to use Google Docs to extract GE prices ~By Brainymidget


  • Please log in to reply
116 replies to this topic

#41
Blitzer150
[ Display Name History ]

Blitzer150

    Chicken Feather

  • Members
  • 4 posts
  • Joined:6 March 2006
"#N/A"



Even when I've correctly put the formula into the box and written in the item name correctly, I still keep getting this.



Any thoughts?



Sometimes even once a price update is complete (yes its complete, not occurring) the prices flicker between #N/A and their price.



Any thoughts?
Posted Image

#42
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
I realized that for most items "#N/A" is displayed right after GE updates, when the items' prices are getting updated. (right after GE update my entire page is full of that) Usually, it takes GoogleDocs around 20-30 minutes to have the new price showing. Since items on the GE aren't updated all at once and some even get double updated, expect #N/A to be there up to 30 minutes after item updates at many times of the day



EDIT: Realized that its not limited only to ge updates.
Billionaire status as of 7/19/2009

#43
Robert_R
[ Display Name History ]

Robert_R

    Demon Vanquisher

  • Members
  • 2,026 posts
  • Gender:Male
  • Location:Behind the scary door
  • Joined:10 June 2009
  • RuneScape Status:P2P
  • RSN '07:Robertr1
Do I have to type in every url I want to use? It says I can't paste things into the cell while editing it.

#44
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
Personally, I have a copy of the code in notepad, edit it from there, then paste it into google docs.



Makes it much easier.
Billionaire status as of 7/19/2009

#45
Robert_R
[ Display Name History ]

Robert_R

    Demon Vanquisher

  • Members
  • 2,026 posts
  • Gender:Male
  • Location:Behind the scary door
  • Joined:10 June 2009
  • RuneScape Status:P2P
  • RSN '07:Robertr1

Personally, I have a copy of the code in notepad, edit it from there, then paste it into google docs.



Makes it much easier.


Ok, but it still won't let me paste into the google docs for some reason, any ideas why?

#46
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
Have the entire code that you want to have in that cell in notepad first, edit as needed, then copy+paste the whole code into the cell.



You can't, or even if you could, shouldn't, copy+paste bits and pieces of the code into a single cell multiple times, opens up opportunities to screw up the code and is very tedious.



Also, while you are editing a single cell, google docs prohibits you from pasting; its only meant for manual editing.



Does that help?
Billionaire status as of 7/19/2009

#47
epicnuts
[ Display Name History ]

epicnuts

    Goblin Armour

  • Members
  • 97 posts
  • Gender:Male
  • Location:Belgium
  • Joined:30 October 2008
  • RuneScape Status:P2P
  • RSN:zoomkey
  • Clan:Zeitphasm
Could you please teach us how to do this in excel, as it's not easy to switch to google docs after years of using excel, and google docs misses some functions I really want to have in some of my calculators.
Posted Image

#48
AgingMiser
[ Display Name History ]

AgingMiser

    Goblin Armour

  • Members
  • 96 posts
  • Gender:Not Telling
  • Joined:23 October 2008
  • RuneScape Status:Retired
  • RSN:Aging Miser
  • RSN2:Evil Scruff
  • Clan:TTFS

First of all, absolutely wonderful guide, should help me lots in everything ;)



Someone mentioned the problem of + in the price change column, and the problem was never really addressed. This problem interferes with the ability to make a column with daily percentage changes. Would it be possible to eliminate the + from this column using the importhtml code?

Or any way to convince googledocs that the "+" means positive?

If this is too difficult, any other code eliminating the + would be greatly appreciated



Also, is it possible to make the "m" and "k" conversions directly in the importhtml code?




Yeah, but... you might be scared.



Open at your own risk, Thaa wasn't kidding.




-edit-



Robert, try double clicking the cell first, and then pasting the code. That might help.
Posted Image

#49
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
Aging Miser, tyvm.

Thats...complicated :shock:



Is it possible to make a find+replace function for a single cell deleting a +? That's a very BIG problem for me, just getting rid of the damn plus sign!
Billionaire status as of 7/19/2009

#50
AgingMiser
[ Display Name History ]

AgingMiser

    Goblin Armour

  • Members
  • 96 posts
  • Gender:Not Telling
  • Joined:23 October 2008
  • RuneScape Status:Retired
  • RSN:Aging Miser
  • RSN2:Evil Scruff
  • Clan:TTFS

Aging Miser, tyvm.

Thats...complicated :shock:



Is it possible to make a find+replace function for a single cell deleting a +? That's a very BIG problem for me, just getting rid of the damn plus sign!




Lol. Only started on Google Docs when I read this guide yesterday tbh, and I found something totally awesome.



If the price of an [item] is listed as ___k in the GE Database, use this code to isolate the number:



=MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3), 1,#)*1000



The # is equivalent to the number of digits preceding the k, plus one. Take runite ore, which is currently listed as 13.7k. There are 3 digits preceding k - 1, 3, and 7. Add 1 to that, which gives you 4, then replace the # in the code with 4.



The code to isolate a number in the millions is almost identical:



=MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3), 1,#)*1000000



Again, the # is equivalent to the number of digits preceding the m, plus one.



The code in my post above was from Thaa's post, and is a logic test - thus, in other words, a universal test that will give a correct number regardless of whether it's a k or m.



I'm working on a script for price changes right now, but for some reason, the spreadsheet's functions aren't loading. When I do find the script (should be in a couple of days, a week tops) I'll post here :geek:
Posted Image

#51
AgingMiser
[ Display Name History ]

AgingMiser

    Goblin Armour

  • Members
  • 96 posts
  • Gender:Not Telling
  • Joined:23 October 2008
  • RuneScape Status:Retired
  • RSN:Aging Miser
  • RSN2:Evil Scruff
  • Clan:TTFS
Got it.



=IF(Isnumber(http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22), http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22,MID(http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22,2,9))



Alternatively, if the price change is defined in [cell], you can use



=IF(Isnumber([cell]), [cell],MID([cell],2,9))

Posted Image

#52
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
I tried your second suggestion on that, after typing in:

=IF(Isnumber([D4]), [D4],MID([D4],2,9))

I got an error message and when I double clicked on the cell, this showed up....

=IF(Isnumber([R[-1]C[-5]]), [R[-1]C[-5]],MID([R[-1]C[-5]],2,9))

Any ideas?
Billionaire status as of 7/19/2009

#53
AgingMiser
[ Display Name History ]

AgingMiser

    Goblin Armour

  • Members
  • 96 posts
  • Gender:Not Telling
  • Joined:23 October 2008
  • RuneScape Status:Retired
  • RSN:Aging Miser
  • RSN2:Evil Scruff
  • Clan:TTFS

I tried your second suggestion on that, after typing in:

=IF(Isnumber([D4]), [D4],MID([D4],2,9))

I got an error message and when I double clicked on the cell, this showed up....

=IF(Isnumber([R[-1]C[-5]]), [R[-1]C[-5]],MID([R[-1]C[-5]],2,9))

Any ideas?




You're supposed to replace the brackets around [cell] too. So the correct code would actually be:

=IF(Isnumber(D4),D4,MID(D4,2,9))

Posted Image

#54
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
Silly me.



You're awesome. Many thanks.
Billionaire status as of 7/19/2009

#55
AgingMiser
[ Display Name History ]

AgingMiser

    Goblin Armour

  • Members
  • 96 posts
  • Gender:Not Telling
  • Joined:23 October 2008
  • RuneScape Status:Retired
  • RSN:Aging Miser
  • RSN2:Evil Scruff
  • Clan:TTFS

Silly me.



You're awesome. Many thanks.




Welcome. :]
Posted Image

#56
lamia_rog
[ Display Name History ]

lamia_rog

    Chicken Feather

  • Members
  • 2 posts
  • Joined:5 November 2005
Thanks Brainy for the guide.



Made this Herblore Sheet with it.

http://spreadsheets.... ... utput=html

#57
opeious
[ Display Name History ]

opeious

    Unicorn Horn

  • Members
  • 249 posts
  • Gender:Male
  • Location:India
  • Joined:13 May 2006
  • RuneScape Status:Semi-Retired
  • RSN:Opeious
Pwnzorg guide 10/10!



Check out my puny :? enchanted bolt guide

http://spreadsheets.... ... RcWc&hl=en
Posted ImagePosted Image

Barrows Drops:....................................................Dragon Drops:...............Other Drops:
Dharok: 2 Helms, 1 Top, 2 Legs, 4 Axes.......................2 Claws Split..................1 Bandos Tassets
Karil: 1 Coif, 1 Top, 2 Skirt, 2 Crossbows......................3 Plate legs...................4 Berserker Rings
Ahrim: 3 Hood, 2 Top, 3 Skirt, 1 Staff..........................2 Plate Skirts.................2 Warrior Rings
Verac: 1 Helm, 3 Tops, 2 Skirts, 1 Flail..........................1 Med........................1 Archer Ring
Guthan: 2 Helm, 5 Tops, 1 Skirt, 0 Spear.....................1 Shield Half.................1 Seers Ring
Torag: 2 Helms, 2 Tops, 5 Legs, 1 Hammer....................4 Hatchets..................1 Missed Divine Sigil (was at bank -,- fml)

#58
brainymidget
[ Display Name History ]

brainymidget

    Retired TET Member

  • Clan Official
  • 826 posts
  • Gender:Male
  • Location:Case Western Reserve University
  • Joined:2 October 2006
  • RuneScape Status:None
  • RSN:Brainymidget
  • Clan:SoDB
Oo Aging Miser I like it.

Edited that to include the Concat and it worked. Here's what I used for Dragon Pickaxe.

=MID(Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A5),"table",2),2,3), 1,3)*1000000
Posted Image
Posted Image

#59
TyrantElf
[ Display Name History ]

TyrantElf

    Unicorn Horn

  • Members
  • 197 posts
  • Gender:Male
  • Location:Runescape
  • Joined:1 March 2008
  • RuneScape Status:P2P
  • RSN:TyrantElf
  • RSN2:dauth reona
  • Clan:Hunt4stars
I was wondering if i could copy this to another forum, You will get full credit, and i think it will really help!


Proud Ubuntu User!

There are no stupid questions, just stupid people. -Scott Adams
“A computer once beat me at chess, but it was no match for me at kick boxing.”-Emo Phillips


My Blog!
Posted Image
Posted Image


#60
Shelby_Polo
[ Display Name History ]

Shelby_Polo

    Retired Crew Leader

  • Members
  • 1,856 posts
  • Gender:Male
  • Location:Disco, Michigan
  • Joined:30 December 2007
  • RuneScape Status:P2P
  • RSN:Shelby Polo
EDIT: Nevermind I figured out my question... :unsure:

Posted Image
Suggest a poll for Tip.it - Here!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users