www.webdeveloper.com
Results 1 to 13 of 13

Thread: Help using a Google Spreadsheet with a script

  1. #1
    Join Date
    Dec 2012
    Posts
    8

    Help using a Google Spreadsheet with a script

    I was hoping to get help using Google Spreadsheet data with a script I have paste below. The script is a calculator/comparison tool, that compares movie rating with one another. Essentially, the scrip displays two drop down lists (both lists are identical), and you choose one movie in one drop down and another movie in other drop down, then you hit calculate. Inside the script, which you will see below, I have assigned values to each movie and as you can imagine, this becomes tedious. So what I would like to do is use a Google Drive Spreadsheet to almost power the data inside this script, so when the google spreadsheet is saved, the values change inside the script.

    1. Here is the google spreadsheet (viewable): https://docs.google.com/spreadsheet/...zRNakFVVHExYkE

    ... as you can see, the data is pretty straight forward, and matches what's inside the script, however, given I'm forced to surround the data by '<option value=xx.xx> and '</option>, I am unsure how to allow the data on the spreadsheet to power this calc.

    2. Script:
    HTML Code:
    <html>
    <head>
    
    <script type="text/javascript">
    
      var _gaq = _gaq || [];
      _gaq.push(['_setAccount', 'UA-30589636-1']);
      _gaq.push(['_trackPageview']);
    
      (function() {
        var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
        ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
        var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
      })();
    
    </script>
    <body>
    
    <select id="player1">
    <option value=00.00>SELECT MOVIE</option>
    <option value=80.50>X-Files 1 (Sci-Fi)</option>
    <option value=78.50>Star Trek (Sci-Fi)</option>
    <option value=100.00>Die Hard 1 (Action)</option>
    <option value=88.50>Die Hard 2 (Action)</option>
    <option value=58.00>Die Hard 3 (Action)</option>
    </select>
    
    <p>Vs.</p>
    
    <select id="player2">
    <option value=00.00>SELECT MOVIE</option>
    <option value=80.50>X-Files 1 (Sci-Fi)</option>
    <option value=78.50>Star Trek (Sci-Fi)</option>
    <option value=100.00>Die Hard 1 (Action)</option>
    <option value=88.50>Die Hard 2 (Action)</option>
    <option value=58.00>Die Hard 3 (Action)</option>
    </select>
    <input type="button" value="Calc" onclick="calcVals()"/>
    
    <p>=</p>
    
    <div id="results"></div>
    <script type="text/javascript">
    
    function calcVals() {
    var score1=document.getElementById("player1").value
    var score2=document.getElementById("player2").value
    var pick1=document.getElementById("player1").options[document.getElementById("player1").selectedIndex].text
    var pick2=document.getElementById("player2").options[document.getElementById("player2").selectedIndex].text
    var grandscore=score1-score2;
    if (grandscore==0){
    document.getElementById("results").innerHTML=pick1+" <p><strong>is as good as</strong></p> "+pick2
    } else {
    document.getElementById("results").innerHTML=grandscore>0?pick1+" <p><strong>is better than</strong></p> "+pick2+" <em>by</em> "+grandscore+" <strong>percent</strong>":pick2+" <p><strong>is better than</strong></p> "+pick1+" <em>by</em> "+Math.abs(grandscore)+" <strong>percent</strong>"
    	}
    }
    </script>
    So, again, in Summary:

    I. I would love to have the above script adjusted so that automatic saves on the Google Spreadsheet will just show up on the calc.
    II. I essentially do not want to even have to open up my javascript file any longer, I'd like to just have to update my google spreadsheet


    Thanks in advance if anyone can provide a solution to this!!!!

  2. #2
    Join Date
    Nov 2010
    Posts
    1,038
    is the spreadsheet published? you should be able to access the data like this:
    https://developers.google.com/gdata/...adsheet_sample

    but I can't get it working (or maybe I am reading the documentation wrong)

  3. #3
    Join Date
    Dec 2012
    Posts
    8

  4. #4
    Join Date
    Dec 2012
    Posts
    8
    xelawho: Much appreciated on the info, however, I'm not following...

  5. #5
    Join Date
    Dec 2012
    Posts
    8
    And that spreadsheet should be published now, try again if you could?

  6. #6
    Join Date
    Nov 2010
    Posts
    1,038
    this seems to work - it goes a bit funny on the last option because you haven't given Die Hard 3 a rating. I put an alert in the onchange so you can see the values coming through. It's a bit of a low-rent way of looping through the columns, but I couldn't see another...

    Code:
    <body>
    <select id="res" onchange="alert(this.value)"></select>
    <script>
    function getData(o){
    var idx=0;
    var obj=o.feed.entry;
    for (var i = 0; i < obj.length; i++) {
    if(i%3==0){
    document.getElementById("res").options[idx]=new Option(obj[i+1].content.$t,obj[i].content.$t)
    console.log(document.getElementById("res").options[idx])
    idx++        }
    	}
    }
    </script>
    <script src="https://spreadsheets.google.com/feeds/cells/0AjdJSwsrKzjqdHppVmlQSktrNmxnazRNakFVVHExYkE/od6/public/basic?alt=json-in-script&callback=getData"></script>
    </body>
    Last edited by xelawho; 12-27-2012 at 02:58 PM. Reason: slightly lower-rent method

  7. #7
    Join Date
    Dec 2012
    Posts
    8
    xelawho:

    I may have tested your code wrong, but my goal (and my javascript that i pasted above) has two different drop down lists, as my goal is allow the user to select one movie in one drop down list and another in the other drop down list (Even if the movies are the same)... if the same, the calculator will say that they are equal. It's a comparison calculator.

    Any chance you could help me create this from what you made, or help me manipulate my javascript above to read the google spreadsheet? Thanks a ton!!!!!!!

    So what I'm after:
    1. Two drop down lists
    2. Same identical lists in each drop down
    3. The same movie being selected in each would call for an equal rating
    4. Somehow have the calculator say what movie is better, or just repeat the values would be sufficient enough, as the user would know which is rated higher if it gave both values
    5. I am wanting to just adjust the values inside the spreadsheet and again I only need one spreadsheet, as the drop down lists will again use the same data/list

  8. #8
    Join Date
    Nov 2010
    Posts
    1,038
    It's basically the same code, with another select and your function and html added. Here you go:
    Code:
    <!doctype html>
    <html lang="en">
    <head>
    <body>
    <select id="player1"></select>
    <p>Vs.</p>
    <select id="player2"></select><br>
    <input type="button" value="Calc" onclick="calcVals()"/>
    
    <p>=</p>
    
    <div id="results"></div>
    <script>
    function getData(o){
    var idx=0;
    var obj=o.feed.entry;
    for (var i = 0; i < obj.length; i++) {
    if(i%3==0){
    document.getElementById("player1").options[idx]=new Option(obj[i+1].content.$t+" "+obj[i+2].content.$t,obj[i].content.$t);
    document.getElementById("player2").options[idx]=new Option(obj[i+1].content.$t+" "+obj[i+2].content.$t,obj[i].content.$t)
    idx++        }
    	}
    }
    
    function calcVals() {
    var score1=document.getElementById("player1").value
    var score2=document.getElementById("player2").value
    var pick1=document.getElementById("player1").options[document.getElementById("player1").selectedIndex].text
    var pick2=document.getElementById("player2").options[document.getElementById("player2").selectedIndex].text
    var grandscore=score1-score2;
    if (grandscore==0){
    document.getElementById("results").innerHTML=pick1+" <p><strong>is as good as</strong></p> "+pick2
    } else {
    document.getElementById("results").innerHTML=grandscore>0?pick1+" <p><strong>is better than</strong></p> "+pick2+" <em>by</em> "+grandscore+" <strong>percent</strong>":pick2+" <p><strong>is better than</strong></p> "+pick1+" <em>by</em> "+Math.abs(grandscore)+" <strong>percent</strong>"
    	}
    }
    </script>
    <script src="https://spreadsheets.google.com/feeds/cells/0AjdJSwsrKzjqdHppVmlQSktrNmxnazRNakFVVHExYkE/od6/public/basic?alt=json-in-script&callback=getData"></script>
    </body>
    </html>

  9. #9
    Join Date
    Dec 2012
    Posts
    8
    xelawho: You are awesome! Thanks so much!!!!!! Last question, if i wanted it to compare 2 vs 2? So two drop downs "vs" 2 drop downs?

  10. #10
    Join Date
    Nov 2010
    Posts
    1,038
    Can you explain how you would compare 2 vs. 2?

  11. #11
    Join Date
    Dec 2012
    Posts
    8
    Sorry, what I mean is.

    [ drop down list ] & [ drop down list ]

    vs.

    [ drop down list ] & [ drop down list ]

    [ calculate ]


    So:

    [ Diehard 1 ] & [ X-Files ]

    vs.

    [ Diehard 2 ] & [ Diehard 3 ]

    [ calculate ]

    = This would suggest that the first two are better than the second two by, i think my math is right, 14 or so %... I know this seems overboard, but I'd use it for more than just 2 for 2 movie comparison calculations, so 2 for 2 would be the most ideal for me.

    I can't thank you enough!

  12. #12
    Join Date
    Nov 2010
    Posts
    1,038
    so you make another 2 selects, fill the options the same way, add the selected values of 1 & 2 and 3 & 4 and compare them in the same way. Which bit are you having trouble with?

  13. #13
    Join Date
    Dec 2012
    Posts
    8
    Nope, figured out what I did wrong. Got it! Thanks so much, appreciate your help! You rock! This site rocks!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles