OAuth, OAuthConfig, & Google Apps Script (Fusion Table API - SQL Insert Problems)

Question

I am developing a google apps script function that will insert a row into a fusion table. Most of my fusion table interface works fine but this is my first attempt at an sql 'POST' query. Based on the documentation here I should be able to put the sql statement within the POST body. I've reduced myself to the OAuth Playground for troubleshooting and I keep getting the following error. I've been trying to use the sql insert statement sql=INSERT INTO {fusionId} (HeadingName) VALUES (ValueOne) and I have tried various variations of this statement to no avail. Please help me determine the proper syntax or method to do this.

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "required",
    "message": "Required parameter: sql",
    "locationType": "parameter",
    "location": "sql"
   }
  ],
  "code": 400,
  "message": "Required parameter: sql"
 }
}

Once I figure this out, I then need to duplicate the playground operation within my code, which (I think) will look something like this:

function fusionRequest(methodType, sql, oAuthFields) {
  OAL.googleAuth(oAuthFields);
  var options =
    {
      oAuthUseToken : "always",
      oAuthServiceName : 'fusiontables',
      method : "POST",
      payload : "sql=INSERT INTO {fusionId} (Heading) VALUES (ONE)",
      contentType : "application/json"
    };
Logger.log(options)
  var fetchResult = UrlFetchApp.fetch(oAuthFields.queryUrl, options);
  return JSON.parse(fetchResult.getContentText());  
}

I've been thinking it could also be a problem with the content-type, but I am at a loss. Please help.

Update #1 By using Sanya's advice I was able to get the request working in the OAuth Playground. However, I'm still struggling with the request in the code. I am getting repeated requests for authorization when running the code (or a general OAuth Error when debugged). From my previous experience here, I believe this means that there is still something wrong with my payload. Any advice on this would be much appreciated.

var oAuthFields = {
  'clientId' : '523606257547.apps.googleusercontent.com',
  'scope' : 'https://www.googleapis.com/auth/fusiontables',
  'fetchUrl' : 'https://www.googleapis.com/fusiontables/v1/',
  'clientSecret' : 'L-f8DgwK4rs7Qmw9k5IFL7lZ',
  'fusionId' : '1b4kT_aYRfNBy8ZPtSZqhQUqVSVIYj_QWiBmjXXI',
  'service' : 'fusiontables',
  'queryUrl' : 'https://www.googleapis.com/fusiontables/v1/query/'
};
function fusionRequest(methodType, sql, oAuthFields) {
  OAL.googleAuth(oAuthFields);
  var options =
    {
      oAuthUseToken : "always",
      oAuthServiceName : 'fusiontables',
      method : "POST",
      payload : "sql=INSERT INTO {fusion id} (\'Heading\') VALUES (\'ONE\')",
      contentType : "application/x-www-form-urlencoded"
    };
  var fetchResult = UrlFetchApp.fetch(oAuthFields.queryUrl, options);
  return JSON.parse(fetchResult.getContentText());  
}

For context, the googleAuth() function and overall layout of this function is the same as a function I use to add columns in the fusion table (which works).

SandorRacz SandorRacz · Accepted Answer · 2013-03-29T13:19:38

The documentation is faulty and there are some details missing.

You can use the sql=... parameter in the request url. The ContentType header usually is application/json but the API probably accepts other ContentTypes too. In this case you're limited in 2048 characters URL length.

You can also use sql=... in the POST body. In this case you have to set the ContentType must be application/x-www-form-urlencoded. You are limited to 500 INSERT statements in 1 request.

Another option is to use the importRows method. Here you will use a CSV as the POST body. In this method you're limited by 100MB uploaded data. The ContentType must be "application/octet-stream". Be careful: the url is different than the above: https://www.googleapis.com/upload/fusiontables/v1/tables/tableId/import. Also if you provide non UTF-8 characters in the body you must give an &encoding=... url parameter More details here: importRows reference

OAuth, OAuthConfig, & Google Apps Script (Fusion Table API - SQL Insert Problems)

2 Answers