· 9 years ago · Dec 30, 2016, 10:12 PM
1returned code 403. Server response: {
2 "error": {
3 "errors": [
4 {
5 "domain": "usageLimits",
6 "reason": "dailyLimitExceededUnreg",
7 "message": "Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup.",
8 "extendedHelp": "https://code.google.com/apis/console"
9 }
10 ],
11 "code": 403,
12 "message": "Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup."
13 }
14}
15
16/**
17 * The URL for the Fusion Tables API
18 * @type {string}
19 */
20var FUSION_URL = 'https://www.googleapis.com/fusiontables/v1/query';
21
22/**
23 * Submit the data to Fusion Tables when the form is submitted.
24 * @const
25 * @param {Object} e The form object.
26 */
27function onFormSubmit(e) {
28 // Get the row number of the newly entered data.
29 var sheet = SpreadsheetApp.getActiveSheet();
30 var row = sheet.getLastRow();
31
32 // Check to make sure the rowid column is there.
33 init();
34
35 // The values entered into the form, mapped by question.
36 var formValues = e.namedValues;
37
38 // Insert the data into the Fusion Table.
39 var rowId = createRecord(formValues);
40 if (!rowId) {
41 rowId = -1;
42 }
43 insertRowId(rowId, row);
44}
45
46/**
47 * Initialize the spreadsheet by adding a rowid column.
48 */
49function init() {
50 var sheet = SpreadsheetApp.getActiveSheet();
51 var lastColumn = sheet.getLastColumn();
52 var lastHeaderValue = sheet.getRange(1, lastColumn).getValue();
53 if (lastHeaderValue != 'rowid') {
54 sheet.getRange(1, lastColumn + 1).setValue('rowid');
55 }
56}
57
58/**
59 * Add the rowid from the INSERT to the corresponding row in the spreadsheet.
60 * @param {string} rowId The row id of the inserted row.
61 * @param {number} row The row number to enter the rowid in.
62 */
63function insertRowId(rowId, row) {
64 var sheet = SpreadsheetApp.getActiveSheet();
65 var lastColumn = sheet.getLastColumn();
66 lastCell = sheet.getRange(row, lastColumn);
67 lastCell.setValue(rowId);
68}
69
70/**
71 * Create a record in the Fusion Table.
72 * @param {Object} dictionary of columns mapped to values.
73 * @return {?string} the rowid if successful, otherwise null.
74 */
75function createRecord(columnValues) {
76 // Get the properties associated with this Script.
77 var docid = ScriptProperties.getProperty('docid');
78 var addressColumn = ScriptProperties.getProperty('addressColumn');
79 var latlngColumn = ScriptProperties.getProperty('latlngColumn');
80
81 // Create lists of the column names and values to create the INSERT Statement.
82 var columns = [];
83 var values = [];
84 for (var column in columnValues) {
85 // If the column is not the spreadsheetRowNum,
86 // add it and the value to the lists.
87 if (column != 'spreadsheetRowNum') {
88 var value = columnValues[column];
89
90 // If an address column was specified, geocode the value in it.
91 if (addressColumn && column == addressColumn) {
92 var latlng = geocode(value);
93 latlngColumn = latlngColumn.replace(/'/g, "\'");
94 columns.push(latlngColumn);
95 values.push(latlng);
96 }
97
98 if (typeof value != 'string') {
99 value = value.toString();
100 }
101 value = value.replace(/'/g, "\'");
102 values.push(value);
103
104 column = column.replace(/'/g, "\'");
105 columns.push(column);
106 }
107 }
108
109 var query = [];
110 query.push('INSERT INTO ');
111 query.push(docid);
112 query.push(" ('");
113 query.push(columns.join("','"));
114 query.push("') ");
115 query.push("VALUES ('");
116 query.push(values.join("','"));
117 query.push("')");
118
119 var response = queryFt(query.join(''));
120 if (response) {
121 var rowId = response[1][0];
122 return rowId;
123 }
124}
125
126/**
127 * Geocode the address.
128 * @param {string} address The user-entered address.
129 * @return {string} the geocoded results.
130 */
131function geocode(address) {
132 var results = Maps.newGeocoder().geocode(address);
133 Logger.log('Geocoding: ' + address);
134 if (results.status == 'OK') {
135 var bestResult = results.results[0];
136 var lat = bestResult.geometry.location.lat;
137 var lng = bestResult.geometry.location.lng;
138 var latLng = lat + ',' + lng;
139 Logger.log('Results: ' + latLng);
140 return latLng;
141 } else {
142 Logger.log('Error geocoding: ' + address);
143 Logger.log(results.status);
144 return '0,0';
145 }
146}
147
148
149/**
150 * Initialize the query to Fusion Tables. Rerun the query if not successful.
151 * @param {string} query The query to execute
152 * @return {?Array} the Fusion Table response formated as an array if the
153 * query was successful. Returns null if not.
154 */
155function queryFt(query) {
156 var response = run(query);
157
158 // If the query failed with a 401 or 500 error, try again one more time.
159 if (response == -1) {
160 response = run(query);
161 }
162
163 // If the query failed again, or failed for some other reason, return.
164 if (response == -1 || response == -2) {
165 return;
166 }
167
168 return response;
169}
170
171/**
172 * Send query to Fusion Tables and catch any errors.
173 * @param {string} query The query to execute
174 * @return {Array|number} the Fusion Table response formatted as an array
175 * if successful, -1 if a 401 or 500 error occurred, -2 if some other error
176 * occurred.
177 */
178function run(query) {
179 var method = 'post';
180 var lowercaseQuery = query.toLowerCase();
181 if (lowercaseQuery.indexOf('select') == 0 ||
182 lowercaseQuery.indexOf('show') == 0 ||
183 lowercaseQuery.indexOf('describe') == 0) {
184 method = 'get';
185 }
186
187 var token = ScriptProperties.getProperty('token');
188 if (!token) {
189 token = getGAauthenticationToken();
190 if (!token) {
191 return -2;
192 }
193 }
194
195 var response;
196 var sql = encodeURIComponent(query);
197 try {
198 if (method == 'get') {
199 var url = FUSION_URL + '?sql=' + sql;
200 response = UrlFetchApp.fetch(url, {
201 method: method,
202 headers: {
203 'Authorization': 'GoogleLogin auth=' + token
204 }
205 });
206 } else {
207 response = UrlFetchApp.fetch(FUSION_URL, {
208 method: method,
209 headers: {
210 'Authorization': 'GoogleLogin auth=' + token
211 },
212 payload: 'sql=' + sql
213 });
214 }
215 } catch(err) {
216 if (err.message.search('401') != -1) {
217 // If the auth failed, get a new token
218 token = getGAauthenticationToken();
219 if (!token) {
220 return -2;
221 }
222 return -1;
223 } else if (err.message.search('500') != -1) {
224 // If there were too many requests being sent, sleep for a bit
225 Utilities.sleep(3000);
226 return -1;
227 } else {
228 Logger.log('The failing query: ' + decodeURIComponent(sql));
229 var docid = ScriptProperties.getProperty('docid');
230 if (!docid) {
231 Logger.log('The script is missing a docid Project Property');
232 }
233 if (err.message.search('Bad column reference') != -1) {
234 Logger.log('Looks like the column names in the form do not match ' +
235 'the column names in the table. Make sure these match!');
236 }
237 var addressColumn = ScriptProperties.getProperty('addressColumn');
238 var latlngColumn = ScriptProperties.getProperty('latlngColumn');
239 if (addressColumn && !latlngColumn) {
240 Logger.log('Since you added an addressColumn project property, ' +
241 'you also need to add a latlngColumn property');
242 }
243 Logger.log(err.message);
244 return -2;
245 }
246 }
247
248 response = response.getContentText();
249 response = CSV2Matrix(response);
250 return response;
251}
252
253/**
254 * Get the auth token using Client Login. Save the token
255 * to a Script Property "token".
256 * @return {?string} the auth token.
257 */
258function getGAauthenticationToken() {
259 var username;
260 var password;
261 var response;
262
263 try {
264 username = ScriptProperties.getProperty('username');
265 if (!username) {
266 throw new Error('Missing username in Project Properties.');
267 }
268 } catch(err) {
269 Logger.log('Error authenticating.');
270 Logger.log(err.message);
271 return;
272 }
273
274 try {
275 password = ScriptProperties.getProperty('password');
276 if (!password) {
277 throw new Error('Missing password in Project Properties.');
278 }
279 } catch(err) {
280 Logger.log('Error authenticating.');
281 Logger.log(err.message);
282 return;
283 }
284
285 username = encodeURIComponent(username);
286 password = encodeURIComponent(password);
287 try {
288 response = UrlFetchApp.fetch(
289 'https://www.google.com/accounts/ClientLogin', {
290 method: 'post',
291 payload: 'accountType=GOOGLE&Email=' + username +
292 '&Passwd=' + password + '&service=fusiontables&Source=googledocs'
293 });
294 } catch(err) {
295 Logger.log('Error authenticating.');
296 Logger.log(err.message);
297 return;
298 }
299
300 var tokens = response.getContentText();
301 var token = tokens.slice(tokens.search('Auth=') + 5, tokens.length);
302 token = token.replace(/n/g, '');
303 ScriptProperties.setProperty('token', token);
304 return token;
305}
306
307/**
308 * Parse CSV return values into array of objects
309 * Copied and adapted from here:
310 * http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-
311 * Javascript-Exec-Regular-Expression-Command.htm
312 * @param {string} strData The string data to parse into an array
313 * @param {string} strDelimiter The string delimiter
314 * @return {Array} An array of objects containing the parsed values
315 */
316function CSV2Matrix(strData, strDelimiter) {
317 strDelimiter = (strDelimiter || ',');
318 var objPattern = new RegExp(
319 '(\' + strDelimiter + '|\r?\n|\r|^)' +
320 '(?:"([^"]*(?:""[^"]*)*)"|' +
321 '([^"\' + strDelimiter + '\r\n]*))'
322 , 'gi');
323 var arrData = [[]];
324 var arrMatches = null;
325
326 while (arrMatches = objPattern.exec(strData)) {
327 var strMatchedDelimiter = arrMatches[1];
328 if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter)) {
329 arrData.push([]);
330 }
331 if (arrMatches[2]) {
332 var strMatchedValue = arrMatches[2].replace(/'""'/g, '"');
333 } else {
334 var strMatchedValue = arrMatches[3];
335 }
336 arrData[arrData.length-1].push(strMatchedValue);
337 }
338 if (arrData[arrData.length-1].length == 0 ||
339 arrData[arrData.length-1][0].length == 0) {
340 arrData.pop();
341 }
342 return arrData;
343}
344
345/**
346 * Sync the Fusion Table to the Form data. Run this every hour or so.
347 */
348function sync() {
349 // Check to make sure the rowid column is there.
350 init();
351
352 // Get the data in the spreadsheet and convert it to a dictionary.
353 var sheet = SpreadsheetApp.getActiveSheet();
354 var lastRow = sheet.getLastRow();
355 var lastColumn = sheet.getLastColumn();
356 var spreadsheetData = sheet.getRange(1, 1, lastRow, lastColumn);
357 spreadsheetData = spreadsheetData.getValues();
358 var spreadsheetMap = {};
359 convertToMap(spreadsheetData, spreadsheetMap);
360
361 // Get the columns in the spreadsheet and escape any single quotes
362 var columns = spreadsheetData[0];
363 var escapedColumns = [];
364 for (var i = 0; i < columns.length; i++) {
365 var columnName = columns[i];
366 columnName = columnName.replace(/'/g, "\'");
367 escapedColumns.push(columnName);
368 }
369
370 // Get the data from the table and convert to a dictionary.
371 var docid = ScriptProperties.getProperty('docid');
372 var query = "SELECT '" + escapedColumns.join("','") + "' FROM " + docid;
373 var ftResults = queryFt(query);
374 if (!ftResults) {
375 return;
376 }
377 var ftMap = {};
378 convertToMap(ftResults, ftMap);
379
380 // Get the properties associated with this Script.
381 var addressColumn = ScriptProperties.getProperty('addressColumn');
382 var latlngColumn = ScriptProperties.getProperty('latlngColumn');
383
384 // For each row in the Fusion Table, find if the row still exists in the
385 // Spreadsheet. If it exists, make sure the values are the same. If
386 // they are different, update the Fusion Table data.
387 // If the row doesn't exist in the spreadsheet, delete the row from the table.
388 for (var rowId in ftMap) {
389 var spreadsheetRow = spreadsheetMap[rowId];
390 if (spreadsheetRow) {
391 var updates = [];
392 var tableRow = ftMap[rowId];
393 for (var column in tableRow) {
394 var tableValue = tableRow[column];
395 var spreadsheetValue = spreadsheetRow[column];
396 if (tableValue != spreadsheetValue) {
397 if (addressColumn == column) {
398 var latlng = geocode(spreadsheetValue);
399 latlngColumn = latlngColumn.replace(/'/g, "\'");
400 updates.push("'" + latlngColumn + "' = '" + latlng + "'");
401 }
402 if (typeof spreadsheetValue != 'string') {
403 spreadsheetValue = spreadsheetValue.toString();
404 }
405 spreadsheetValue = spreadsheetValue.replace(/'/g, "\'");
406 column = column.replace(/'/g, "\'");
407 updates.push("'" + column + "' = '" + spreadsheetValue + "'");
408 }
409 }
410
411 // If there are updates, send the UPDATE query.
412 if (updates.length) {
413 var query = [];
414 query.push('UPDATE ');
415 query.push(docid);
416 query.push(' SET ');
417 query.push(updates.join(','));
418 query.push(" WHERE rowid = '");
419 query.push(rowId);
420 query.push("'");
421 queryFt(query.join(''));
422 Utilities.sleep(3000);
423 }
424
425 } else {
426 // If the row doesn't exist in the spreadsheet, delete it from the table
427 queryFt('DELETE FROM ' + docid + " WHERE rowid = '" + rowId + "'");
428 Utilities.sleep(3000);
429 }
430 }
431
432 // Insert all the data into the Fusion Table that failed to insert.
433 // These rows were given a rowid of -1 or have a blank rowid.
434 var failedInserts = spreadsheetMap[-1];
435 for (var i = 0; failedInserts && i < failedInserts.length; i++) {
436 var rowId = createRecord(failedInserts[i]);
437 if (!rowId) {
438 rowId = -1;
439 }
440 insertRowId(rowId, failedInserts[i]['spreadsheetRowNum']);
441 Utilities.sleep(3000);
442 }
443}
444
445/**
446 * Converts the form and table data to a dictionary, mapping rowid
447 * to column values. If rowid == -1 or null, the rowid is mapped to a list
448 * of column values representing the failed inserts.
449 * @param {Array} array An array of data, the first row contains headers.
450 * @param {Object} map The resulting dictionary of row id mapped to columns.
451 * {rowid:{column:value,...} | [{{column:value,...}}],}.
452 */
453function convertToMap(array, map) {
454 var columns = array[0];
455
456 for (var i = 1; i < array.length; i++) {
457 var row = array[i];
458 var rowId = row[row.length - 1];
459 var columnMap = {};
460
461 for (var j = 0; j < row.length - 1; j++) {
462 var columnName = columns[j];
463 var columnValue = row[j];
464 columnMap[columnName] = columnValue;
465 }
466
467 if (rowId == -1 || !rowId) {
468 if (!map[-1]) {
469 map[-1] = [];
470 }
471 // Add the spreadsheet row number to the map
472 columnMap['spreadsheetRowNum'] = i + 1;
473 map[-1].push(columnMap);
474 } else {
475 map[rowId] = columnMap;
476 }
477 }
478}
479
480https://www.googleapis.com/fusiontables/v1/query?sql=SELECT%20Suburb%20FROM%20__tableID__%20LIMIT%201000&jsonCallback=&&key=__key__&callback=&