Google Apps Script for querying Google trends

google_trends_query.js

/** LICENSE: BSD revised
 * Copyright (c) Lee Hanxue ([email protected]), All rights reserved.
 * Redistribution and use in source and binary forms, with or without modification, 
 * are permitted provided that the following conditions are met:
 * Redistributions of source code must retain the above copyright notice, 
 * this list of conditions and the following disclaimer. 
 * Redistributions in binary form must reproduce the above copyright notice, 
 * this list of conditions and the following disclaimer in the documentation and/or 
 * other materials provided with the distribution. Neither the name of the nor the 
 * names of its contributors may be used to endorse or promote products derived from 
 * this software without specific prior written permission. 
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 
 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
 * IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR 
 * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, 
 * EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * Author: Lee Hanxue
 * Version: 0.1 
 * Date: 19-December-2012
 */

var sheet; // current spreadsheet
var q;
var cat;
var geo;
var gprop;
var cmpt;
var date;

/** This function read input from the spreadsheet and sanitize it, 
  * then call up queryGoogleTrends to perform actual query
  * finally display the actual result
  */
function startQuery() {
  sheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // start the query
  var result = buildQueryString(sheet.getRangeByName("q").getValue(),
    sheet.getRangeByName("cat").getValue(),
    sheet.getRangeByName("geo").getValue(),
    sheet.getRangeByName("gprop").getValue(),
    sheet.getRangeByName("cmpt").getValue(),
    sheet.getRangeByName("date").getValue()
                    );
  
  // display the resulting link in a cell
  sheet.getRangeByName("Query_Result").setValue(result).setBackground("yellow");
  
  var csv_result = generateCsvDownload(sheet.getRangeByName("q").getValue(),
    sheet.getRangeByName("cat").getValue(),
    sheet.getRangeByName("geo").getValue(),
    sheet.getRangeByName("gprop").getValue(),
    sheet.getRangeByName("cmpt").getValue(),
    sheet.getRangeByName("date").getValue()
                    );
  
  sheet.getRangeByName("CSV_Download_Link").setValue(csv_result).setBackground("yellow");
  


}

function buildQueryString(q, cat, geo, gprop, cmpt, date) {
  return "http://www.google.com/trends/explore#" +
  "q=" + encodeURIComponent(q) + // search string
  "&cat=" + encodeURIComponent(cat) + // category
  "&geo=" + encodeURIComponent(geo) + // Geographical region
  "&gprop=" + encodeURIComponent(gprop) + // Web / Images / News / Froogle
  "&cmpt=" + encodeURIComponent(cmpt) + // other comparison
  "&date=" + encodeURIComponent(date); // date range
}

/**
 * This function generates the output of Google Trends in CSV format.
 * Essentially the same as buildQueryString, with 2 differences:
 * 1. the additional parameter graph=all_csv 
 * 2. Starts with http://www.google.com/trends/viz#"
 * returns a CSV file instead of HTML output
 */
function generateCsvDownload(q, cat, geo, gprop, cmpt, date) {
  return "http://www.google.com/trends/viz?" +
  "q=" + encodeURIComponent(q) + // search string
  "&cat=" + encodeURIComponent(cat) + // category
  "&geo=" + encodeURIComponent(geo) + // Geographical region
  "&gprop=" + encodeURIComponent(gprop) + // Web / Images / News / Froogle
  "&cmpt=" + encodeURIComponent(cmpt) + // other comparison
  "&date=" + encodeURIComponent(date) + // date range
  "&graph=all_csv";
}


/**
 * Returns a widget formatted with the text.
 */
function formatTextPanel(a, text) {
  var app = a ;
  var panel = app.createVerticalPanel();
  
  var lines = text.split("\n");
  
  for (var i=0; i<lines.length; i++) {
    var cleaned = removeLeadingWhiteSpace(lines[i]);
    var label = app.createLabel(cleaned[1]);
    if (cleaned[1].length == 0) {
      label.setText("-");
      label.setStyleAttribute("visibility", "hidden");
    }
    if (cleaned[0] > 0) {
      var margin = cleaned[0] * 6; // 6 px per char
      label.setStyleAttribute("margin", "0px 0px 0px "+margin+"px");
    }
    panel.add(label);
  }
  return panel;
}

/** This function is meant show the result in a panel
* TODO: Incomplete because Google Apps Script does not allow the rendering of arbitrary HTML content
* function displayResultInPanel(result) {
  * var app = UiApp.createApplication().setTitle("Query Result");
    * var link = app.createAnchor('Click here for Google Trends result ', result).setId("link");
    * app.add(link);
    * sheet.show(app);
    
    * app.add(outputPanel.add(HtmlService.createHtmlOutput(result.getContentText())));
    * app.add(outputPanel.add(app.createLabel(result.getContentText())));
    *
    *sheet.show(HtmlService.createHtmlOutput(result.getContentText()).getContent());
    *var template = HtmlService.createHtmlOutput(result.getContentText())
    * }
  */

/**
 * Remove whitespaces from start and report how many.
 */
function removeLeadingWhiteSpace(text) {
  var i = 0;
  var res = [];
  while (i < text.length && text[i] == ' ') {
    i = i+1;
  }
  res[0] = i;
  res[1] = text.substr(i);
  return res;
}

function onOpen() {  
  sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [    
    {name: "Query Google Trends", functionName: "startQuery"},
  ];  
  sheet.addMenu("Google Trends (Click here)", menu);
  sheet.toast("Click the Google Trends Menu to continue..");
}