Forum:

HTML Pages with CSS and JavaScript

Feed Select Option in HTML from a Google SpreadSheet

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi

I'm trying to feed a Selection Option from values in a Google SpreadSheet

So instead of having:

I'm trying to get those values from a Google SpreadSheet and fill up the Selector.

Here is what I'm trying but doesn't seam to work. What I'm doing wrong?

This is the Link to the Google SpreadSheet

Thank you very much for your help

Bear Bibeault

Sheriff

Posts: 67636

173

I like...

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

That looks like PHP, no?

I've moved this to the PHP forum.

[Asking smart questions] [About Bear] [Books by Bear]

HTML 5 (verified skill)
Skill verified by Jeanne Boyarsky

CSS 3 (verified skill)
Skill verified by Jeanne Boyarsky

JavaScript (verified skill)
Skill verified by Jeanne Boyarsky

samuel marin

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I´m trying to do in JavaScript. Just don´t know if it is possible or how to do it.

I´m actually don´t know how to do it in PHP either.

thank you

Bear Bibeault

Sheriff

Posts: 67636

173

I like...

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Well, is that supposed to be PHP or JavaScript in your page at line 13?

What google API is that that you are using? [Edit: found here]

[Asking smart questions] [About Bear] [Books by Bear]

samuel marin

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I tried in JavaScript.

It is the JavaScript Google API the one I'm using

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

Thank you very much again

Bear Bibeault

Sheriff

Posts: 67636

173

I like...

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

It's doubtful many people are familiar with that particular API, but we can help you debug it with the usual Javascript debugging techniques. But we need to establish a solid base upon which to being.

Post the code you are trying, without any unnecessary PHP (which just confused the issue), and the exact problems you are having with it. At what point is it not doing what you expect?

[Asking smart questions] [About Bear] [Books by Bear]

samuel marin

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Please find below the Code that I'm using. The body part where the Selector is written works. I can see the selector with Texas on it.

The query works but the part where the info form the query (which is the State of Alabama) should be added to the Selector, that's what is not working.

Thank you very much again

Link to the Google Sheet

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
    google.load("visualization", "1", {packages:["corechart"]});

// To load this function when the window loads 
window.onload = function loadStates() {

//I select only one value (In this case Alabama, to test if it works)
    var queryStringStates = encodeURIComponent('SELECT A LIMIT 1');
    var magicIncantation = '/gviz/tq?gid=0&headers=1&tq=';

//Put the query together
    var queryStates = new google.visualization.Query(
        'https://docs.google.com/spreadsheets/d/1xfb9trifQA5KDPc9Nh5hBL4MJ290Mxcc1Uod2VTPzYI' +
        magicIncantation + queryStringStates);

//Get the data
    var dataStates = response.getDataTable();

//Load the data (In this case the state of Alabama) to the Selector which is in the body part with the  
//State of Texas by default to make sure that the Selector loads
    var x = document.getElementById("mySelect");
    var option = document.createElement("option");
    option.text = dataStates;
    x.add(option);
}
</script>
</head>

<body>
    <select id="mySelect">
        <option value="Texas">Texas</option>
    </select>   
  </body>
</html>

Bear Bibeault

Sheriff

Posts: 67636

173

I like...

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

My guess is that in line 25 you are setting a collection into a context where a scalar is expected and so it's only using the first value.

Just a SWAG.

Set a breakpoint at line 25 and see what dataStates contains.

Why aren't you using jQuery to vastly simplify all of this?

[Asking smart questions] [About Bear] [Books by Bear]

samuel marin

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,

I'm trying to debug through Google Chrome (F12) but not very sure how to run it and see the values. I'm seeing that there is and error in 21 see image attached.

I'm not using jQuery becuase I don't know how to do this in JQuery and also I want to integrate this in Google Site and I don't think I can use jQuery in Google Sites.

Thank you again

Debug.JPG

Bear Bibeault

Sheriff

Posts: 67636

173

I like...

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Well, yeah, a JavaScript error is going to stop everything in its tracks.

Where is response defined? What is it supposed to be? You're not being particularly forthcoming with information.

[Asking smart questions] [About Bear] [Books by Bear]

samuel marin

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Bear,

I tried in a different way to see if it worked. Now I'm able to debug the code and see the info stored in Data. However, I don't understand it and don't know how to use it.

Now I can see in the final result (also attached) that something tried to be added [object Object] but not the value that I tried to add (Value read from the Google Spreadsheet, which is ""Alabama)

Thank you very much again

New Code below as well as Image with the Debug and result of storage of Data attached.

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
    google.load("visualization", "1", {packages:["corechart"]});
    //google.setOnLoadCallback(drawSheetName);

window.onload = function LoaeElements() {

var queryString = encodeURIComponent('SELECT A LIMIT 1');
    var magicIncantation = '/gviz/tq?sheet=Selector&headers=1&tq=';

var query = new google.visualization.Query(
        'https://docs.google.com/spreadsheets/d/1c34jIRfIeuh-rzr8VS7rPw168nJkflvWNpLR2de3KZw' +
        magicIncantation + queryString);
    query.send(handleSampleDataQueryResponse);

}

function handleSampleDataQueryResponse(response) {
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
    }

//Establece opciones Column
       var optionsColumnChart = {
          height: 400,
          title: 'This is the title On Column Chart',
        };

var data = response.getDataTable();

var x = document.getElementById("mySelect");
    var option = document.createElement("option");
    option.text = data;
    x.add(option);

}

</script>

</head>
  <body>
        
    <select id="mySelect">
        <option value="Texas">Texas</option>
    </select>

</body>
</html>

Debug.JPG

FinalResult.JPG

samuel marin

Greenhorn

Posts: 24

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I think I found out a way to use jQuery in Google Sites, so if you could help me out on how to do what I'm trying to do in JQuery that would work as well.

THANK YOU SO MUCH!!!

Bear Bibeault

Sheriff

Posts: 67636

173

I like...

posted 6 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

That looks typical of a google internal structure. What does the documentation for the getTableData method have to say about what it returns? I suspect it's not a construct that you are supposed to use directly.

And you never answered my question about jQuery. Life is too short to do DOM manipulation by hand!

[Asking smart questions] [About Bear] [Books by Bear]

Quick! Before anybody notices! Cover it up with this tiny ad:

Free, earth friendly heat - from the CodeRanch trailboss

https://www.kickstarter.com/projects/paulwheaton/free-heat