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).
SUCCESS! After lots of research, I have resolved the problem. It was not a payload or contentType issue (in fact, I found that the .fetch automatically defaulted to the "application/x-www-form-urlencoded" encoding). The issue was with the authorization. I used James Ferreira's Fusion Table Library as a model and inserted Logger.log(UrlFetchApp.getRequest(url, fetchArgs)) before the UrlFetchApp.fetch(url, fetchArgs).getContentText() in both my code and the library's.
//log entry for library
{oAuthServiceName=fusion, useIntranet=false, followRedirects=true, oAuthUseToken=always, payload=sql=INSERT INTO 1b4kT_aYRfNBy8ZPtSZqhQUqVSVIYj_QWiBmjXXI ('Heading', 'Heading 2') VALUES ('NONE','TWO'), method=POST, validateHttpsCertificates=true, contentType=application/x-www-form-urlencoded, url=https://www.google.com/fusiontables/api/query}
//log entry for my code
{oAuthServiceName=fusiontables, useIntranet=false, followRedirects=true, oAuthUseToken=always, payload=sql=INSERT+INTO+1b4kT_aYRfNBy8ZPtSZqhQUqVSVIYj_QWiBmjXXI+('Heading',+'Heading+2')+VALUES+('NONE','TWO'), method=POST, validateHttpsCertificates=true, contentType=application/x-www-form-urlencoded, url=https://www.googleapis.com/fusiontables/v1/query}
I then compared the log of the working library to that of my own and found three differences.
After some experimentation, I determined that ...