Searching via Spreadsheet Data API

Here is part of my code for inspiration (sorry for czech comments):

/**

 * Spreadsheet Data API library class

 * @copyright  Copyright (c) Ale¹ Holubec

 */

SpreadsheetDataAPILibrary = {

  SPREADSHEET_API_URL : 'https://spreadsheets.google.com/feeds',

  WORKSHEETS : '/worksheets',

  LIST : '/list',

  CELLS : '/cells',

  VISIBILITY : '/private',

  PROJECTION : '/full',

  key : '---SPREADSHEET_KEY---',

  

  getWorksheetsUri : function() {

    return this.SPREADSHEET_API_URL + this.WORKSHEETS + '/' + this.key + this.VISIBILITY + this.PROJECTION;

  },

  

  getListUri : function(worksheetId) {

    return this.SPREADSHEET_API_URL + this.LIST + '/' + this.key + '/' + worksheetId + this.VISIBILITY + this.PROJECTION;

  },

  getCellsUri : function(worksheetId) {

    return this.SPREADSHEET_API_URL + this.CELLS + '/' + this.key + '/' + worksheetId + this.VISIBILITY + this.PROJECTION;

  },

  // Vrati response object

  spreadsheetDataAPI_ : function(uri, method, query) {

    method = method || 'get';

    query = query || '';

    var url = uri;

    var fetchArgs = this.googleOAuth_('spreadsheet',this.SPREADSHEET_API_URL);

    fetchArgs.method = method;

    if( method.toLowerCase() == 'get' ) {

      url +=query;

      fetchArgs.payload = null;

    } else {

      fetchArgs.payload  = query;

      fetchArgs.contentType = 'application/x-www-form-urlencoded';

    }

    var resp = UrlFetchApp.fetch(url, fetchArgs);

    //Logger.log( 'UrlFetchApp response code : '+resp.getResponseCode()+' : '+url );

    return resp;

  },

  

  googleOAuth_ :function(name,scope) {

    var oAuthConfig = UrlFetchApp.addOAuthService(name);

    oAuthConfig.setRequestTokenUrl('https://www.google.com/accounts/OAuthGetRequestToken?scope='+scope);

    oAuthConfig.setAuthorizationUrl('https://www.google.com/accounts/OAuthAuthorizeToken');

    oAuthConfig.setAccessTokenUrl('https://www.google.com/accounts/OAuthGetAccessToken');

    oAuthConfig.setConsumerKey('anonymous');

    oAuthConfig.setConsumerSecret('anonymous');

    return {oAuthServiceName:name, oAuthUseToken:'always'};

  },

  // Najde worksheet id podle jmena a vrati napr. od6 pro Kontakty

  getWorksheetId : function(worksheetName) {

    //https://spreadsheets.google.com/feeds/worksheets/---SPREADSHEET_KEY---/private/full?title=Kontakty

    var worksheetUri = this.getWorksheetsUri();

    var resp = this.spreadsheetDataAPI_( worksheetUri, 'get', '?title='+encodeURIComponent(worksheetName) );

    if( resp.getResponseCode() != 200 )

      throw 'Unable to retrieve getWorksheetId ('+worksheetName+') : '+worksheetUri+'';

    var rootElement = Xml.parse(resp.getContentText()).getElement();

    var entry = rootElement.getElement('entry');

    var id = entry.getElement('id');

    // <id>https://spreadsheets.google.com/feeds/worksheets/---SPREADSHEET_KEY---/---WORKSHEET_ID---</id>

    var param = id.getText().split('/');

    var worksheetId = param[param.length-1];

    return worksheetId;

  },

  

  // Vrati pole objektu s radky obsahujicimi vsechny hodnoty ze vsech sloupcu nalezenymi podle query

  // Nazvy objektu jsou podle nazvu sloupcu ale bez mezer, diakritiky a NEJSOU to normalizovana slova oddelena velkymi pismeny!

  // Obsah query muze byt podle http://code.google.com/intl/cs-CZ/apis/spreadsheets/data/3.0/reference.html#ListParameters

  // napr: 

  //    '?sq='+encodeURIComponent('pøíjmení="Lastname"')

  //    '?sq='+encodeURIComponent('email="lastname.firstname@email.com"')

  //    '?sq='+encodeURIComponent('datumregistrace>2011-02-15')

  //    '?q='+encodeURIComponent('lastname.firstname@email.com')+'&orderby='+encodeURIComponent('idkontaktu')+'&reverse=true'

  // headers je optional, obsahuje objekt obsahujicimi vsechny nazvy sloupcu v normalizovane podobe 

  //                      indexovane podle nazvu sloupcu ale bez mezer, diakritiky a NEJSOU slova oddeleny velkymi pismeny

  getListEntries : function(worksheetId, query, headers) {

    headers = headers || this.getColumnsNames(worksheetId);

    Logger.log(headers);

    var listUri = this.getListUri(worksheetId);

    var resp = this.spreadsheetDataAPI_( listUri, 'get', query );

    if( resp.getResponseCode() != 200 )

      throw 'Unable to retrieve getListEntries ('+worksheetId+') : '+listUri+'';

    var rows = [];

    var rootElement = Xml.parse(resp.getContentText()).getElement();

    for each(var entry in rootElement.getElements('entry')) {

      var cols = {};

      for each(var elem in entry.getElements()) {

        // Najde pouze elementy z gsx namespace

        if( elem.getName().getNamespace() == 'http://schemas.google.com/spreadsheets/2006/extended' ) {

          Logger.log(  elem.getName().getLocalName() + ' : '+ elem.getText() );

          // Nazvy objektu jsou podle nazvu sloupcu ale bez mezer, diakritiky a NEJSOU to normalizovana slova oddelena velkymi pismeny!

          var apiHeaderName = UtilityLibrary.removeDiacritics(elem.getName().getLocalName().toLowerCase());

          var normalizedHeaderName = headers[apiHeaderName];

          cols[normalizedHeaderName] = elem.getText();

        }

      }

      rows.push(cols);

    }

    return rows;

  },

  

  // Vrati objekt obsahujicimi vsechny nazvy sloupcu v normalizovane podobe

  // indexovane podle nazvu sloupcu ale bez mezer, diakritiky a NEJSOU slova oddeleny velkymi pismeny

  getColumnsNames : function(worksheetId) {

// https://spreadsheets.google.com/feeds/cells/---SPREADSHEET_KEY---/---WORKSHEET_ID---/private/full?max-row=1

var headers = {prijmeni:'prijmeni', mesto:'mesto', ulice:'ulice', telefon:'telefon', zeme:'zeme', jmeno:'jmeno', password:'password', datumregistrace:'datumRegistrace',timestamp:'timestamp', psc:'psc', email:'email', datumnarozeni:'datumNarozeni', titulza:'titulZa', idkontaktu:'idKontaktu', titulpred:'titulPred'};

return headers;

var cellsUri = this.getCellsUri(worksheetId);

    var query = '?max-row=1'; // chci pouze prvni radek s nazvy sloupcu v elementu content

    var resp = this.spreadsheetDataAPI_( cellsUri, 'get', query );

    if( resp.getResponseCode() != 200 )

      throw 'Unable to retrieve getColumnsNames ('+worksheetId+') : '+cellsUri+'';

    var headers = {};

    var rootElement = Xml.parse(resp.getContentText()).getElement();

    for each(var entry in rootElement.getElements('entry')) {

      var content = entry.getElement('content').getText();

      var normalizedHeaderName = SpreadsheetLibrary.normalizeHeader(content);

      var apiHeaderName = normalizedHeaderName.toLowerCase();

      headers[apiHeaderName] = normalizedHeaderName;

    }

    return headers;

  },

    

 // Normalizes a string, by removing all alphanumeric characters and using mixed case to separate words and remove diacritics

  normalizeHeader : function(header) {

    ...

  },

};