Jump to content
brainymidget

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

Recommended Posts

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 by Kaphias

Salamoniesunsetsig5.png

8,325th to 99 Firemaking 3/9/08 | 44,811th to 99 Cooking 7/16/08

4,968th to 99 Farming 10/9/09 | Runescaper August 2005-March 2010

Tip.it Mod Feb. 2008-Sep. 2008 | Tip.it Crew Sep. 2008-Nov. 2009

Share this post


Link to post
Share on other sites

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


Capt_Davy.png AbandonnedHeroKeyer25.png

Share this post


Link to post
Share on other sites

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 ;)


Xenoxai.png

Share this post


Link to post
Share on other sites

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.


Salamoniesunsetsig5.png

8,325th to 99 Firemaking 3/9/08 | 44,811th to 99 Cooking 7/16/08

4,968th to 99 Farming 10/9/09 | Runescaper August 2005-March 2010

Tip.it Mod Feb. 2008-Sep. 2008 | Tip.it Crew Sep. 2008-Nov. 2009

Share this post


Link to post
Share on other sites

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


Xenoxai.png

Share this post


Link to post
Share on other sites

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?


Salamoniesunsetsig5.png

8,325th to 99 Firemaking 3/9/08 | 44,811th to 99 Cooking 7/16/08

4,968th to 99 Farming 10/9/09 | Runescaper August 2005-March 2010

Tip.it Mod Feb. 2008-Sep. 2008 | Tip.it Crew Sep. 2008-Nov. 2009

Share this post


Link to post
Share on other sites

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


Xenoxai.png

Share this post


Link to post
Share on other sites

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.


Salamoniesunsetsig5.png

8,325th to 99 Firemaking 3/9/08 | 44,811th to 99 Cooking 7/16/08

4,968th to 99 Farming 10/9/09 | Runescaper August 2005-March 2010

Tip.it Mod Feb. 2008-Sep. 2008 | Tip.it Crew Sep. 2008-Nov. 2009

Share this post


Link to post
Share on other sites

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.


Naaxi.png

Share this post


Link to post
Share on other sites

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


"Happiness depends more on the inward disposition of mind than on outward circumstances."

Benjamin Franklin

 

"Insanity: doing the same thing over and over again and expecting different results."

Albert Einstein

 

 

 

 

G%20I%20Jackass.png

G_I_Jackass.png

Share this post


Link to post
Share on other sites

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!


svenne.png

#14340th to 99 magic!! w000t

Share this post


Link to post
Share on other sites

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.


Naaxi.png

Share this post


Link to post
Share on other sites

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!


M3DO.png

Mamiću odlazi!

Share this post


Link to post
Share on other sites

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;",";"."))))


Diederik22

Share this post


Link to post
Share on other sites

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

 

ldDj6tr.jpg

 

 

 

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:

 

WAkjd8q.jpg

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.