Getting Google Apps Script to Authorize Fusion Tab

Question

I'm building a database that I hope to host in a Fusion Table and I'm developing some test functions for interfacing with the Fusion API. I'm using Google Apps Scripts and have relied heavily on other sources for this code. I've spent most of a day researching this problem and I am now stuck.

When running addNewColumn() in the script editor, it gets to the "fetch ready" log entry (as I can see in the log), it then goes through the authentication process (every time), then just stops (and never gets to the "fetch executed" log entry). There are no errors thrown yet it never completes the fetch command.

When running in debug mode, it goes through authorization then hangs at the fetch line. If i click 'step-in' I get an error that simply says "OAuth Error". I'm not really sure what to do here. Any help would be much appreciated.

function googleAuth() {
      var scope = "https://www.googleapis.com/auth/fusiontables";
      var service = 'fusion';
      var oAuthConfig = UrlFetchApp.addOAuthService(service);
      oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/"+
                 "OAuthGetRequestToken?scope="+scope);
      oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
      oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
      oAuthConfig.setConsumerKey('{consumer key}');
      oAuthConfig.setConsumerSecret('{secret}');
      return {oAuthServiceName:'fusion', oAuthUseToken:"always"};
}

function addNewColumn(){   
      var p = {
          'name' : "newColumn",
          'type' : "NUMBER"
      };

      Logger.log(addColumn(p));
}


function addColumn(parameters) {
  var url = "https://www.googleapis.com/fusiontables/v1/";
  var fetchArgs = googleAuth(); 

  fetchArgs.method = "POST";
  fetchArgs.payload = parameters;

  //if the fetch arg payload is set to null it will add an unnamed column
  //fetchArgs.payload = null;  

  url += "tables/"+"{table id}"+"/columns";
  url += '?key='+"{key}";

  Logger.log("fetch ready");

  var result = UrlFetchApp.fetch(url, fetchArgs).getContentText();

  Logger.log("fetch executed");

  return Utilities.jsonParse(result);  
}

---

Upate

I've simplified my functions to test and I've discovered that by simply inserting 'null' in place of the 'payload' variable in the payload field of the options body, the code will successfully create a nameless column in the fusion table. However, when I re-insert the payload variable - it stops working.

When not working, it either: asks to reauthorize every time I run it from the editor, or when run from a web app states "Error Encountered: Authorization is required to perform that action". How does the payload change authorization? I've cut and pasted the exact same payload into the OAuth2.0 playground and it works perfectly. Please help.

function googleAuth() {
      var oAuthConfig = UrlFetchApp.addOAuthService(service);
      oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/"+
                 "OAuthGetRequestToken?scope=" + scope);
      oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
      oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
      oAuthConfig.setConsumerKey(clientId);
      oAuthConfig.setConsumerSecret(clientSecret);
      return {oAuthServiceName:service, oAuthUseToken:"always"};
}

function addColumn() {
  googleAuth();
  var payload = 
    {
      name : "IT WORKED",
      type : "STRING"
    };
  var options =
    {
      "oAuthUseToken":"always",
      "oAuthServiceName":service,
      "method" : "post",
      "payload":payload
    };
  var url = fetchUrl + "tables/" + fusionId + "/columns" + "?key=" + apiKey;
  var fetchResult = UrlFetchApp.fetch(url, options);
  Logger.log(fetchResult.getContentText());
  return Utilities.jsonParse(fetchResult.getContentText());
}

---

Update #2

I have found a way that lets it run without any errors but it still does not assign the requested name and type to the new column. The most recent addition was specifying the content type as shown below. By forcing the contentType to 'application' or 'json', it will run but still doesn't pass the payload as intended.

  var options =
    {
      "oAuthUseToken":"always",
      "oAuthServiceName":service,
      "method" : "POST",
      "payload" : payload,
      'contentType' : 'application'
    };

Answer 1

SOLVED!!! The solution was twofold:

1. the payload had to be converted to a string via JSON.stringify()
2. The contentType had to manually be set to "application/json"

This is very different than the multiple tutorials I've found online but wanted to share my working solution.

function addColumn(authToken) {
  googleAuth();
  var payload =
    {
      name : 'YES',
      type : 'STRING'
    };
  var options =
    {
      payload : JSON.stringify(payload),
      oAuthUseToken : "always",
      oAuthServiceName : service,
      method : "POST",
      contentType : "application/json"
    };
  var url = fetchUrl + "tables/" + fusionId + "/columns";// + "?key=" + apiKey;
  var fetchResult = UrlFetchApp.fetch(url, options);
  Logger.log(fetchResult.getHeaders());
  Logger.log(fetchResult.getContentText());
  return Utilities.jsonParse(fetchResult.getContentText());

}

Answer 2

• The reason that this appears to be different that other urlFetch POST examples is because Fusion Table Service expects an actual JSON payload as the request "BODY". When you add a JSON object (without Stringify) as a "payload", UrlFetchApp will convert each name/value pair into a Url-Encoded Request String (i.e. name=YES&type=STRING). However when Payload is a String (as in your case as you are using stringify), UrlFetch will just URL-Encode the String you provide (in this case a JSON string). If you were "POSTING" to a service expected name/value parameters, then you would not use Stringify.
• Note: Just so you are aware and to clarify your other related question that you posted here , you are using Oauth 1.0 in this example (addOAuthService only supports Oauth1.0 Endpoints and protocols)
• One more Note: You are mixing Utilities.jsonParse and JSON.stringify. I would use JSON.parse to be consistent.

Answer 3

I had the same problem, trying to get and manipulate data from fusion tables.

There's a fusion table library in Google's notable script library: https://developers.google.com/apps-script/notable-script-libraries?hl=pt-BR

I linked that library, but it seems to be buggy. So (for debugging reasons) i copied the source code to my project and got it work:

https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/fusion-tables-class/fusion-source

Maybe you find your answers there.