Using JavaScript to get information from BigQuery

Question

I am new to JavaScript and Google BigQuery, so please forgive my ignorance. I am trying to write a javascript to collect data from one of the public databases on BigQuery. I found an answer to this at Obtaining BigQuery data from JavaScript code (the code for which I have pasted below) but when I saved the file as .html, replaced the client id and project number with mine, and tried to run it, I get the Authorize button and the page title. I click the Authorize button, and it disappears, but no query is run. Is there something else I was supposed to replace or is there something else I need to make this work? I saved the file as a .html, perhaps I should have saved it with a different extension?

I tried all three ways of creating a client id in the Google developers console and all gave me the same behavior.

I'm sure its just something silly that I am forgetting, but any advice would be greatly appreciated.

Here is the code given by Ryan Boyd, which I am unable to get working properly(which is surely my fault):

<html>
<head>
<script src="https://apis.google.com/js/client.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load('visualization', '1', {packages: ['geochart']});
</script>
<script>
  // UPDATE TO USE YOUR PROJECT ID AND CLIENT ID
  var project_id = '605902584318';
  var client_id = '605902584318.apps.googleusercontent.com';

  var config = {
    'client_id': client_id,
    'scope': 'https://www.googleapis.com/auth/bigquery'
  };

  function runQuery() {
   var request = gapi.client.bigquery.jobs.query({
      'projectId': project_id,
      'timeoutMs': '30000',
      'query': 'SELECT state, AVG(mother_age) AS theav FROM [publicdata:samples.natality] WHERE year=2000 AND ever_born=1 GROUP BY state ORDER BY theav DESC;'
    });
    request.execute(function(response) {     
        console.log(response);
        var stateValues = [["State", "Age"]];
        $.each(response.result.rows, function(i, item) {
          var state = item.f[0].v;
          var age = parseFloat(item.f[1].v);
          var stateValue = [state, age];
          stateValues.push(stateValue);
        });  
        var data = google.visualization.arrayToDataTable(stateValues);
        var geochart = new google.visualization.GeoChart(
        document.getElementById('map'));
        geochart.draw(data, {width: 556, height: 347, resolution: "provinces", region: "US"});
    });
  }

  function auth() {
    gapi.auth.authorize(config, function() {
        gapi.client.load('bigquery', 'v2', runQuery);
        $('#client_initiated').html('BigQuery client initiated');
    });
    $('#auth_button').hide();
  }
</script>
</head>

<body>
<h2>Average Mother Age at First Birth in 2000</h2>
<button id="auth_button" onclick="auth();">Authorize</button>
<button id="query_button" style="display:none;" onclick="runQuery();">Run Query</button>
<div id="map"></div>
</body>
</html>

Update: I opened the Developer Tools in Chrome and found this error in the console:

Failed to execute 'postMessage' on 'DOMWindow': The target origin provided ('file://') does not match the recipient window's origin ('null')

.

I tried editing in my Google Developer console as per these instructions: Google API in Javascript

Still same error.

Are you running on `file://` you will properly need to run it on ether `localhost` or an external WebServer. Not just from your file system. — Andreas Louv, Sep 22 '14 at 16:38
Thanks!! That helped! I also need to change my scope, and my google developers URLs. — Mandi, Sep 22 '14 at 19:28

score 1 · Answer 1 · answered Sep 22 '14 at 16:08

1

Looks like you may need to add:

$('#query_button').show();

to the bottom of the auth() function

like so:

function auth() {
    gapi.auth.authorize(config, function()
     {
        gapi.client.load('bigquery', 'v2', runQuery);
        $('#client_initiated').html('BigQuery client initiated');
     });
    $('#auth_button').hide();
    $('#query_button').show();
}

answered Sep 22 '14 at 16:08

stephenkelzer

1,234
2
12
20

You could also instead just run the query by placing `runQuery();` where I inserted that new line in your `auth()` function. This would instead just run the query instead of showing the button to you that you can then click to run the query. – stephenkelzer Sep 22 '14 at 16:10
Thanks Steve, this really just made a Run Query button appear, but didn't fix the underlying problem. Unfortunately, I think I didn't give enough detail on my problem before you answered, but I very much appreciate you taking the time to answer me!! – Mandi Sep 22 '14 at 18:06
ha! Not a problem, we're here to help! At least you now know how to get a button to appear using jQuery. – stephenkelzer Sep 22 '14 at 18:25

score 1 · Answer 2 · edited May 23 '17 at 12:12

Searching for the error you got, I found this page:

Google API in Javascript

Based on the error you're receiving, my guess is that you either do not have your Javascript Origin configured properly on the Google API console you got your Client ID from, and/or you are trying to run your script from the file system instead of through a web server, even one running on localhost. The Google API client, near as I've been able to tell, does not accept authorization requests from the file system or any domain that has not been configured to request authorization under the supplied Client ID. --@citizenslave

Thanks!! I needed this plus a few more things to get it to work. — Mandi, Sep 22 '14 at 18:05

score 1 · Accepted Answer · answered Sep 22 '14 at 18:04

It turns out it was a combination of things. I had the Javascript origin not configured properly, I didn't have all the scopes needed for my query, and I couldn't just open the html file in a browser, I needed to create an HTTP server.

So I changed the code to be:

<html>
<head>
  <script src="https://apis.google.com/js/client.js"></script>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
  <script type="text/javascript">
  google.load('visualization', '1', {packages: ['geochart']});
</script>
  <script>
  // UPDATE TO USE YOUR PROJECT ID AND CLIENT ID
var project_id = 'XXXXXXXXXXXX';
var client_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.apps.googleusercontent.com';

var config = {
  'client_id': client_id,
  'scope': 'https://www.googleapis.com/auth/plus.login https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/bigquery'
};

function runQuery() {
  var request = gapi.client.bigquery.jobs.query({
    'projectId': project_id,
    'timeoutMs': '30000',
    'query': 'SELECT state, AVG(mother_age) AS theav FROM [publicdata:samples.natality] WHERE year=2000 AND ever_born=1 GROUP BY state ORDER BY theav DESC;'
  });
  request.execute(function(response) {     
    console.log(response);
    var stateValues = [["State", "Age"]];
    $.each(response.result.rows, function(i, item) {
      var state = item.f[0].v;
      var age = parseFloat(item.f[1].v);
      var stateValue = [state, age];
      stateValues.push(stateValue);
    });  
    var data = google.visualization.arrayToDataTable(stateValues);
    var geochart = new google.visualization.GeoChart(
      document.getElementById('map'));
    geochart.draw(data, {width: 556, height: 347, resolution: "provinces", region: "US"});
  });
}

function auth() {
  gapi.auth.authorize(config, function() {
    gapi.client.load('bigquery', 'v2', runQuery);
    $('#client_initiated').html('BigQuery client initiated');
  });
  $('#auth_button').hide();
}
</script>
  </head>

  <body>
  <h2>Average Mother Age at First Birth in 2000</h2>
  <button id="auth_button" onclick="auth();">Authorize</button>
  <button id="query_button" style="display:none;" onclick="runQuery();">Run Query</button>
  <div id="map"></div>
  </body>
  </html>

I fixed my Google Javascript Origins url to be http://localhost:8888/ and my Redirect uri to be http://localhost:8888/oauth2callback, opened a command prompt to run this command from the directory of my html file:

python -m SimpleHTTPServer 8888

and then went to localhost:8888 in my browser and clicked my html file there.

Thanks so much for all the feedback!

It worked perfectly! Now to change the query for my purposes!

Using JavaScript to get information from BigQuery

3 Answers3