Register
Page 1 of 2 12 Last
Results 1 to 10 of 12
  1. #1

    Market API - Google Docs testing

    I know there is another thread that gets the data into Excel but I am too cheap to upgrade Excel from 2003

    I am looking at trying to make Google Doc's to be able to pull and work with the market API, however using my own ID I get the error

    "Data temporarily unavailable"

    Now that being said I am trying a very basic query to setup a data sheet and then query that in a working sheet.

    The query that I am trying is
    =importxml("http://theunderminejournal.com/api.market.php?key=MAGIC_STRING_THAT_I_AM_NOT_SHAR ING&realm=A-Exodar", "//realm")

    I was able to download a local copy once via chome and I "assumed" this would be my second call. I'm not sure if the data is too big for Google Docs of if the server does not like my call.

    I think I made my test call around 2:15 PM EDT on Apr 11

  2. #2
    Erorus's Avatar
    Posts
    1,404
    Reputation
    114
    Tagged in
    677 Posts
    Add to this user's reputation
    I see the following hits for A-Exodar on April 11, all yours:
    1:10PM EDT (Browser)
    2:19PM EDT (Not Browser)
    2:24PM EDT (Not Browser)
    2:34PM EDT (Not Browser)
    2:44PM EDT (Not Browser)

    Any time you put in the URL of the resource into your call, assume that it won't be cached and will instead be pulled regularly, using up your daily allotment. Instead, get the data, save it locally (or to a public server of your own), and point your app to the copy you downloaded.

    An alternative to using Google Docs would be OpenOffice.org.

  3. #3
    Well that stinks. Looks like Google calls it many times as I only recall hitting it once maybe twice before I got the unavailable error. Oh well. I'm going to play with OpenOffice but I was able to find a dirty way to get into Excel 2003 too. I'll keep looking at that as I am not very with with XML translation.

    Thanks for looking in the logs for me.

  4. #4
    The solution is pretty simple

    1) Get a cheap web site

    2) use curl to periodically (every 5 hours) download the http://theunderminejournal.com/api.m..._I_AM_NOT_SHAR ING&realm=A-Exodar"
    into a file(e.g. thefile.xml) that can be served up
    3) point google aps to http://www.cheaphosting.com/mydomain/thefile.xml

    Now you have something that is never more than 5 hours out of date, that you, google apps and others can access several times a minute without TUJ complaining.
    At a certain number of requests per realm, it would be much less stress on TUJ than all the users hitting the TUJ server.

  5. #5
    Kathroman's Avatar
    Posts
    5,452
    Reputation
    286
    Blog Entries
    20
    Tagged in
    2157 Posts
    Add to this user's reputation
    As a follow up, since my brain doesn't seem to be working today - can someone help me out with either Google Docs (preferred) or open office. The problem with open office was that it said the data exceeded the maximum number of rows, so that one might be an easier fix.

    In google, I have tried an importXML function for separate columns: ID, NAME, MARKET, QUANTITY, MARKETAVERAGE, and REAGENTPRICE - the problem is that a bunch of the items don't have a marketaverage element (which seemed weird to me) and even fewer have reagentprice. This means that the first 4 columns populate ~8000 rows of data correctly, but marketaverage only fills ~5500, and reagentprice ~2500. This would be fine except that instead of aligning properly with the correct item rows, they fill the top 5500 and 2500 and everything below it is left empty.

    The google documentation is actually surprisingly lousy, can someone help me with this? My other option was to parse the xml file into a mysql database and then extract it again with blank values for the missing elements, but that's just because I'm familiar with php/mysql - if there is a better/faster way, I'd love to hear about it.

    Thanks
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

  6. #6
    Can't you write a function to insert NULL/0 if nothing is defined?

    (I don't know much about spreadsheets.)

  7. #7
    Kathroman's Avatar
    Posts
    5,452
    Reputation
    286
    Blog Entries
    20
    Tagged in
    2157 Posts
    Add to this user's reputation
    The problem is that, from my understanding, google limits the number of import calls to 50 per spreadsheet, so I could write a conditional function, but from what I can tell, it would only grab data for 50 items, leaving me short by about 8200...not ideal.

    I think I'm going to have to write a little script for it to grab the data, parse it and then conditionally plug it into the cells, but as I mentioned, google's documentation and the examples they give aren't great. Also the google results for "google apps script" are, oddly enough, dominated by google results...
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

  8. #8
    I think I would probably do the same thing -- by writing a script to prepare the data for entry. Or maybe you can convince Erorus to give you data with NULL variables.

    You may have already passed by these pages, but...

    Someone said something about importing data raw, and then filtering it from there. Is this affected by the 50 import calls you were referring to?
    http://www.google.com/support/forum/...d8aac2d2&hl=en

    Google Apps Scripts: Tutorial on How to Parse XML
    http://code.google.com/googleapps/ap..._tutorial.html

    Again, sorry I'm not too helpful. My experience with Google Docs is limited to viewing documents.

  9. #9
    Kathroman's Avatar
    Posts
    5,452
    Reputation
    286
    Blog Entries
    20
    Tagged in
    2157 Posts
    Add to this user's reputation
    Yeah, I've been through those ones. I feel like I've already bothered Erorus enough...

    I'll figure it out, my brain is just basically turned off for the weekend.

    EDIT: I have a copy of Excel 2002 at work and was able to import it just fine and then upload it to Google without any problems. All a really needed was set of data to start working with, I don't need to keep it updated just yet. I will take a look at that down the road, so if there are any google apps script experts out there that would like to chime in before I take the plunge, I'd greatly appreciate it.
    Last edited by Kathroman; April 18th, 2011 at 10:20 AM.
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

  10. #10
    Kathroman's Avatar
    Posts
    5,452
    Reputation
    286
    Blog Entries
    20
    Tagged in
    2157 Posts
    Add to this user's reputation
    @Erorus. Out of curiosity (since I believe I've maxed out my available requests, lol) How much work would it be to alter the xml data to ensure that all the sub-elements show up, even if there isn't any data? I'm assuming that would have a pretty big impact on the size of the file, since it seems to be about ~30% of what is currently available, I'm just wondering if that is the only real obstacle, or if it presents any extra hassle for you.

    Please, don't go out of your way on my account, because I've already found a temporary workaround and (with a bit of a kick) can probably get it to do exactly what I want it to on my own, anyway. Just curious, that's all.
    Like what you see? Become an Ethereal Contributor to help support the site and gain access to additional perks.

        

    Guides: Thunder Bluff Vendor Supply Route | Entry Level JC Strategy | Alternative DMF Guide

    TSM 2.0: TSM 2.0 Groups & Operations GUIDE | TSM 2.0 Basic JC Shuffler | TSM 2.0 ADVANCED JC Shuffler

 

 

Similar Threads

  1. Online TUJ FusionTable - There's a Google beta for that
    By Hagu in forum Official Forum of The Undermine Journal
    Replies: 0
    Last Post: May 25th, 2011, 07:31 PM