SpreadSheet To Fusion Tables

README.md

Script to sync a Google SpreadSheet to a Fusion Table

Save for a few legacy projects that still use Fusion Tables I don't actively use this script anymore. This update hopefully solves the OAuth issue that cropped up once Google depcricated the Client Login method used by the prior version.

As always, your mileage may vary, and I welcome someone -- Google or otherwise -- to offer a long-term maintained solution.

The following is largely cribbed from a Google example here. I try to explain some of the API settings that must be enabled in the Developer's Console and elsewhere

Create your spreadsheet and import it into Fusion Tables

• Head to Google docs, create a spreadsheet and add some data to it. Simple enough right?

• Now I'm going to create a Fusion Table based off my spreadsheet. I do this either by importing from Google Docs or downloading the spreadsheet as a csv and uploading it to Fusion Tables. It is important to note it's best practice for the column names of the spreadsheet and the table to match. It's not a deal breaker, but why not be consistent? For good measure I make sure any new columns are also in the same order.

• After my Fusion Table is created, I need to get the Encrypted Table ID in order to make sure the spreadsheet can access it. To find the Encrypted Table ID I click File --> About this table. I'll copy this somewhere in a text file until I need it.

• I'm almost ready to make this happen, but I'm going to need to enable the Google Fusion Tables API in two places.

• First, head to the Developer's Console and log in with your Google account if prompted.

• Click create a project and give it a name.

• Choose the Enable and manage APIs under the Use Google APIs tile

• Find Fusion Tables API and click the "Enable API button"

• Click Go to Credentials. You will be "Calling Fusion Tables API from a web browser"

• Create the OAuth 2.0 client ID

• For Product name shown to users I put "Sync Spreadsheet To Fusion Tables"

• Click Done

Add the script to the spreadsheet

• Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --> Script Editor and paste the script code. On Line 9 I add my Fusion Table's Table ID...

    // Add the encrypted table ID of the fusion table here
    var TABLE_ID = '17xnxY......';
  

• Optional: if you have multiple header rows, put the row number of the first data row on this line:

        var FIRST_DATA_ROW = 2;
  

• Optional: if you want to allow the spreadsheet to have different columns than the table, change the line 15 value to "false":

        var REQUIRE_SAME_COLUMNS = true;
  

• Click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --> Save.

• Click Resources --> Developer's Console Project. Enter the Project Number for the project you just created.

• Reload the spreadsheet and you will see a new menu item next to help. Mine reads "Sync Spreadsheet To Fusion Table." Click the menu item and you will see an option to "Update Fusion Table."

• Now just add some new information to your spreadsheet and click "Update Fusion Table." The application will ask you to authenticate. Once you click OK, your spreadsheet data should be synced with your Fusion Table.

• Sit back and enjoy this moment …

This script can now be combined with other script functions to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run minute by minute, or hourly and those changes will be reflected on the table.

Of course, unless the script didn't work... Mention me in the comments so I'm notified and we'll see what we can do...

---

Trying to log error messages and the possible cause. These are not confirmed. Just best guesses

...

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)

The following is likely caused by your API key not being recognized.

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=MY API CODE& returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "authError", "message": "Invalid Credentials", "locationType": "head... (use muteHttpExceptions option to examine full response) (line 79, file "Code")

Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the Google API console and added the key to the script in the proper place.

The following is likely caused by either not authenticating or a corrupt authentication token

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response

spreadsheet_to_fusion_tables.js

/**
 * appsscript script to run in a google spreadsheet that synchronizes its
 * contents with a fusion table by replacing all rows.
 * based on instructions here: 
 * https://htmlpreview.github.io/?https://github.com/fusiontable-gallery/fusion-tables-api-samples/blob/master/FusionTablesSheetSync/docs/reference.html#enabling_advanced_services
 */

// replace with your fusion table's id (from File > About this table)
var TABLE_ID = '17xnxY......';

// first row that has data, as opposed to header information
var FIRST_DATA_ROW = 2;

// true means the spreadsheet and table must have the same column count
var REQUIRE_SAME_COLUMNS = true;

/**
 * replaces all rows in the fusion table identified by TABLE_ID with the
 * current sheet's data, starting at FIRST_DATA_ROW.
 */
function sync() {
    var tasks = FusionTables.Task.list(TABLE_ID);  
    // Only run if there are no outstanding deletions or schema changes.
    if (tasks.totalItems === 0) {
        var sheet = SpreadsheetApp.getActiveSheet();
        var wholeSheet = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
        var values = wholeSheet.getValues();
        if (values.length > 1) {
            var csvBlob = Utilities.newBlob(convertToCsv_(values), 'application/octet-stream');
            FusionTables.Table.replaceRows(TABLE_ID, csvBlob, { isStrict: REQUIRE_SAME_COLUMNS, startLine: FIRST_DATA_ROW - 1 });
            Browser.msgBox('Replaced ' + values.length + ' rows in your Fusion Table', Browser.Buttons.OK);
        }
    } else {
        Logger.log('Skipping row replacement because of ' + tasks.totalItems + ' active background task(s)');
    }
};

/**
 * converts the spreadsheet values to a csv string.
 * @param {array} data the spreadsheet values.
 * @return {string} the csv string.
 */
function convertToCsv_(data) {
    // See https://developers.google.com/apps-script/articles/docslist_tutorial#section3
    var csv = '';
    for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
            var value = data[row][col].toString();
            if (value.indexOf(',') != -1 ||
                value.indexOf('\n') != -1 ||
                value.indexOf('"') != -1) {
                    // Double-quote values with commas, double quotes, or newlines
                    value = '"' + value.replace(/"/g, '""') + '"';
                    data[row][col] = value;
            }
        };
        // Join each row's columns and add a carriage return to end of each row except the last
        if (row < data.length - 1) {
            csv += data[row].join(',') + '\r\n';
        } else {
            csv += data[row];
        };
    };
    return csv;
};

