Google Docs Script - API

gistfile1.txt

/**
 * @This file provides Google Apps Script CRUD wrappers for Google Fusion Tables 
 * <ul>
 * <li>It is intended to be used as an example for using Google Fusion Tables within Googe Apps Scripts
 * <li>It is dependent on the Fusion Tables API and Google OAuth
 * </ul>
 * @author fargyle@google.com (Ferris Argyle)
 */

/**
 * The URL for the Fusion Tables API
 * @type {string}
 */
var  TMPFUSIONURL = 'https://www.google.com/fusiontables/api/query'; //Note that now need HTTPS to access fusion tables; in addition, service name has changed to fusiontables (currently fusion; can use both)

/**
 * Global object for all static variables
 * @type {Object.<string,Object>}
 * @const
 */

var   STATICVARS = { 
    fusionUrl : TMPFUSIONURL,  
    fetchArgs : googleOAuth_('fusiontables',TMPFUSIONURL)
  };

/**
 * Test harness to demonstrate function calls
 * <ul>
 * <li>The doGet() designates this as a Sites example, but this functionality can be used within Spreadsheets as well
 */
function doGet() {
  
  Logger.log('Version 1.1');
  
  var app = UiApp.createApplication();
  var tableId = '123456'; //change this to your table ID
  var returnMsg = "";

  //Read records
  var tableArray = readRecords_(tableId,'*','', '*');
  for (var i = 0; i < tableArray.length; i++) {
    Logger.log(tableArray[i]);
    var label = app.createLabel().setText(tableArray[i].keyColumn + ',' + tableArray[i].dataColumn1 + ',' + tableArray[i].dataColumn2);
    app.add(label);
  }

  returnMsg = createRecord_(tableId, 'keyColumn', 'keyValue',    'keyColumn, dataColumn1', '\'keyValue\', \'dataValue1\'');
  
  returnMsg = updateRecord_(tableId, 'keyColumn', 'keyValue',   'dataColumn1,dataColumn2', '\'dataValue1\', \'dataValue2\'');
  
  returnMsg = deleteRecord_(tableId, 'keyColumn', 'keyValue');

  return app;

}


/**
 * Create a record if the record doesn't exist
 * @param {string} tableId The Id of the Fusion Table in which the record will be created
 * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists
 * @param {string} whereValue The value to search for in the Fusion Table selectColumn
 * @param {string} insertColumns The Fusion Table columns into which values will be inserted
 * @param {string} insertValues The values to insert into the Fusion Table insertColumns
 * @return {string} The bubbled return code from the Fusion query API, or a hardcoded error message if the record already exists
 */
function createRecord_(tableId, whereColumn, whereValue, insertColumns, insertValues ) {

  var returnVal = false;
  var foundRecords = readRecords_(tableId, 'ROWId', whereColumn, whereValue);
  
  if (typeof foundRecords == 'string' && foundRecords.search('>> Error')>-1) {
    returnVal = foundRecords.search;
  } else if (foundRecords) {
    returnVal = '**Error: already in table';
  } else {
    query = 'INSERT INTO '+tableId+' ('+insertColumns+') VALUES ('+insertValues+')';
    returnVal = fusion_('post',query);  
  } 

  return returnVal;

}

/**
 * Read records
 * @param {string} tableId The Id of the Fusion Table in which the record will be created
 * @param {string} selectColumn The Fusion table columns which will returned by the read
 * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists
 * @param {string} whereValue The value to search for in the Fusion Table selectColumn; can be '*'
 * @return {string} An array containing the read records if no error; the bubbled return code from the Fusion query API if error
 */
function readRecords_(tableId, selectColumn, whereColumn, whereValue) {
  
  var query = '';
  var foundRecords = [];
  var returnVal = false;
  var tableList = [];
  var row = [];
  var columns = [];
  var rowObj = new Object();
  
  if (whereValue == '*') {
    var query = 'SELECT '+selectColumn+' FROM '+tableId;
  } else {
    var query = 'SELECT '+selectColumn+' FROM '+tableId+' WHERE '+whereColumn+' = \''+whereValue+'\'';
  }
  
  var foundRecords = fusion_('get',query);

  if (typeof foundRecords == 'string' && foundRecords.search('>> Error')>-1) {
    returnVal = foundRecords.search;
  } else if (foundRecords.length > 1 ) {
    //first row is header, so use this to define columns array
    row = foundRecords[0];
    columns = [];
    for (var k = 0; k < row.length; k++) {
      columns[k] = row[k];
    }
    
    for (var i = 1; i < foundRecords.length; i++) {
      row = foundRecords[i];
      if( row.length > 0 ) {    
        //construct object with the row fields
        rowObj = {};
        for (var k = 0; k < row.length; k++) {
          rowObj[columns[k]] = row[k];
        }
        tableList[i-1] = rowObj;  //start new array at zero to conform with javascript conventions
      }
    }
    returnVal = tableList;
  }

  
  return returnVal;
  
}

/**
 * Update record
 * <ul>
 * <li>Create a new record if it doesn't exist
 * <li>Remove duplicates if they exist
 * </ul>
 * @param {string} tableId The Id of the Fusion Table in which the record will be updated
 * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists
 * @param {string} whereValue The value to search for in the Fusion Table selectColumn
 * @param {string} updateColumns The Fusion Table columns which will be updated
 * @param {string} updateValues The values to update in the Fusion Table insertColumns
 * @return {string} The bubbled return code from readRecords() or the Fusion query API 
 */
