Copy rows from spreadsheet to fusion tables with a script
August 17, 2011 1:42 AM Subscribe
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!
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!
Response by poster: 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
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
posted by Monsieur Caution at 7:35 AM on August 17, 2011
This thread is closed to new comments.
posted by Monsieur Caution at 2:24 AM on August 17, 2011