// create menu buttons
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Update Fusion Table",
        functionName: "sync"
    }];
    ss.addMenu("Sync Spreadsheet To Fusion Table", menuEntries);
};

Someone has it already for 2018??? thanks

How to run sync function on form submit? I added it to current project triggers (run-sync from spreadsheet and on form submission). However it doesn't get run.

How would I modify the script if I have multiple sheets pushing data into 1 fusion table so they don't overwrite the others? Set a unique row to start?

Tried that with no success; any other ideas?

Thanks for the great script. Works well. Ran in to a small issue where it stops when confronted with single quotes.

Would appreciate it if someone could patch the code so that it works well with single quotes.

Is there a way to only sync new rows that have been added to the Gsheet? We like to create views and use individual fusion tables, but every time we run the script all of our changes in fusion table are erased.

Hi everyone, I have the same problem as @mattcojones .

Although 2 columns in the Google Spreadsheets are in the date time format, it changes to a text string in the Fusion Tables. I suspect it's due to the inclusion of the Convert to CSV code. Can this portion of the script be omitted?

Hope to learn from all of you cos I am a beginner. Thank you! :)

function getColumnNrByName(sheet, name)
{var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
var values = range.getValues();
for (var row in values) {
for (var col in values[row]) {
if (values[row][col] == name) {
return (parseInt(col)+1);
}
}
}
throw 'failed to get column by name';
}

function addressToPosition() {
var sheet = SpreadsheetApp.getActiveSheet();
var addloc= getColumnNrByName(sheet, "Where is the location of the F&B joint?"); //Get column number of where free form location is stored
var latloc= getColumnNrByName(sheet, "Lat"); //Get column number for where Lat is to be stored
var longloc= getColumnNrByName(sheet, "Lng");
var geocoder = Maps.newGeocoder().setRegion('sg');

var lastRow= sheet.getDataRange().getLastRow();
Logger.log(lastRow)
var addrtxt=sheet.getRange(lastRow, addloc).getValue();
Logger.log(addrtxt);
location = geocoder.geocode(addrtxt);

if (location.status === 'OK') {
lat = location["results"][0]["geometry"]["location"]["lat"];
lng = location["results"][0]["geometry"]["location"]["lng"];
sheet.getRange(lastRow, latloc).setValue(lat);
sheet.getRange(lastRow, longloc).setValue(lng);
} else //Go to Null Island
{ sheet.getRange(lastRow, latloc).setValue(0.00);
sheet.getRange(lastRow, longloc).setValue(0.00);
}
}

/**

• AppsScript script to run in a Google Spreadsheet that synchronizes its
• contents with a Fusion Table by replacing all rows.
  */

// Replace with your Fusion Table's ID (from File > About this table)
var TABLE_ID = '1EmZEpPjVy6olbXBz7wHA4B5fUR3xC8ZlamCCtPVk';

// First row that has data, as opposed to header information
var FIRST_DATA_ROW = 2;

// True means the spreadsheet and table must have the same column count
var REQUIRE_SAME_COLUMNS = true;

/**

• Replaces all rows in the Fusion Table identified by TABLE_ID with the
• current sheet's data, starting at FIRST_DATA_ROW.
  */
  function sync() {
  var tasks = FusionTables.Task.list(TABLE_ID);
  // Only run if there are no outstanding deletions or schema changes.
  if (tasks.totalItems == 0) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var wholeSheet = sheet.getRange(1, 1, sheet.getLastRow(),
  sheet.getLastColumn());
  var values = wholeSheet.getValues();
  if (values.length > 1) {
  var csvBlob = Utilities.newBlob(convertToCsv_(values),
  'application/octet-stream');
  FusionTables.Table.replaceRows(TABLE_ID, csvBlob,
  { isStrict: REQUIRE_SAME_COLUMNS, startLine: FIRST_DATA_ROW - 1 });
  Logger.log('Replaced ' + values.length + ' rows');
  }
  } else {
  Logger.log('Skipping row replacement because of ' + tasks.totalItems +
  ' active background task(s)');
  }
  }

/**

• Converts the spreadsheet values to a CSV string.
• @param {Array} data The spreadsheet values.
• @return {string} The CSV string.
  */
  function convertToCsv_(data) {
  // See https://developers.google.com/apps-script/articles/docslist_tutorial#section3
  var csv = '';
  for (var row = 0; row < data.length; row++) {
  for (var col = 0; col < data[row].length; col++) {
  var value = data[row][col].toString();
  if (value.indexOf(',') != -1 ||
  value.indexOf('\n') != -1 ||
  value.indexOf('"') != -1) {
  // Double-quote values with commas, double quotes, or newlines
  value = '"' + value.replace(/"/g, '""') + '"';
  data[row][col] = value;
  }
  }
  // Join each row's columns and add a carriage return to end of each row
  // except the last
  if (row < data.length - 1) {
  csv += data[row].join(',') + '\r\n';
  }
  else {
  csv += data[row];
  }
  }
  return csv;
  }

With Google fusion tables you need to convert formats to another because they only support csv. and kml. formats. With this you can use xls, csv, kml, shp and many other formats:

Spatial Vector files: .shp, .mif, .mid, .tab, .kml, .gpx, .dxf, SQLite
Spatial Raster files: .tif, .tiff, .jpg, .png, .gif, .img, .ecw, .sid, .jp2
Image files: .jpg, .gif, .swf, .png, .img, .ecw, .sid, .jp2
Excel and CSV: .csv and .xls

Also check out this as an alternative to google fusion tables as they are getting shut down by google.