OAuth，OAuthConfig，& Google Apps脚本（Fusion Table API - SQL插入问题） [英] OAuth, OAuthConfig, & Google Apps Script (Fusion Table API - SQL Insert Problems)

问题描述

我正在开发一个谷歌应用程序脚本函数，它会将一行插入到融合表中。我的融合表接口大部分工作正常，但这是我第一次尝试sql'POST'查询。根据这里的文档，我应该能够将sql语句在POST正文中。我已将自己减少到 OAuth Playground 进行故障排除，并且不断收到以下错误消息。我一直在尝试使用SQL插入语句 sql = INSERT INTO {fusionId}（HeadingName）VALUES（ValueOne），并且我试过了这个语句的各种变体，无济于事。请帮助我确定正确的语法或方法。

  {
error：{
errors：[
 {
domain：global，
reason：required，
message：必需参数：sql， 
locationType：parameter，
location：sql
} 
]，
code：400，
 ：必需的参数：sql
} 
} 
  

然后我需要在我的代码中复制游戏操作，我认为这会是这样的：

 函数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（选项）
 var fetchResult = UrlFetchApp.fetch（oAuthFields.queryUrl，options）; 
返回JSON.parse（fetchResult.getContentText（））; 
} 
  

我一直认为它也可能是内容类型的问题，但我很茫然。请帮助。

更新＃1
通过使用三亚的建议，我可以在OAuth Playground中获得请求。不过，我仍在努力处理代码中的请求。运行代码时，我得到了重复的授权请求（或调试时出现一般OAuth错误）。从我之前的经验此处，我相信这意味着我的有效载荷仍然有问题。任何意见，将不胜感激。

  var oAuthFields = {
'clientId'：'523606257547.apps。 googleusercontent.com'，
'scope'：'https://www.googleapis.com/auth/fusiontables'，
'fetchUrl'：'https://www.googleapis.com/fusiontables/ '$'b'clientSecret'：'L-f8DgwK4rs7Qmw9k5IFL7lZ'，
'fusionId'：'1b4kT_aYRfNBy8ZPtSZqhQUqVSVIYj_QWiBmjXXI'，
'service'：'fusiontables'，
'queryUrl'： 'https://www.googleapis.com/fusiontables/v1/query/'
}; 
函数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）; 
返回JSON.parse（fetchResult.getContentText（））; 
 
 $ / code> 

对于上下文，googleAuth（）函数和这个函数的总体布局是与我用于在融合表中添加列的功能一样（可以工作）。 成功！经过大量研究，我已经解决了这个问题。这不是一个有效载荷或contentType问题（事实上，我发现.fetch自动默认为application / x-www-form-urlencoded编码）。问题在于授权。我使用James Ferreira的Fusion Table Library作为模型，并在 UrlFetchApp.fetch（）之前插入 Logger.log（UrlFetchApp.getRequest（url，fetchArgs）） url，fetchArgs）.getContentText（）在我的代码和库中。

  // log条目（'NONE'，'TWO'）的条目
 {oAuthServiceName = fusion，useIntranet = false，followRedirects = true，oAuthUseToken = always，payload = sql = INSERT INTO 1b4kT_aYRfNBy8ZPtSZqhQUqVSVIYj_QWiBmjXXI（'Heading'，'Heading 2'）VALUES ），method = POST，validateHttpsCertificates = true，contentType = application / x-www-form-urlencoded，url = https：//www.google.com/fusiontables/api/query} 
 
 //日志条目
 {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 / xw https://www.googleapis.com/fusiontables/v1/query} 
  
然后，我将工作库的日志与我自己的日志进行了比较，发现了三个不同之处。 
 
 
 
 
我认为这是一个区别（INSERT INTO与INSERT + INTO）。
 
请求的'service'参数不同（fusion与fusiontables ）。
 
 
 qpi查询网址不同。 （www.google.com/fusiontables/api/query与www.googleapis.com/fusiontables/v1/query）。
 
 
 
 
经过一番实验后，我确定了...... 
 
 
假设的编码与我的错误无关。
 
 
服务参数也是不相关的，我已经在各种文档中看到过，并且似乎都有效。
 
 
令人沮丧的是，这是个问题。 href =https://developers.google.com/fusiontables/docs/v1/using#insertRow =nofollow> api文档表示使用那个不工作的工具。 googleapis.com/fusiontables/v1/query用于OAuth2.0 OAuthConfig（我在GAS中使用的Bult-in授权工具）尚未迁移到2.0，因此文档已失效。我已提交迁移OAuthConfig的功能请求此处 。 请如果您还希望解决这个问题，请给这个线索一些爱。
 
 
 
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.

What I think is a difference of encoding for the sql statement ("INSERT INTO" vs. "INSERT+INTO").
The 'service' parameter of the request was different ("fusion" vs. "fusiontables").
The qpi query url was different. ("www.google.com/fusiontables/api/query" vs. "www.googleapis.com/fusiontables/v1/query).
After some experimentation, I determined that ...

The supposed encoding was irrelevant to my error.
The service parameter was also irrelevant. I have seen both in various documentation and either one seems to work.
Frustratingly, this was the issue. It's frustrating because the api documentation says to use the one THAT DOESN'T WORK. Apparently, www.googleapis.com/fusiontables/v1/query is for use with OAuth2.0. OAuthConfig (the bult-in authorization tool that I'm using in GAS) has not been migrated to 2.0 so there was a lapse of documentation. I have submitted a feature request to migrate OAuthConfig here. Please give that thread some love if you also want it to be addressed.


                        
这篇关于OAuth，OAuthConfig，& Google Apps脚本（Fusion Table API  -  SQL插入问题）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！