Google Docs Help: Collaboration In Blogger Using Google Docs Tutorial Part I

That's the article submission feature. It's that simple. Create a spreadsheet, create a form, tailor the html of the form to your liking and insert it into your web page, sit back and wait for the data to roll in!

Displaying Aritcles

All that's left is to display the submitted articles in an organized fashion on the blog page. This is where the Google Visualization API comes in. The Google visualization API was announced in March of this year. JavaScript can utilize the API to extract data from a Google Docs Spreadsheet. You might be wondering why we couldn't use JavaScript to submit articles as well. So far, the visualization API is for just that, visualization. There are no facilities in the API to write into or modify the contents of a spreadsheet, only read facilities are provided.

You can see the JavaScript source code in its entirety below. The source code is based on the Hello World app provided by Google for the visualization API. It's structured into three sections:

1. The initialization code. This is the code that runs when the blog page is loaded.
2. The proxy functions that handle callbacks from the Google visualization API queries.
3. The function that performs the HTML rendering of the returned query data into the blog page's article divs.

Initialization

The initialization code specifies the location of the APIs provided by Google, loads the visualization API, and calls

google.setOnLoadCallback(hr5642_initialize);

passing it the name of the function that will be executed immediately after the Google API is loaded. In this case, that function is hr5642_initialize. So, application startup looks like:

hr5642_initialize calls three functions. Each function is responsible for querying the underlying Google Docs spreadsheet and using the returned data to populate its associated article list, (div), on the blog page. The three functions are:

• hr5642_initialize_News()
• hr5642_initialize_OtherBlog()
• hr5642_initialize_ThisBlog()

Querying and the Proxy Functions

The three functions above are responsible for submitting the queries to the spreadsheet that will return the specified articles for each div. They are virtually identical and differ only in the article search qualifier, ('ThisBlog' in the code snippet shown below), and the proxy function that they pass as a callback to the Google API, ('handleThisBlogQueryResponseProxy' in the code snippet shown below). The code for hr5642_initialize_ThisBlog is:

function hr5642_initialize_ThisBlog() {

var query = new google.visualization.Query(stQuery);

query.setQuery("select B, C, sum(E) where D ='ThisBlog' group by B, C");

query.send(hr5642_handleThisBlogQueryResponseProxy);

}

The code first creates a new query object from Google API library. This object will be used to hold the query string that specifies what information the application wants to retrieve from the underlying spreadsheet. If you're familiar with SQL, the Google Visualization API query language will be very familiar. For more documentation on the query language check out Google's Query Language Reference. The query string:

select B, C, sum(E) where D ='ThisBlog' group by B, C

asks the API to return a set of rows that contain B, the stored article's hyperlink, 'Link', and C, the stored article's title, 'Title'. The 'group by' qualifier tells the query to only return unique combinations of hyperlink and title, eliminating duplicates that might have been entered by someone clicking on the article submission button twice. The final query term, 'sum(E)', is used by the voting update feature. That will be covered in future installment of the tutorial. A snippet of the spreadsheet that the query string queries can be seen below:

As you can see, the query language utilizes the column headings of the spreadsheet, not the field names inserted when the spreadsheet input form is created.

The last line of code calls the send function of the query object. The only argument of the send function is the proxy callback function that Google will call when the query results are ready. The code execution here looks like:

The proxy functions are all identical, there only purpose is to call the method that renders the html in each div, passing along the query response from the Google API and an argument '2' that specifies which div is to be rendered. For example:

function hr5642_handleThisBlogQueryResponseProxy(response){

hr5642_handleQueryResponse(response, 2);

}

The single argument to the proxy function is required by the Google API and holds the data returned by the API from the spreadsheet in response to the JavaScript query.

Rendering the HTML

In the interest of time and space, the voting and search features will be discussed in another installment of the tutorial. The code for them is intentionally not described here, although it is shown in the full listing below. However, these omissions shouldn't obscure the explanations provided. If they do, please leave a comment, and I'll try to clear it up.

At a Glance: The HTML rendering code is very similar to the "Hello World" App from Google. It uses a simple for loop to walk through the returned query results and write an HTML table row entry for each result.

In Detail: Immediately upon entering the function, we call the Google API to get a data table object from the returned query results:

var data = response.getDataTable();

Next, the switch block decides what div the resulting HTML should be written into based on the div_to_use input argument to the rendering function, (hr5642_handleQueryResponse, in the code shown here).

The HTML is created for the table itself and the header row and pushed into an array of strings, called 'html'. Then the code enters the aforementioned for loop, (no pun intended). To retrieve data from the data table, the following API call is used:

data.getFormattedValue(row, 0)

row is the loop increment variable and specifies which row of data, (reader-submitted article), the code is operating on. The second argument specifies which column in the row to retrieve. Our query asked for B, C, and the sum of E:

select B, C, sum(E) where D ='ThisBlog' group by B, C

So, a column number of 0 returns B, the 'Link'. 1 returns C, the 'Title', and 2 returns sum(E), the count of votes.

Various HTML tags are pushed into the 'html' array along with the data to create the desired html table rows on the blog page, and the loop is repeated for the next row of query data until all the data returned for the query has been rendered. Finally, when the for loop is exited, the ending tag for the html table is added to the 'html' string array. The newly rendered HTML is then inserted into the appropriate div on the blog page by calling:

document.getElementById(stListID).innerHTML = html.join('');

