Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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);
};

marcomow commented Jul 9, 2013

Hey, thanks for the awesome job!!!

I'd suggest you to change line 114 with

var queryPrepend = "INSERT INTO " + tableID + " (" + headers.join(",") + ") VALUES ('";

Because to me was causing error 400, because it couldn't formulate the query.
So, just without putting quotemarks on headers (eg. instead of 'Header', Header), it run.

Again thanks for the awesome template, let me know what you think about this edit! :D

Owner

chrislkeller commented Sep 23, 2013

Three months later I remembered you added this. Thank you.

Adjustment made at line 114. I left the original in for a spell in case.

Chris K.

heshow commented Oct 9, 2013

Hello!

I have been looking for something like this for a long time!! Thanks Guys

I have one issue with the updateFusion function though

when I run the updateFunction I get the following error:

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

and this what i have in line 79:

var response = UrlFetchApp.fetch(URL, {
method: "post",
headers: {
"Authorization": "GoogleLogin auth=" + authToken,
},
payload: "sql=" + query
});
return response.getContentText();
};

I would really appreciate it if you could please spear me your time and let me know what I am doing wrong?

Thank you

Heshow

PS - It seems like the delete query works because when I run the code it deletes the existing data in Google Fusion Table but it doesn't insert new data into the GF table from the Gsheet.

Hi, can you help me?

this happens to my script:

Error en la solicitud para el código devuelto 400 de https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyAU1k1r_YbMVKVyBDzEGPHhG_aJG7Dmd-4&. Respuesta del servidor truncada: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (usar la opción muteHttpExceptions para examinar toda la respuesta)

Thanks

I've got the same error as aalvaradobarajas.
Any suggestions?

The case is that I have more than 3 coloumns..

Hi and Thank you very much. I appreciate your effort and sharing. I am unsuccessful though to update to fusion tables and reveive the following error code:

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)

Could you kindly help me to fix this.
Regards
Jaco

Hi
I have resolved the above issue thank you. I discoverd that I did not read all your instructions thorougly enough and the issue was due to certein cells in column A being empty. But now I have another error code. Please help with this one:

Sorry about the above going through before I finished, I accidentally hit the enter key. Well, here is the error code below:

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyBZ7cNowvyJtmJFUTuWy-trvOb_aKkz_cE& 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)

What it does is delete all my table data already there, but then it does not update anything and gives the error above.

MorgrimX commented Nov 4, 2013

All but the update function worked.

"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=AKgnsbuxBXbcxmpYifEqDDgyv9RNMHVqos-u7nz2EtXDH4YaDo_EWdY... (use muteHttpExceptions option to examine full response)"

Any suggestions?

MorgrimX commented Nov 4, 2013

After ensuring column A in the table had no blank entry, I got the same error message shown in my previous entry above.

MorgrimX commented Nov 6, 2013

I just noticed that Google was auto-emailing me about a suspicious Client login. I followed their suggestions. After indicating I was the one trying the login, I was taken to a page that said simply to try it again. I did, and I happily got past the client login error and now have an Invalid Query:

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyD2jIZ2OWM3_5DTWFuaSU4JZmB2hnhve78& 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)

I don't see where or how I can use muteHttpExceptions option. do you?

Also, I noticed on line 77 you are appended an ampersand to the end of the fusion table API key. That shows up in the error message. It strikes me as odd.

P.S. thank you for your quick response. the web page suggests you haven't been here for over a month, which seems very misleading.

MorgrimX commented Nov 6, 2013

Nagging issue. I am able to manually add (all) rows from my spreadsheet to my (emptied) fusion table with no problem. I am troubled, though, because my spreadsheet actually contains 7sheets. The addmenu() code adds a menu item to each sheet even though we intend to use it for only one sheet. Is that an issue with your code. Should there be only one sheet? Does it even make a difference as long as I select Update Fusion table when I'm using the intended sheet.

I just notice the Get Active Spreadsheet function, so I presume it works for the correct sheet.

Did anyone found solution to the Bad Query request. This is what I am getting.
{
"error": {
"errors": [
{
"domain": "fusiontables",
"reason": "badQueryCouldNotParse",
"message": "Invalid query: Parse error near 'null' (line 0, position -1).",
"locationType": "parameter",
"location": "q"
}
],
"code": 400,
"message": "Invalid query: Parse error near 'null' (line 0, position -1)."
}
}

Any help would be highly appreciated.

Hello,

My website is located here: http://www.geographyofpizza.com/home

As, you can see I am collecting geographic data and have a Google Form linked to a Fusion Table. This website originally helped me and it has been recently updated http://fusion-tables-api-samples.googlecode.com/svn/trunk/FusionTablesFormSync/docs/reference.html to incorporate the API. I followed the complete directions using this code here:http://fusion-tables-api-samples.googlecode.com/svn/trunk/FusionTablesFormSync/src/formsync.js and am still having issues similar to the previous comments in this thread of the Bad Query request:

Truncated server response: {
"error": {
"errors": [
{
"domain": "fusiontables",
"reason": "badQueryCouldNotParse",
"message": "Invalid query: Column `"Othe... (use muteHttpExceptions option to examine full response)

I tried to incorporate: UserProperties.deleteAllProperties(); to see if that works and had no luck. Does anyone have any suggestions to get this Google Form to Sync With my Fusion Table?

Any help would be greatly appreciated :)

jcardi commented Dec 19, 2013

Hello, I'm a user of this great amazing script, which worked for me as recently as July 23. I'm now getting this error, which seems similar to others. Is there any solution??

I hope so, thank you so much!!

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyC766K5_Hwgzvs...asTAoNS7k& 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)

Can some one help, please. Stuck with that error from long :(

I have a similar problem:

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyAijL_MG8Wp1YDP98JFlJFNQUS-cq6ShLE& 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)

MorgrimX commented Feb 1, 2014

I have not solved my bad authentication error that I reported above many months ago. So, I have been doing an update manually. I believe I understand why the update is failing on the fusion table.

After loading the data, I then manually summarized the data. I update the data manually every day. I delete all of the rows and reload data from my source spreadsheet. The option to delete all rows is greyed out and not available as long as the data summarize function is in effect. So, I learned quickly that I first had to go into the Summarize function and select the "Clear Summary" button.

After doing that I am able to delete all rows and then import more rows.

I can't automate this since I have no idea where one finds the fusion table commands to accomplish the Clear Summary, Delete all rows, and import new rows functions.

This, I believe, is at the heart of a lot of error comments here and should be explored.

Google is not allowing authentication and sending emails warning of suspicious login attempts originating from Mountain View(!) when I run the script. Bit stumped about how to get around this at the moment...

sheeloo commented Mar 1, 2014

Solution to problem;
[ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)

I discovered that I was getting the error mentioned above if I had spaces in my column names. One needs to put single quotes around such column names.

To do this add the following loop [in function updateData(authToken, tableID)]
for (var i in headers) {
headers[i] = "'" + headers[i] + "'";
}
after the statement
var headers = data[0];

Thanks chrislkeller for a wonderful solution and explanation.

I have the error
Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyBNpeTYvF66WtJyLiVkqYWea9XQF_CrFPI& returned code 403. Truncated server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured. Please us... (use muteHttpExceptions option to examine full response).
I have never use API or fusion tables before, so dont know what to do.

Regards

Shelly

lachelt commented Apr 18, 2014

I got past the badQueryCouldNotParse problem.
Now the script deletes my table contents, and gives this error:

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyA82xLv8imBsHcIuSGLzGTzTQv7okXsue8& returned code 503. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "serverError", "message": "Internal error. Please try again." }... (use muteHttpExceptions option to examine full response)

And suggestions would be appreciated.

Has anyone gotten a "Range not found." error message after trying to change the range?

wstacey commented Jun 2, 2014

To get past the "Range not found" error i commented out the

var check = ss.getRangeByName(rangeName)
if (check != null) {
ss.removeNamedRange(rangeName);
}

Line from the setRangeFusion() function, after you run it once (and that "rangeName" actually gets set) you can uncomment that line.

However i'm also running into the

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyA82xLv8imBsHcIuSGLzGTzTQv7okXsue8& returned code 503. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "serverError", "message": "Internal error. Please try again." }... (use muteHttpExceptions option to examine full response) error message now.

Cesare1 commented Aug 4, 2014

I had this report while debuging:

Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyAycTAhZp_LqIGvq4a8d-OjF_jkzIb1yzs& 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 82, file "Code")

What should I do on line 82?

Owner

chrislkeller commented Jan 22, 2016

This script and the instructions have been updated as of Jan. 21, 2016. I'm not sure how it will work in the wild, but please file away any issues in the comments.

Hi. I see the following error when clicked "update fusion table" in Google spreadsheet:

ReferenceError: Object "FusionTables" is not defined. (Line 22, code file)

what could be the problem?

You have not enabled the FusionTable service. Basically, from chrislkeller's guide, one step is missing for enabling google's fusion service. Follow these instructions, searching for Fusion Table (turn Fusion Table toggle switch to ON): https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services

I followed these instructions to the letter and all the credentials, Developers Console etc look OK....but when I run the script (and re-open the spreadsheet) the menu button is not created. The data does not appear in the fusion table either. If I run the script manually from the script editor it seems to run OK with no errors, but again no data appears in the fusion table. I also enabled Fusion Table service as suggested above by dimandili. Has anyone got any tips to help me troubleshoot this? Thanks

Hi Chris,

Thanks for the script, I found someting similar here.

That script changes the fusion table when a form is filled or changed and thus the sheet gets changed=;
It would be nice if you can incorpaerate this in your script as I always am strugeling with my rowid.
So a change in the sheet should be reflected in the fusion table to.
In my real life situation I use a script to get lines from a GSheet into the GForm response repository.
Then i use the script from the first line it's not ideal. In my case yours would work better if it triggered a sync when the GSheet was changed.
Furthermore it seems this script make Fusion Table duplicates?

Thanks for reading

3dfh commented Mar 10, 2016

Content has a different number of columns than the table (line 297).
what can i do?

thanks for this tutorial!

Same issue than 3dfh here.

Content has a different number of columns than the table (line 1998). (ligne 104, fichier "Code")

104 FusionTables.Table.replaceRows(TABLE_ID, csvBlob, { isStrict: REQUIRE_SAME_COLUMNS, startLine: FIRST_DATA_ROW - 1 });

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

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...!)

dan-fahey commented May 11, 2016 edited

@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 commented Jul 1, 2016 edited

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 commented Jul 19, 2016 edited

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?

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

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!

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.

Many thanks sir!

It's help me

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!

@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

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