Skip to content

Instantly share code, notes, and snippets.

@chrislkeller
Last active December 18, 2020 08:12
Show Gist options
  • Save chrislkeller/3013360 to your computer and use it in GitHub Desktop.
Save chrislkeller/3013360 to your computer and use it in GitHub Desktop.
SpreadSheet To Fusion Tables

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?

Google spreadsheet

  • 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.

Fusion Table Import

  • 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"

Credentials

  • 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
/**
* 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);
};
@mnorelli
Copy link

Getting the error:
ReferenceError: "FusionTables" is not defined.

Fixed by enabling Fusion Tables in the Script Editor:
Go to Resources > Advanced Google Services..
Search for "Fusion Tables API" and switch to On.
Click Ok.
screenshot 2016-04-20 11 47 11

@LizHannaford
Copy link

Thanks very much for the updated script and all the helpful comments that enabled me to fix the error messages.
I've got an Update fusion Table tab on my google sheet. When I run it, it seems to work fine. I get a message box telling me it's Replaced 33 Rows in FT. I click OK. But, when I go to FT and refresh, it hasn't updated the spread sheet.
Any ideas what might be going wrong?
(This is the first time I've ventured onto Github so go easy on me...!)

@daniel-fahey
Copy link

daniel-fahey commented May 11, 2016

@3dfh @PasNecessairement

Content has a different number of columns than the table

I had the same issue, setting the line 15 value to false got it working for me.
This is despite having the same number of columns with the same header names.
Still, massive thank you to @chriskeller

@albess
Copy link

albess commented Jul 1, 2016

Hi Chris (or anyone that might be able to help)

I have sync'd a google form to a fusion table via a google spreadsheet. recently my colleague changed one of the headers and threw my auto sync off. Here's the message I keep getting: Problem running SQL: SELECT 'Timestamp','Representative, Organization or Landlord Name','Availability','Lease Timeframe','Full Address','Rent Amount','Upfront Costs','Bedroom (s)','Bathrooms','Floor','Pets','Parking','Contact Name','Contact Phone Number, E-Mail','Notes','Status','rowid' FROM 1GjBak1ySMdbo85PohEfgckygrUtD_ssstg6nXY43: Exception: Invalid query: Column `Representative, Organization or Landlord Name' does not exist. Looks like the column names in the form do not match the column names in the table. Make sure these match! (line 308, file "Code")

Even after, I changed the header back to the original ('Representative, Organization'), I am still unable to sync properly. When I run sync now, it only yields rows inputted after a certain time-period or the newest rows. Any idea what might be going on? Here's my fusion table for reference: https://www.google.com/fusiontables/DataSource?docid=1GjBak1ySMdbo85PohEfgckygrUtD_ssstg6nXY43#map:id=3

FYI: This table was also merged with a color code spreadsheet to create this map which is the one I distribute. https://www.google.com/fusiontables/DataSource?docid=1lkJv8-kXZbEjP4wK_t1w3y7zne6cEpIC5tcrfXpW#map:id=3

@EdmurHuq
Copy link

EdmurHuq commented Jul 19, 2016

Hi Chris, I followed your instructions to the T and for some reason the additional tab "Sync Spreadsheet to Fusion Table" did not show up. Any ideas?

@tonysim93
Copy link

Hi Chris,

I was wondering if there is a script function to update fusion tables from a different SHEET (Not Spreadsheet).

For instance, rather than syncing my "Sheet1" raw data to fusion tables, I want to sync my "Sheet2" analytical data (which contains formula columns derived from the raw data), and update them real-time with my fusion tables. Please see attachment for reference of sheets.
rawdata
analyticaldata

@shanirabi
Copy link

Hi, thanks for the explanation.
Is there any way to sync from fusion table to google spreadsheet and not the other way around?

Thanks a lot!

@mattcojones
Copy link

Firstly, thanks for this, it's been a huge help.

One question - I have a date field in my Google Sheet. However, when the data moves to the Fusion Table it is no longer in a date format. It looks like a date, and even if I manually change the Fusion Table field to a date, it doesn't function as a date e.g. if I use a filter in the Fusion Table. Anybody able to recommend a fix?

Thanks.

@maharsantoso
Copy link

Many thanks sir!

It's help me

@cv26
Copy link

cv26 commented Jul 10, 2017

Hi, Thanks for the great code and explanation.

I've copy and pasted the exact code and the Sync Spreadsheet to Fusion Table option does appear on the menu bar next to help.

My issue is that I'm getting a "FusionTables" is not defined error when I try to sync the two. Any help on this error would be much appreciated!

@paulhastings0
Copy link

@cv26 I believe the issue you have is addressed by @mnorelli a few comments above. Here's the direct link. https://gist.github.com/chrislkeller/3013360#gistcomment-1757714

@carpano
Copy link

carpano commented Oct 23, 2017

Thank you very much for the sharing @chrislkeller.
Everything works perfectly well, very usefull for a non programmer.
Your post is really awesome and the description, step by step, is just perfect.
Have a nice day !

@douglasg14b
Copy link

douglasg14b commented Nov 10, 2017

Sadly this causes the Content has a different number of columns than the table (line 566). to always appear, even though the columns are exactly the same.

If I actually add an additional column so they are not the same, the error changes to: Content has a different number of columns than the table (line 1).

@volcomlov
Copy link

volcomlov commented Nov 16, 2017

hi all, i want to ask

the command made above has been successful I run
but there are few constraints
when I enter data into a google sheet column, I use enter for every new paragraph but when I open it through the fusion table enter on every new paragraph is lost.
anyone can help for my problem? how to create a fusion table look just like google sheet.

Thanks

2
1

This is my script on sheet
var TABLE_ID = '1gnhBi2ntXxJNf6FMW99DiN0F75itBTqvQLl3VtNA';

var FIRST_DATA_ROW = 2;

var REQUIRE_SAME_COLUMNS = false;

function sync() {
var tasks = FusionTables.Task.list(TABLE_ID);

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)');
}
}

function convertToCsv_(data) {

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) {

    value = '"' + value.replace(/"/g, '""') + '"';
    data[row][col] = value;
  }
}

if (row < data.length - 1) {
  csv += data[row].join(',') + '\r\n';
}
else {
  csv += data[row];
}

}
return csv;
}

@jjparrilla
Copy link

Someone has it already for 2018??? thanks

@mathsbeauty
Copy link

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.

@gscalf
Copy link

gscalf commented Feb 26, 2018

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?

@shash66
Copy link

shash66 commented Mar 5, 2018

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.

@csanders23
Copy link

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.

@roziemd
Copy link

roziemd commented Oct 10, 2018

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;
    }

@EvilCookie666
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment