Copy rows from spreadsheet to fusion tables with a script

August 17, 2011 1:42 AM

How can I programaticaly copy ranges from google docs spreadsheet to a fusion table?

I would like to create an apps script in a google docs spreadsheet that will periodically copy rows from the sheet and insert them into a fusion table. Below I've pasted my best attempt, but it should be noted that I really want it to be posting a 2 column range, not that pair of values. That is just a placeholder.

This link here describes how to talk to fusion tables. I think this is the correct syntax for the POST request. http://code.google.com/apis/fusiontables/docs/developers_guide.html#Inserting

But I dont understand how to write the script to achieve this. I've messed around with it a bunch and I think I have these problems

-I'm not forming the post request correctly -I'm missing some sort of authentication step.

I am a total newb and what I have so far here is copy pasted below . Also below are a few links that I believe, together, contain the answer, but my understanding is too weak to implement it.

This guy seems to have figured out how to write an app script to send post requests http://blog.vivekhaldar.com/post/428652690/google-apps-script-spreadsheets-mashup-hub

This seems to be important
http://code.google.com/googleapps/appsscript/class_urlfetchapp.html

these people seem to be doing somthing very similiar, but I cant figure out how to make it work
http://groups.google.com/group/fusion-tables-users-group/browse_thread/thread/99db4db33e405f01


function deet() {
var advancedArgs = {
method: "post",
payload: "?sql=" + "INSERT INTO 1299801(Text, Number) VALUES ('Blue Shoes', 50)",
headers: {"Authorization": "Basic <base64 encoding of your username:passwd"}};
var response = UrlFetchApp.fetch(
"https://www.google.com/fusiontables/api/query",advancedArgs);

}
Thanks in advance!

posted by Popcorn to computers & internet (3 answers total)

Your function certainly posts. This example shows how to handle authentication. Other than that, I'd suggest removing the ? from your payload string and checking what response you get using Logger.log(response.getContentText()); or Browser.msgBox(response.getContentText()); or Browser.msgBox(response.getHeaders());
posted by Monsieur Caution at 2:24 AM on August 17, 2011

Thanks. What you said is a little over my head. I'm not a coder.

Are you saying that my function is fine and the issue is with the authentication?

Why should I remove the "?" from the payload? Are I not trying to get all those little chunks to add up to something like this, where there is a "?" between "query" and "sql"

https://www.google.com/fusiontables/api/query?sql=SELECT ROWID FROM 274409 WHERE Product='Red Shoes'

I see that that example handles authentication, but I don't get what I need to do with it to make it work. Do I put all three of those functions in the script window as is? How do I get the three of those functions to work together?

The syntax of all these examples confuses me also, how do I know what is code and what is an instruction to type something? For example, when it asks for a base64 encoding, I assume I'm supposed to put something in there, what does it want?

How about the authentication functions that you linked to (below)? Am I supposed to put my actual login credentials in there or are those references to somewhere else that has my login?


function getGAauthenticationToken(email, password) {
password = encodeURIComponent(password);
var response = UrlFetchApp.fetch("https://www.google.com/accounts/ClientLogin", {
method: "post",
payload: "accountType=GOOGLE&Email=" + email + "&Passwd=" + password + "&service=fusiontables&Source=testing"
});
var responseStr = response.getContentText();
responseStr = responseStr.slice(responseStr.search("Auth=") + 5, responseStr.length);
responseStr = responseStr.replace(/\n/g, "");
return responseStr;
}

function getdata(authToken) {
query = encodeURIComponent("SHOW TABLES");
var URL = "http://www.google.com/fusiontables/api/query?sql=" + query;
var response = UrlFetchApp.fetch(URL, {
method: "get",
headers: {
"Authorization": "GoogleLogin auth=" + authToken,
}
});
return response.getContentText();
}
posted by Popcorn at 3:27 AM on August 17, 2011

That's a lot of questions. The short of it is you're not handling authentication (yes to putting your username/password into the spaces in the example), and a question mark separates the URL host and path from the query string of a get request rather than a post request, which has no query string and instead a list of payload items on a new line. Sticking lines like Browser.msgBox(response.getHeaders()); after your fetch will give you some output to let you see what's happening and how successful you are. A developer for this would probably cost $20-$60 at oDesk.com. Just search for web programmers with a 1 hour minimum.
posted by Monsieur Caution at 7:35 AM on August 17, 2011