Finally, The Source Code!

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
    google.load("visualization", "1");
    
 
    function hr5642_initialize() {
    hr5642_initialize_ThisBlog();
    hr5642_initialize_OtherBlog();
    hr5642_initialize_News();    
    }
    
 
    var stQuery = 'http://spreadsheets.google.com/tq?key=pvFXGB-79Kl2AewIPEIRKKw&gid=0&pub=1';
    var formaction = '<form target=\"thisblogform\" action=\"http://spreadsheets.google.com/formResponse?key=pvFXGB-79Kl2AewIPEIRKKw\" method=\"post\">';
    var stAction = '';
    var news_div = 0;
    var otherblog_div = 1;
    var thisblog_div = 2;
    
 
    function hr5642_initialize_ThisBlog() {      
      var query = new google.visualization.Query(stQuery);   
      query.setQuery("select B, C, sum(E) where D ='ThisBlog' group by B, C");     
      query.send(hr5642_handleThisBlogQueryResponseProxy);   
    }
    
 
    function hr5642_initialize_OtherBlog() {      
        var query = new google.visualization.Query(stQuery);  
        query.setQuery("select B, C, sum(E) where D ='OtherBlog' group by B, C");     
        query.send(hr5642_handleOtherBlogQueryResponseProxy);   
      }  
      
 
    function hr5642_initialize_News() {      
      var query = new google.visualization.Query(stQuery);     
      query.setQuery("select B, C, sum(E) where D ='News' group by B, C");     
      query.send(hr5642_handleNewsQueryResponseProxy);    
    }   
      
 
    google.setOnLoadCallback(hr5642_initialize); 
    
 
    function hr5642_handleNewsQueryResponseProxy(response){
      hr5642_handleQueryResponse(response, 0);
    }
 
 
    function hr5642_handleOtherBlogQueryResponseProxy(response){
      hr5642_handleQueryResponse(response, 1);
    }
 
 
    function hr5642_handleThisBlogQueryResponseProxy(response){
      hr5642_handleQueryResponse(response, 2);
    }
 
 
    function hr5642_handleQueryResponse(response, div_to_use) {     
      if (response.isError()) {     
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());    
        return;
      }   
      var data = response.getDataTable();     
      var html = [];
      var stVotesUpdateFunc = "";
      var stListID = "";
      switch(div_to_use){
        case 0:
          stVotesUpdateFunc = 'hr5642_initialize_News()';
          stListID = 'news';
          break;
        case 1:
          stVotesUpdateFunc = 'hr5642_initialize_OtherBlog()';
          stListID = 'otherblog';
          break;
        case 2:
          stVotesUpdateFunc = 'hr5642_initialize_ThisBlog()';
          stListID = 'thisblog';
          break;        
      }
      html.push('<table border=\"1\"><tr><th>Article</th><th><input value=\"Votes\" onclick=\"');
      html.push(stVotesUpdateFunc);
      html.push('\" type=\"button\"></th><th></th><th></th></tr>'); 
      for (var row = 0; row < data.getNumberOfRows(); row++){  
        var stName = data.getFormattedValue(row, 1);
        var srchIndex = 0;
        var srchLength = 0;
        switch(div_to_use){
          case 0:
            srchIndex = stName.indexOf(hr5642_getSrch('srchnews'));
            srchLength = hr5642_getSrch('srchnews').length;
            break;
          case 1:
            srchIndex = stName.indexOf(hr5642_getSrch('srchotherblog'));
            srchLength = hr5642_getSrch('srchotherblog').length;
            break;
          case 2:
            srchIndex = stName.indexOf(hr5642_getSrch('srchthisblog'));
            srchLength = hr5642_getSrch('srchthisblog').length;
            break;        
        }
        if((srchIndex != -1) || (srchLength == 0)){ 
          html.push('<tr><td><a href=\"');
          html.push(data.getFormattedValue(row, 0));   
          html.push('\">');     
          html.push(data.getFormattedValue(row, 1));  
          html.push('</a></td><td> ');
          html.push(data.getFormattedValue(row, 2));   
          html.push('</td><td>');
          html.push(formaction);         
          html.push('<input size=\"15\" name=\"single:0\" type=\"hidden\" value=\"');
          html.push(data.getFormattedValue(row, 0)); 
          html.push('\"><input size=\"35\" name=\"single:1\" type=\"hidden\" value=\"');
          html.push(data.getFormattedValue(row, 1));
          html.push('\"><input value=\"ThisBlog\" name=\"group:2\" type=\"hidden\">');
          html.push('<input type=\"hidden\" name=\"single:3\" value=\"-1\"><input value=\"\" class=\"downbutton\" type=\"submit\"></form></td><td>');
                      
 
                      
 
          html.push(formaction);
          html.push('<input size=\"15\" name=\"single:0\" type=\"hidden\" value=\"');
          html.push(data.getFormattedValue(row, 0)); 
          html.push('\"><input size=\"35\" name=\"single:1\" type=\"hidden\" value=\"');
          html.push(data.getFormattedValue(row, 1));
          html.push('\"><input value=\"ThisBlog\" name=\"group:2\" type=\"hidden\">');
          html.push('<input type=\"hidden\" name=\"single:3\" value=\"1\"><input value=\"\"  class=\"upbutton\" type=\"submit\">');
          html.push('</form>');
          html.push('</td>');      
          html.push('</tr>'); 

 
       }    
    }    
    html.push('</table>');   
    document.getElementById(stListID).innerHTML = html.join('');   
  }             

 
  function hr5642_getSrch(my_value){      
    var stSearch = document.getElementById(my_value).value;      
    return stSearch;    
  }        
      
 

 
          
 
</script>

Thanks!

If you've made it this far, you're a trooper! Thanks for the your time and interest. Please leave any feedback, ideas, or suggestions as comments below. And don't forget! Contact your representatives!