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

[Kaphias]

So far my spreadsheet for farming is coming along great, however, I'm having the issue that googledocs likes to return things back with letters (ie. lantadyme seed, comes back as 32.1k, rather than 32100), which makes them unusable in calculations.

 

How can I make it so googledocs understands "k" to mean "1000"?

 

EDIT: my problem is the same as Ickdeep's.

You can see how I've done it on my Farming sheet here, bottom left.

EDIT: or even better

=if(iferror(find("m",B21),false),left(B21,find("m",B21)-1)*1000000,if(iferror(find("k",B21),false),left(B21,find("k",B21)-1)*1000,B21))

where "B21" is the import cell.

Edited June 13, 2010 by Kaphias

[Capt_Davy]

Thank you, Kaphias! I've just tweaked it a bit, and it works for my spreadsheet! :thumbsup:

 

Aaaahhhh....time to redo it another 5 times :)

 

If anyone's interested, I'll share it as soon as I finish it. (it basically just has the top 5 herbs to plant for profit, and torstol, assuming Scroll of Life)

 

EDIT: here's the link, if anyone's interested (to be honest, I'd be surprised, seeing at how effective Troacctid's sheet is)

 

https://spreadsheets.google.com/ccc?key=0Ahzpz1w3bcYcdDJVemZVRTBvelhGZ0lUZTdjWDhiQ1E&hl=en

[teuneboon]

I tried hard, but I just keep getting #Error(Parse Error). This is the code I use:

=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)

 

The spreadsheet is:

http://spreadsheets.google.com/ccc?key=0AjhQ7wHYDKCOdE1MazltMXhhbnJ2NF9yQ2ZLSmdMVnc&hl=nl&authkey=CLqMvRY

Check column J, the error is there. I used Chrome on Ubuntu to create this, but it should work, since when I view other spreadsheets that use this method it also works.

 

For those who want to know what this is about: it will be my Merchanting Log, the first entry is fake by the way ;)

[Kaphias]

I tried hard, but I just keep getting #Error(Parse Error). This is the code I use:

=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)

 

The spreadsheet is:

http://spreadsheets.google.com/ccc?key=0AjhQ7wHYDKCOdE1MazltMXhhbnJ2NF9yQ2ZLSmdMVnc&hl=nl&authkey=CLqMvRY

Check column J, the error is there. I used Chrome on Ubuntu to create this, but it should work, since when I view other spreadsheets that use this method it also works.

 

For those who want to know what this is about: it will be my Merchanting Log, the first entry is fake by the way ;)

Just tried your code on my sheet and it worked fine. I can't imagine browser/OS would make a difference. One thing I would try: make a new sheet (in the same doc) and try to get it working there, without all the other things you have on your current sheet. Just "Dark Bow" and your code. You may need to reload the sheet or something to get it to work, though I hardy ever find that to be the case.

[teuneboon]

I tried hard, but I just keep getting #Error(Parse Error). This is the code I use:

=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)

 

The spreadsheet is:

http://spreadsheets.google.com/ccc?key=0AjhQ7wHYDKCOdE1MazltMXhhbnJ2NF9yQ2ZLSmdMVnc&hl=nl&authkey=CLqMvRY

Check column J, the error is there. I used Chrome on Ubuntu to create this, but it should work, since when I view other spreadsheets that use this method it also works.

 

For those who want to know what this is about: it will be my Merchanting Log, the first entry is fake by the way ;)

Just tried your code on my sheet and it worked fine. I can't imagine browser/OS would make a difference. One thing I would try: make a new sheet (in the same doc) and try to get it working there, without all the other things you have on your current sheet. Just "Dark Bow" and your code. You may need to reload the sheet or something to get it to work, though I hardy ever find that to be the case.

I tried it in all different browsers and on windows too. Still can't get it to work. This is just plain weird XD

[Kaphias]

I tried hard, but I just keep getting #Error(Parse Error). This is the code I use:

=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)

 

The spreadsheet is:

http://spreadsheets.google.com/ccc?key=0AjhQ7wHYDKCOdE1MazltMXhhbnJ2NF9yQ2ZLSmdMVnc&hl=nl&authkey=CLqMvRY

Check column J, the error is there. I used Chrome on Ubuntu to create this, but it should work, since when I view other spreadsheets that use this method it also works.

 

For those who want to know what this is about: it will be my Merchanting Log, the first entry is fake by the way ;)

Just tried your code on my sheet and it worked fine. I can't imagine browser/OS would make a difference. One thing I would try: make a new sheet (in the same doc) and try to get it working there, without all the other things you have on your current sheet. Just "Dark Bow" and your code. You may need to reload the sheet or something to get it to work, though I hardy ever find that to be the case.

I tried it in all different browsers and on windows too. Still can't get it to work. This is just plain weird XD

Yeah, very odd. Did you try it on a new sheet like I said above?

[teuneboon]

I tried hard, but I just keep getting #Error(Parse Error). This is the code I use:

=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)

 

The spreadsheet is:

http://spreadsheets.google.com/ccc?key=0AjhQ7wHYDKCOdE1MazltMXhhbnJ2NF9yQ2ZLSmdMVnc&hl=nl&authkey=CLqMvRY

