I have created a Google Spreadsheet which I would like to sync to Google Fusion Tables. In the end, I'd like to create a dashboard with provide (somewhat) live visual information from the spreadsheet. (I'd like to be able to map the businesses, the category of business, and use a different icon for businesses that need to be visited).
Currently, my spreadsheet has ~570 rows x ~24 columns, but new rows will be added throughout the year, and potentially data will be added to the existing cell.
I used this: http://fusion-tables-api-samples.googlecode.com/svn/trunk/FusionTablesFormSync/docs/reference.html and was sure to enable the fusion tables service for the apps script and in the developer console.
My problem is that within the 6 minute time frame only 115 of the rows has been synchronized (with values in the rowid column). Running the sync function again allows maybe 1-2 rows to be added.
How can I speed up the process and/or have it pick up the synchronizing from where it left off?
EDIT: So to get the rows that haven't yet been added, I commented out the portion of the sync function that checks to see if the row exists in the spreadsheet, if yes, making sure the values are the same, otherwise updating.
When running the sync function, this is the execution transcript: [14-07-22 14:08:54:798 EDT] Starting execution [14-07-22 14:08:54:856 EDT] ScriptProperties.getProperty([docid]) [0.038 seconds] [14-07-22 14:08:54:872 EDT] ScriptProperties.getProperty([addressColumn]) [0.014 seconds] [14-07-22 14:08:54:889 EDT] ScriptProperties.getProperty([latlngColumn]) [0.015 seconds] [14-07-22 14:08:54:889 EDT] Session.getScriptTimeZone() [0 seconds] [14-07-22 14:08:54:890 EDT] SpreadsheetApp.getActiveSheet() [0 seconds] [14-07-22 14:08:55:067 EDT] Sheet.getLastColumn() [0.176 seconds] [14-07-22 14:08:55:068 EDT] Sheet.getRange([1, 23]) [0 seconds] [14-07-22 14:08:55:134 EDT] Range.getValue() [0.064 seconds] [14-07-22 14:08:55:134 EDT] SpreadsheetApp.getActiveSheet() [0 seconds] [14-07-22 14:08:55:187 EDT] Sheet.getLastRow() [0.051 seconds] [14-07-22 14:08:55:237 EDT] Sheet.getLastColumn() [0.049 seconds] [14-07-22 14:08:55:238 EDT] Sheet.getRange([1, 1, 592, 23]) [0 seconds] [14-07-22 14:08:55:965 EDT] Range.getValues() [0.727 seconds] [14-07-22 14:14:57:561 EDT] Execution failed: Exceeded maximum execution time [362.742 seconds total runtime]
How can I figure out where it is hanging / how to make it process faster?
Here is the sync function:
/**
* Syncs the Fusion Table to the form data. Run this every hour or so.
*/
function sync() {
init();
// Get the data in the spreadsheet and convert it to a dictionary.
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var spreadsheetData = sheet.getRange(1, 1, lastRow, lastColumn);
var spreadsheetValues = spreadsheetData.getValues();
var columns = spreadsheetValues[0];
var spreadsheetMap = mapRowsByRowId(columns,
spreadsheetValues.slice(1, spreadsheetValues.length));
// Get the columns in the spreadsheet and escape any single quotes
var escapedColumns = [];
for (var i = 0; i < columns.length; i++) {
var columnName = columns[i];
columnName = escapeQuotes(columnName);
escapedColumns.push(columnName);
}
// Get the data from the table and convert to a dictionary.
var query = "SELECT '" + escapedColumns.join("','") + "' FROM " + DOCID;
var ftResults = runSqlWithRetry(query);
if (!ftResults) {
return;
}
var ftMap = mapRowsByRowId(ftResults.columns, ftResults.rows);
// For each row in the Fusion Table, find if the row still exists in the
// spreadsheet. If it exists, make sure the values are the same. If
// they are different, update the Fusion Table data.
// If the row doesn't exist in the spreadsheet, delete the row from the table.
///*
for (var rowId in ftMap) {
var spreadsheetRow = spreadsheetMap[rowId];
if (spreadsheetRow) {
var updates = [];
var tableRow = ftMap[rowId];
for (var column in tableRow) {
if (column === 'rowid') {
continue;
}
var tableValue = tableRow[column];
var spreadsheetValue = spreadsheetRow[column];
if (tableValue != spreadsheetValue) {
spreadsheetValue = processSpreadsheetValue(column, spreadsheetValue);
updates.push("'" + escapeQuotes(column) + "' = '" +
spreadsheetValue + "'");
}
}
// If there are updates, send the UPDATE query.
if (updates.length) {
var query = [];
query.push('UPDATE ');
query.push(DOCID);
query.push(' SET ');
query.push(updates.join(','));
query.push(" WHERE rowid = '");
query.push(rowId);
query.push("'");
runSqlWithRetry(query.join(''));
waitBetweenCalls();
}
} else {
// If the row doesn't exist in the spreadsheet, delete it from the table
runSqlWithRetry('DELETE FROM ' + DOCID + " WHERE rowid = '" +
rowId + "'");
waitBetweenCalls();
}
}
//*/
// Insert all the data into the Fusion Table that failed to insert.
// These rows were given a rowid of -1 or have a blank rowid.
var failedInserts = spreadsheetMap[-1];
for (var i = 0; failedInserts && i < failedInserts.length; i++) {
var rowId = createRecord(failedInserts[i]);
if (!rowId) {
rowId = -1;
}
insertRowId(rowId, failedInserts[i].spreadsheetRowNum);
waitBetweenCalls();
}
}
