UPDATE TO THE UPDATE: I’m not aware of a straight-up-set-it-and-forget-it sync between a Google Spreadsheet and a Fusion Table. But using the script and setting a trigger to run the script will get you close though.
John’s script will allow you to update a Fusion Table from a spreadsheet.
Then you can trigger the script in time increments by setting up a trigger, but there are still restrictions with using the trigger method.
There is also a script that will allow you to take data from a Google Form, which is sent to a spreadsheet and then synced to a Fusion Table:
Either of these methods are more efficient than updating spreadsheet, downloading a csv of the spreadsheet, deleting Fusion Tables rows and then importing the csv to the Fusion Table.
UPDATED: Clarified the need to authenticate your email address before running the Update Fusion function in the script.
This week, John M. from the Fusion Tables Google Group has offered up his version of an Apps script that updates a Fusion Table from a spreadsheet.
I’ve also posted the script to a gist here. And an updated version — that works off the active sheet — lives here.
Then John McGrath posted an Apps script to GitHub that updates a Fusion Table from a spreadsheet.
The two methods sound similar, but they do operate in different ways. First I’ll walk through how I got John M.’s script up and running. Then I’ll illustrate some differences.
For illustrating the script, I set up a simple spreadsheet with three, imaginatively-titled columns.
Then I create the Fusion Table based off that spreadsheet.
Once created I grab the Fusion Table’s numeric ID by going to File —> About. This ID is 2793906.
Back at the spreadsheet now, and go to Tools —> Script Editor. I’ve only just learned about the potential here, but needless to say there is a high ceiling. Anyway, paste your code here and click save. You will be prompted to give the project a name.
Go back to the spreadsheet again and head to Tools —> Script Manager. Here you’ll get a window will all of the functions available to you. Highlight onOpen and click “Run” and then “Close.” You should now have a menu item Called “Fusion Tables” staring at you.
Click the menu item you find three options: Update Fusion Table; Change Email Information (authentication) and Change Range of Data to be Sent.)
The second option allows you to authenticate or “grant access” to send data from a spreadsheet to a fusion table. This is a must do before proceeding and is necessary for the script to work
After you authenticate you will want to to “Change Range of Data to be Sent.” To do this, simply highlight the cells you will be POSTing and click the menu item.
From there, you are ready for Update Fusion Table which should be self explanatory. That will send the data housed in the spreadsheet to Fusion Tables.
You will get a confirmation box. Go ahead and click OK. You will get another cofirmation box saying the range has been changed.
Now just add some information to your spreadsheet and click the Update Fusion Table menu item. If I wanted to change something around I can just make the changes and click Update Fusion Tables.
Sit back and enjoy this moment.
So what are the differences between these two scripts. After a cursory runthrough of both, here’s what I’ve found.
Apps Script to use Form —> Spreadsheet —> Fusion Tables
- Requires “a role account” because script editor will display unencrypted password.
- Data is sent via a form to the spreadsheet and then POSTed to the Fusion Table.
- Data added via the form can be updated in the spreadsheet and synced with Fusion Tables. But the script does not support adding new data to the spreadsheet and POSTing it to the Fusion Table.
- A sync between the spreadsheet and the table can be added — minute by minute, or hourly — and those changes will be reflected on the table.
- It’s not possible to modify the form — adding another question — or modify the table’s schema once the script is being used.
Apps Script to use Spreadsheet —> Fusion Tables
- Authenticates against user’s account.
- If I add columns to the spreadsheet, I then need to add a column to the Fusion Table, which isn’t difficult, and once completed the script works fine. Perhaps we can figure out a function that would do that and add it to the menu items?