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.

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

This seems to be important

these people seem to be doing somthing very similiar, but I cant figure out how to make it work

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(


Thanks in advance!
posted by Popcorn to Computers & Internet (3 answers total) 1 user marked this as a favorite
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" 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("", {
method: "post",
payload: "accountType=GOOGLE&Email=" + email + "&Passwd=" + password + "&service=fusiontables&Source=testing"
var responseStr = response.getContentText();
responseStr = responseStr.slice("Auth=") + 5, responseStr.length);
responseStr = responseStr.replace(/\n/g, "");
return responseStr;

function getdata(authToken) {
query = encodeURIComponent("SHOW TABLES");
var URL = "" + 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 Just search for web programmers with a 1 hour minimum.
posted by Monsieur Caution at 7:35 AM on August 17, 2011

« Older Workplace culture in the US   |   Who is the photographer from Los Angeles with the... Newer »
This thread is closed to new comments.