Last week Kathryn Hurley of the Google Fusion Tables team offered up an Apps script that basically allowed a user to submit data to a Fusion Table from a form, using Google Spreadsheets as a conduit.
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.
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.)
-
Update Fusion Table is self explanatory. That will send the data housed in the spreadsheet to Fusion Tables. The second allows you to authenticate or “grant access,” which is necessary for the script to work. But before we do anything, I want to “Change Range of Data to be Sent.” Simply highlight the cells I will be POSTing and click the menu item.
-
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?
Cross-posted from Chris L. Keller …