Google Forms to Fusion Table with New API -
i set google form input data fusion table below tutorial.
http://kh-samples.googlecode.com/svn/trunk/code/instructions.html
now old fusion tables api shut down, i'm getting below error message through google forms script editor:
returned code 403. server response: { "error": { "errors": [ { "domain": "usagelimits", "reason": "dailylimitexceededunreg", "message": "daily limit unauthenticated use exceeded. continued use requires signup.", "extendedhelp": "https://code.google.com/apis/console" } ], "code": 403, "message": "daily limit unauthenticated use exceeded. continued use requires signup." } } i've turned on fusion tables service through api console i'm not sure how link api key in apps script code.
/** * url fusion tables api * @type {string} */ var fusion_url = 'https://www.googleapis.com/fusiontables/v1/query'; /** * submit data fusion tables when form submitted. * @const * @param {object} e form object. */ function onformsubmit(e) { // row number of newly entered data. var sheet = spreadsheetapp.getactivesheet(); var row = sheet.getlastrow(); // check make sure rowid column there. init(); // values entered form, mapped question. var formvalues = e.namedvalues; // insert data fusion table. var rowid = createrecord(formvalues); if (!rowid) { rowid = -1; } insertrowid(rowid, row); } /** * initialize spreadsheet adding rowid column. */ function init() { var sheet = spreadsheetapp.getactivesheet(); var lastcolumn = sheet.getlastcolumn(); var lastheadervalue = sheet.getrange(1, lastcolumn).getvalue(); if (lastheadervalue != 'rowid') { sheet.getrange(1, lastcolumn + 1).setvalue('rowid'); } } /** * add rowid insert corresponding row in spreadsheet. * @param {string} rowid row id of inserted row. * @param {number} row row number enter rowid in. */ function insertrowid(rowid, row) { var sheet = spreadsheetapp.getactivesheet(); var lastcolumn = sheet.getlastcolumn(); lastcell = sheet.getrange(row, lastcolumn); lastcell.setvalue(rowid); } /** * create record in fusion table. * @param {object} dictionary of columns mapped values. * @return {?string} rowid if successful, otherwise null. */ function createrecord(columnvalues) { // properties associated script. var docid = scriptproperties.getproperty('docid'); var addresscolumn = scriptproperties.getproperty('addresscolumn'); var latlngcolumn = scriptproperties.getproperty('latlngcolumn'); // create lists of column names , values create insert statement. var columns = []; var values = []; (var column in columnvalues) { // if column not spreadsheetrownum, // add , value lists. if (column != 'spreadsheetrownum') { var value = columnvalues[column]; // if address column specified, geocode value in it. if (addresscolumn && column == addresscolumn) { var latlng = geocode(value); latlngcolumn = latlngcolumn.replace(/'/g, "\\\'"); columns.push(latlngcolumn); values.push(latlng); } if (typeof value != 'string') { value = value.tostring(); } value = value.replace(/'/g, "\\\'"); values.push(value); column = column.replace(/'/g, "\\\'"); columns.push(column); } } var query = []; query.push('insert '); query.push(docid); query.push(" ('"); query.push(columns.join("','")); query.push("') "); query.push("values ('"); query.push(values.join("','")); query.push("')"); var response = queryft(query.join('')); if (response) { var rowid = response[1][0]; return rowid; } } /** * geocode address. * @param {string} address user-entered address. * @return {string} geocoded results. */ function geocode(address) { var results = maps.newgeocoder().geocode(address); logger.log('geocoding: ' + address); if (results.status == 'ok') { var bestresult = results.results[0]; var lat = bestresult.geometry.location.lat; var lng = bestresult.geometry.location.lng; var latlng = lat + ',' + lng; logger.log('results: ' + latlng); return latlng; } else { logger.log('error geocoding: ' + address); logger.log(results.status); return '0,0'; } } /** * initialize query fusion tables. rerun query if not successful. * @param {string} query query execute * @return {?array} fusion table response formated array if * query successful. returns null if not. */ function queryft(query) { var response = run(query); // if query failed 401 or 500 error, try again 1 more time. if (response == -1) { response = run(query); } // if query failed again, or failed other reason, return. if (response == -1 || response == -2) { return; } return response; } /** * send query fusion tables , catch errors. * @param {string} query query execute * @return {array|number} fusion table response formatted array * if successful, -1 if 401 or 500 error occurred, -2 if other error * occurred. */ function run(query) { var method = 'post'; var lowercasequery = query.tolowercase(); if (lowercasequery.indexof('select') == 0 || lowercasequery.indexof('show') == 0 || lowercasequery.indexof('describe') == 0) { method = 'get'; } var token = scriptproperties.getproperty('token'); if (!token) { token = getgaauthenticationtoken(); if (!token) { return -2; } } var response; var sql = encodeuricomponent(query); try { if (method == 'get') { var url = fusion_url + '?sql=' + sql; response = urlfetchapp.fetch(url, { method: method, headers: { 'authorization': 'googlelogin auth=' + token } }); } else { response = urlfetchapp.fetch(fusion_url, { method: method, headers: { 'authorization': 'googlelogin auth=' + token }, payload: 'sql=' + sql }); } } catch(err) { if (err.message.search('401') != -1) { // if auth failed, new token token = getgaauthenticationtoken(); if (!token) { return -2; } return -1; } else if (err.message.search('500') != -1) { // if there many requests being sent, sleep bit utilities.sleep(3000); return -1; } else { logger.log('the failing query: ' + decodeuricomponent(sql)); var docid = scriptproperties.getproperty('docid'); if (!docid) { logger.log('the script missing docid project property'); } if (err.message.search('bad column reference') != -1) { logger.log('looks column names in form not match ' + 'the column names in table. make sure these match!'); } var addresscolumn = scriptproperties.getproperty('addresscolumn'); var latlngcolumn = scriptproperties.getproperty('latlngcolumn'); if (addresscolumn && !latlngcolumn) { logger.log('since added addresscolumn project property, ' + 'you need add latlngcolumn property'); } logger.log(err.message); return -2; } } response = response.getcontenttext(); response = csv2matrix(response); return response; } /** * auth token using client login. save token * script property "token". * @return {?string} auth token. */ function getgaauthenticationtoken() { var username; var password; var response; try { username = scriptproperties.getproperty('username'); if (!username) { throw new error('missing username in project properties.'); } } catch(err) { logger.log('error authenticating.'); logger.log(err.message); return; } try { password = scriptproperties.getproperty('password'); if (!password) { throw new error('missing password in project properties.'); } } catch(err) { logger.log('error authenticating.'); logger.log(err.message); return; } username = encodeuricomponent(username); password = encodeuricomponent(password); try { response = urlfetchapp.fetch( 'https://www.google.com/accounts/clientlogin', { method: 'post', payload: 'accounttype=google&email=' + username + '&passwd=' + password + '&service=fusiontables&source=googledocs' }); } catch(err) { logger.log('error authenticating.'); logger.log(err.message); return; } var tokens = response.getcontenttext(); var token = tokens.slice(tokens.search('auth=') + 5, tokens.length); token = token.replace(/\n/g, ''); scriptproperties.setproperty('token', token); return token; } /** * parse csv return values array of objects * copied , adapted here: * http://www.bennadel.com/blog/1504-ask-ben-parsing-csv-strings-with- * javascript-exec-regular-expression-command.htm * @param {string} strdata string data parse array * @param {string} strdelimiter string delimiter * @return {array} array of objects containing parsed values */ function csv2matrix(strdata, strdelimiter) { strdelimiter = (strdelimiter || ','); var objpattern = new regexp( '(\\' + strdelimiter + '|\\r?\\n|\\r|^)' + '(?:"([^"]*(?:""[^"]*)*)\"|' + '([^"\\' + strdelimiter + '\\r\\n]*))' , 'gi'); var arrdata = [[]]; var arrmatches = null; while (arrmatches = objpattern.exec(strdata)) { var strmatcheddelimiter = arrmatches[1]; if (strmatcheddelimiter.length && (strmatcheddelimiter != strdelimiter)) { arrdata.push([]); } if (arrmatches[2]) { var strmatchedvalue = arrmatches[2].replace(/'""'/g, '"'); } else { 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; } /** * sync fusion table form data. run every hour or so. */ function sync() { // check make sure rowid column there. init(); // data in spreadsheet , convert dictionary. var sheet = spreadsheetapp.getactivesheet(); var lastrow = sheet.getlastrow(); var lastcolumn = sheet.getlastcolumn(); var spreadsheetdata = sheet.getrange(1, 1, lastrow, lastcolumn); spreadsheetdata = spreadsheetdata.getvalues(); var spreadsheetmap = {}; converttomap(spreadsheetdata, spreadsheetmap); // columns in spreadsheet , escape single quotes var columns = spreadsheetdata[0]; var escapedcolumns = []; (var = 0; < columns.length; i++) { var columnname = columns[i]; columnname = columnname.replace(/'/g, "\\\'"); escapedcolumns.push(columnname); } // data table , convert dictionary. var docid = scriptproperties.getproperty('docid'); var query = "select '" + escapedcolumns.join("','") + "' " + docid; var ftresults = queryft(query); if (!ftresults) { return; } var ftmap = {}; converttomap(ftresults, ftmap); // properties associated script. var addresscolumn = scriptproperties.getproperty('addresscolumn'); var latlngcolumn = scriptproperties.getproperty('latlngcolumn'); // each row in fusion table, find if row still exists in // spreadsheet. if exists, make sure values same. if // different, update fusion table data. // if row doesn't exist in spreadsheet, delete row table. (var rowid in ftmap) { var spreadsheetrow = spreadsheetmap[rowid]; if (spreadsheetrow) { var updates = []; var tablerow = ftmap[rowid]; (var column in tablerow) { var tablevalue = tablerow[column]; var spreadsheetvalue = spreadsheetrow[column]; if (tablevalue != spreadsheetvalue) { if (addresscolumn == column) { var latlng = geocode(spreadsheetvalue); latlngcolumn = latlngcolumn.replace(/'/g, "\\\'"); updates.push("'" + latlngcolumn + "' = '" + latlng + "'"); } if (typeof spreadsheetvalue != 'string') { spreadsheetvalue = spreadsheetvalue.tostring(); } spreadsheetvalue = spreadsheetvalue.replace(/'/g, "\\\'"); column = column.replace(/'/g, "\\\'"); updates.push("'" + column + "' = '" + spreadsheetvalue + "'"); } } // if there updates, send update query. if (updates.length) { var query = []; query.push('update '); query.push(docid); query.push(' set '); query.push(updates.join(',')); query.push(" rowid = '"); query.push(rowid); query.push("'"); queryft(query.join('')); utilities.sleep(3000); } } else { // if row doesn't exist in spreadsheet, delete table queryft('delete ' + docid + " rowid = '" + rowid + "'"); utilities.sleep(3000); } } // insert data fusion table failed insert. // these rows given rowid of -1 or have blank rowid. var failedinserts = spreadsheetmap[-1]; (var = 0; failedinserts && < failedinserts.length; i++) { var rowid = createrecord(failedinserts[i]); if (!rowid) { rowid = -1; } insertrowid(rowid, failedinserts[i]['spreadsheetrownum']); utilities.sleep(3000); } } /** * converts form , table data dictionary, mapping rowid * column values. if rowid == -1 or null, rowid mapped list * of column values representing failed inserts. * @param {array} array array of data, first row contains headers. * @param {object} map resulting dictionary of row id mapped columns. * {rowid:{column:value,...} | [{{column:value,...}}],}. */ function converttomap(array, map) { var columns = array[0]; (var = 1; < array.length; i++) { var row = array[i]; var rowid = row[row.length - 1]; var columnmap = {}; (var j = 0; j < row.length - 1; j++) { var columnname = columns[j]; var columnvalue = row[j]; columnmap[columnname] = columnvalue; } if (rowid == -1 || !rowid) { if (!map[-1]) { map[-1] = []; } // add spreadsheet row number map columnmap['spreadsheetrownum'] = + 1; map[-1].push(columnmap); } else { map[rowid] = columnmap; } } } is else using method of data input , got work new api?
thanks
with straight js can construct url such below , encode using encodeuri() here's encoded example - replace tableid , key actual strings
https://www.googleapis.com/fusiontables/v1/query?sql=select%20suburb%20from%20__tableid__%20limit%201000&jsoncallback=&&key=__key__&callback=&
Comments
Post a Comment