Discussions

Expand all | Collapse all

Automatic website import to QB field

  • 1.  Automatic website import to QB field

    Posted 10-22-2018 20:53
    We are trying to create a field/fields that will pull rates from www.snaprates.com into Quickbase that can be automatically update. Any help on this would be great. Thank you.  




  • 2.  RE: Automatic website import to QB field

    Bronze
    Contributor
    Posted 10-22-2018 21:12
    Many solutions can be there, depending on what that website provides, in terms of RSS feeds, json output, XML outputs etc. But without all the information, one solution that I can think of is to use Selenium automation to create a bot that could go to that website, copy selected portions of that website, and import that data into your qb tables, just as if a human being is doing it.


  • 3.  RE: Automatic website import to QB field

    Top
    Contributor
    Posted 10-22-2018 22:32
    There are a lot of reasons you can't do this:

    (1) snaprates.com does not support https so you would get "mixed content" error.

    (2) snaprates.com does not support cross domain requests.

    (3) snaprates.com may or may not allow you to scrape their data

    But this is QuickBase and nothing is impossible!

    It has been years since I have done this but you can still use my old pal YQL (Yahoo Query Language) to fetch the data through their servers and return it to QuickBase (thus circumventing the same origin policy your browser enforces).

    Here is proof:



    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=678

    To repeat this result just copy the code in the above pastie and paste it into the console.

    The key thing to note is that despite QuickBase and SnapRates being hosted on separate domains QuickBase can in fact access the SnapRates data by going through the YQL service. Note the dialog displaying the SnapRates data is being displayed within QuickBase.

    You didn't say what data you wanted to extract so I just grabbed all the table data. It is trivial to extract specific data from the table data.






  • 4.  RE: Automatic website import to QB field

    Posted 10-23-2018 14:21
    Dan, 

    That is great help. Thank you very much. To take it a step further for example I am trying take the 10yr treasury and directly put it into a field on QB so that I can do calculations with. Any idea on how to do that? 


  • 5.  RE: Automatic website import to QB field

    Top
    Contributor
    Posted 10-23-2018 16:27
    YQL uses xpath selectors (not CSS selectors) to index into the html and select nodes. So if that was the only scrap of data you wanted to grab, you could create a pinpoint xpath selector to grab the value in the first table, fourth row, second column:

    var site = "http://www.snaprates.com/index.php";
    var yql = '
      SELECT *
      FROM htmlstring
      WHERE url='${site}' AND xpath='//section[@id="family1"]//table/tbody/tr[4]/td[2]'
      ';
    var resturl = "https://query.yahooapis.com/v1/public/yql?q="; +
      encodeURIComponent(yql) + 
      "&format=json" +
      "&env=store://datatables.org/alltableswithkeys";
    $.get(resturl, function(data) {
      //console.log(JSON.stringify(data.query.results.result, null, "  "))
      $("<div id=QBU_dialog>").html(data.query.results.result).dialog();
    });

    This will result in the following dialog:


    To get this value into a table you stuff the value 
        data.query.results.result
    into a field of the open form or call the API and place it where you want.

    This is a very general procedure that can be used to extract data from just about any web page.


  • 6.  RE: Automatic website import to QB field

    Posted 10-23-2018 17:38
    Dan: Would the "stuff" action be via a formula field? I get an error when i run the code.



    I am new to writing the API call function for edit record getting data from an outside source into the form.. Thank you,



  • 7.  RE: Automatic website import to QB field

    Top
    Contributor
    Posted 10-23-2018 17:44
    Apparently there is an extra semicolon in this code:
    var resturl = "https://query.yahooapis.com/v1/public/yql?q="; +
      encodeURIComponent(yql) + 
      "&format=json" +
      "&env=store://datatables.org/alltableswithkeys";
    Remove it and you should be good:
    var resturl = "https://query.yahooapis.com/v1/public/yql?q=" +
      encodeURIComponent(yql) + 
      "&format=json" +
      "&env=store://datatables.org/alltableswithkeys";