function updateRecord_(tableId, whereColumn, whereValue, updateColumns, updateValues) {
  
  var updateColumnsArray = [];  
  var updateValuesArray = [];
  var updatePairs = '';
  var query = '';
  var foundRecords = [];
  var returnVal = false;
  var row = [];

  updateColumnsArray = updateColumns.split(',');
  updateValuesArray = updateValues.split(',');
  
  for (var i = 0; i < updateColumnsArray.length; i++) {
    if (i > 0) {
      updatePairs = updatePairs + ', ';
    }
    updatePairs = updatePairs + updateColumnsArray[i]+'='+updateValuesArray[i];
  }

  foundRecords = readRecords_(tableId, 'ROWId', whereColumn, whereValue);
  if (typeof foundRecords == 'string' && foundRecords.search('>> Error')>-1) {
    returnVal = foundRecords.search;
  }
  else if (foundRecords) {
    for (var i = 0; i < foundRecords.length; i++) {
      //there should only be one occurrence of the Id, but just in case...
      row = foundRecords[i];
      if (i==0) {
        query = 'UPDATE '+tableId+' SET '+updatePairs+' WHERE ROWId = \''+row.rowid+'\'';
      } else {
        //delete duplicate records
        query = 'DELETE FROM '+tableId+' WHERE ROWId = \''+row.rowid+'\'';
      }
      if (i>0) {
        returnVal = returnVal + ', ';
      }
      returnVal = returnVal + fusion_('post',query);  
    }
  }
  else {
    returnVal = createRecord_(tableId, whereColumn, whereValue, updateColumns, updateValues);
  }
  
  return returnVal;

}

/**
 * Delete record
 * <ul>
 * <li>Remove duplicates if they exist
 * </ul>
 * @param {string} tableId The Id of the Fusion Table in which the record will be updated
 * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists
 * @param {string} whereValue The value to search for in the Fusion Table selectColumn
 * @return {string} The bubbled return code from readRecords() or the Fusion query API, or a hardcoded error message if the record doesn't exist
 */
function deleteRecord_(tableId, whereColumn, whereValue) {
  
  var query = '';
  var foundRecords = [];
  var returnVal = false;
  var row = [];
  var returnVal = false;
  
  foundRecords = readRecords_(tableId, 'ROWId', whereColumn, whereValue);
  if (typeof foundRecords == 'string' && foundRecords.search('>> Error')>-1) {
    returnVal = foundRecords.search;
  }
  else if (foundRecords) {
    for (var i = 0; i < foundRecords.length; i++) {
      //there should only be one occurrence of the Id, but just in case...
      row = foundRecords[i];
      // there's only one element in the row
      query = 'DELETE FROM '+tableId+' WHERE ROWId = \''+row.rowid+'\'';
      if (i>0) {
        returnVal = returnVal + ', ';
      }
      returnVal = returnVal + fusion_('post',query);
    }
  } else {
    returnVal = 'not found';
  }
  
  
  return returnVal;
    }
  
/**
 * Execute get/post against Fusion Tables
 * @param {string} method The type of HTTP method: should be either 'get' (read) or 'post' (create, update, delete)
 * @param {string} sql The query to execute
 * @return {string} An array containing the records for a read, or the bubbled return code from the Fusion query API for other operations or in case of a read error
 */
function fusion_(method,sql) {
  var url = STATICVARS.fusionUrl;
  
  STATICVARS.fetchArgs.method = method;
  if( method == 'get' ) {
    url += '?sql='+sql; 
//    url += '?sql='+encodeURIComponent(sql);  // encoding may be required in future
    STATICVARS.fetchArgs.payload = null;
  } else
    STATICVARS.fetchArgs.payload = 'sql='+sql;
  
  return CSV2Matrix_( UrlFetchApp.fetch(url, STATICVARS.fetchArgs).getContentText() );

}


/**
 * Authorize the script against Fusion Tables
 * @param {string} name The Fusion Tables service name; this has changed to fusiontables (previously fusion; can use both)
 * @param {string} scope The Fusion Tables Query API URL, currently 'https://www.google.com/fusiontables/api/query'; note that now need HTTPS to access fusion tables;
 * @return {object} service name and authorization token
 */
function googleOAuth_(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'};
}
  

/**
 * Parse CSV return values into array of objects
 * <ul>
 * <li>copied and adapted from here:
 * <li>http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-Javascript-Exec-Regular-Expression-Command.htm
 * </ul>
 * @param {string} strData The string data to parse into an array
 * @param {string} strDelimiter The string delimiter
 * @return {string} An array of objects containing the parsed values
 */
function CSV2Matrix_( strData, strDelimiter ) {
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" + //delimiters
    "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + //quoted fields
    "([^\"\\" + strDelimiter + "\\r\\n]*))" //standard fields
    ,"gi");
  var arrData = [[]]; //array of resulting data
  var arrMatches = null; //array to hold the matches
 
  while( arrMatches = objPattern.exec( strData ) ) { //loop through matches 
    var strMatchedDelimiter = arrMatches[ 1 ];
    if( strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter) ) //new line
      arrData.push([]);
    if( arrMatches[2] ) //quoted or unquoted?
      var strMatchedValue = arrMatches[2].replace(/"\"\""/g,"\""); //unescape double quotes
    else //non-quoted
      var strMatchedValue = arrMatches[3];
    arrData[arrData.length-1].push( strMatchedValue );
  }
  if( arrData[arrData.length-1].length == 0 || arrData[arrData.length-1][0].length == 0 )
    arrData.pop();
  return arrData;
}