Check column J, the error is there. I used Chrome on Ubuntu to create this, but it should work, since when I view other spreadsheets that use this method it also works.

 

For those who want to know what this is about: it will be my Merchanting Log, the first entry is fake by the way ;)

Just tried your code on my sheet and it worked fine. I can't imagine browser/OS would make a difference. One thing I would try: make a new sheet (in the same doc) and try to get it working there, without all the other things you have on your current sheet. Just "Dark Bow" and your code. You may need to reload the sheet or something to get it to work, though I hardy ever find that to be the case.

I tried it in all different browsers and on windows too. Still can't get it to work. This is just plain weird XD

Yeah, very odd. Did you try it on a new sheet like I said above?

Yes I did, also tried it on different pcs XD

[Kaphias]

Yes I did, also tried it on different pcs XD

Well you got me, I'm stumped. I'd try PMing Morionic, he's pretty good at sheets and may have an idea.

[Grimy_Bunyip]

Yes I did, also tried it on different pcs XD

Well you got me, I'm stumped. I'd try PMing Morionic, he's pretty good at sheets and may have an idea.

nope, I'm stumped too. Your code is correct. I even tried downloading your spreadsheet, pasting my code into it, and it still doesn't work.

So clearly there's something wrong with your spreadsheet itself, since the rest of mine is fine.

 

Try making a new spreadsheet

If that doesn't work, click my siggy, go to the sample spreadsheet, copy it to your account, delete everything on my spreadsheet

then recreate your spreadsheet from scratch again.

[JohnTheCop]

great guide i refrence it every time i use google docs :D

[sven_bohikus]

Yes I did, also tried it on different pcs XD

Well you got me, I'm stumped. I'd try PMing Morionic, he's pretty good at sheets and may have an idea.

nope, I'm stumped too. Your code is correct. I even tried downloading your spreadsheet, pasting my code into it, and it still doesn't work.

So clearly there's something wrong with your spreadsheet itself, since the rest of mine is fine.

 

Try making a new spreadsheet

If that doesn't work, click my siggy, go to the sample spreadsheet, copy it to your account, delete everything on my spreadsheet

then recreate your spreadsheet from scratch again.

Hi again guys - I think I might have a solution to your problem. It depends on the locate that the google account was created in. Sometimes instead of commas to parse elements in formulas you need to use semicolons. This is not clearly documented in the GoogleDocs Docs - but I ran into this one time before with someone trying to use my spreadsheets.

 

Basically, in some countries comma is used as a decimal point - therefore it cant be used to parse elements in formulae. Replace all your comma with semicolons and see what happens.

 

Cheers!

[VonMedo]

It's not working anymore!!!!!!!

[Grimy_Bunyip]

It's not working anymore!!!!!!!

Grimy bunyip to the

 

change the 2 after "table" to a 1 and u'll be good to go

=Index(ImportHtml(CONCAT("http://itemdb-rs.runescape.com/results.ws?query=",INPUTCELL), "table", 1),2,3)

 

I personally prefer importing prices differently.

But I guess this method is quick and intuitive.

[VonMedo]

It's not working anymore!!!!!!!

Grimy bunyip to the

 

change the 2 after "table" to a 1 and u'll be good to go

=Index(ImportHtml(CONCAT("http://itemdb-rs.runescape.com/results.ws?query=",INPUTCELL), "table", 1),2,3)

 

I personally prefer importing prices differently.

But I guess this method is quick and intuitive.

 

 

YOU ARE THE BEST!

[diederik22]

Today try to make some calculation sheets all worked well for me till i try 'k' and 'm' valued items.

 

I got the warning the the 10.0 value was text and couldn't be multiplied.

I made the m/k formula  a little bit more ugly, but it will work now.

 

=if(iferror(find("m";C3);false);VALUE(SUBSTITUTE(left(C3;find("m";C3)-1);".";","))*1000000;

  if(iferror(find("k";C3);false);VALUE(SUBSTITUTE(left(C3;find("k";C3)-1);".";","))*1000;VALUE(SUBSTITUTE(C3;",";"."))))

[Adam-]

its pretty easy if you ask me

[demoner]

2017/2018 UPDATE

 

Hello whoever stumbles across this. If you're like me, you've been stumbling around the internet looking for the valuable information that has been posted in this thread. Sadly, the compiled information is mostly out of date or way too complicated for a newbie like me to decimate.

 

Allow me to show a simple way to pull regularly updated GE prices from Tip.it into your Google Sheets doc.

 

1) Find an item you want on Tip.it's item pages, and copy the website URL. Here's an example https://www.tip.it/runescape/items/view/1878-nature-rune

 

2) In your Google Sheets doc, click on the cell and input then hit enter: 

=IMPORTHTML("https://www.tip.it/runescape/items/view/1878-nature-rune","table", 3)

[Note: The quotation marks are necessary, you will get errors if you don't use them.]

 

 

It should look like this

 

 

 

 

3) Now all you have to do is use that same code in each cell you want a new item, just replace the URL 

 

 

That's it! This will update every day or so on it's own. It doesn't look like much but with a little formatting you can make it look good, like this: