Can I use Google Visualization API to query a spreadsheet in apps script?

Question

I have a Google Spreadsheet with about 3000 rows of data (and about 30 columns). The data is laid out in standard fashion with column headers and one row per data item. The data represent case studies and articles used in various academic courses, and the dataset is always growing.

I need to search/query the data based on specified parameters. For example, I'd like to run this query : select * where course_no = 123 and instructor = Smith and year = 2012. I'd like to get a range of data as the result, which I can then either simply display as raw data on a new Sheet, or manipulate into some kind of report using GAS, or perhaps just email raw data to a recipient.

It looks like the Google Visualization API offers nice query options (I'm looking at the setQuery method of the google.visualization.Query class), but is it possible to use this without attaching it to a Chart or visualization? Can I just call it in script and then manage the range it returns myself? When I try this, I get an error "ReferenceError: google is not defined" at this line :

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

I'm also not sure that using the spreadsheet url as the data_source_url in the line of code above is the right thing to do, though that's what I'm trying.

I'm trying this because I'm assuming that using this sort of query approach is more efficient than searching through the cells one by one (like is suggested here How do I search Google Spreadsheets?) but I appreciate all feedback. Thank you! Carrie

Answer 1

Unfortunately it's not currently possible to directly load Google Visualisation API in a Google Apps Script project. You do have a couple of options though:

1. Use the noSQL storage build into Google Apps Script (ScriptDb) to read the spreadsheet into a database before querying the response.

2. Forget querying the data in Apps Script and use the QUERY formula in a sheet (this uses the same syntax as the Google Visualization API Query Language

3. If using old Sheets you can File > Publish to the web and then query the data in your script using the URL Fetch Service and calling the data by Setting the Query in the Data Source URL <- the problem with this way is you've potentially got a lot of work to process the returned data which can be html, csv or json. This simple tool helps you build the datasource url.

Personally, depending on the scenario, I'd try #2 but failing that go with #1

Answer 2

I've managed to use UrlFetchApp to get the data.

• The file must be shared publicly ("everyone with a link can view" is enough)

Usage

function test_getSheetsQueryResult()
{
  var fileId = '1WO3PEycHGtfG-yd4V-B6EfKkVYMC73EqDBPqgAqcz3k';
  var sheetName = 'Data';
  var rangeA1 = 'A1:H11';
  var sqlText = "select A, C, D, F, 'google' where E > 0";

  var res = getSheetsQueryResult_(fileId, sheetName, rangeA1, sqlText);
  Logger.log(res);      

}

Code:

/*   
  Types:

    Get             Return
    number    =>    number
    string    =>    string
    date      =>    string
    datetime  =>    string
    boolean   =>    boolean

  Note: 

    The function returns strings for dates because of 2 resons:
      1. The string is automatically converted into a date when pasted into the sheet
      2. There are multiple issues with dates (like different time zones) that could modify returned values
*/
function getSheetsQueryResult_(fileId, sheetName, rangeA1, sqlText)
{

  var file = SpreadsheetApp.openById(fileId);
  var sheetId = file.getSheetByName(sheetName).getSheetId();

  var request = 'https://docs.google.com/spreadsheets/d/' + fileId + '/gviz/tq?gid=' + sheetId + '&range=' + rangeA1 + '&tq=' + encodeURIComponent(sqlText);
  var result = UrlFetchApp.fetch(request).getContentText();     
  // get json object
  var from = result.indexOf("{");
  var to   = result.lastIndexOf("}")+1;  
  var jsonText = result.slice(from, to);  
  var parsedText = JSON.parse(jsonText);      

  // get types
  var types = [];
  var addType_ = function(col) { types.push(col.type); }
  var cols = parsedText.table.cols;
  cols.forEach(addType_);    

  // loop rows
  var rows = parsedText.table.rows;  
  var result = [];  
  var rowQuery = [];
  var eltQuery = {};
  var row = [];
  var nRows = rows[0].c.length;
  var type = '';
  for (var i = 0, l = rows.length; i < l; i++)
  {
    rowQuery = rows[i].c;
    row = [];
    // loop values   
    for (var k = 0; k < nRows; k++)
    {
      eltQuery = rowQuery[k];
      type = types[k];
      if (type === 'number') { row.push(parseInt(eltQuery.v)); }
      if (type === 'boolean' || type === 'string') { row.push(eltQuery.v); }
      else { row.push(eltQuery.f); }      
    }    
    result.push(row);
  }

  return result;

}