如何授权GAS范围编辑Google Spreadsheets [英] How to authorize scope for GAS to edit Google Spreadsheets
问题描述
我有一个Powershell脚本，可以调用Google App Script函数.
I have a Powershell script that calls a Google App Script function.
运行Powershell脚本时，我可以在我的GCP项目的错误报告中看到以下错误:
When I run the Powershell script I can see the following error on the Error Reporting on my GCP project:
Exception: You do not have permission to call SpreadsheetApp.getActiveSpreadsheet. Required permissions: (https://www.googleapis.com/auth/spreadsheets.currentonly || https://www.googleapis.com/auth/spreadsheets)
at toSpreadsheet (Código:3)
at fromPS (Código:14)
我了解到我必须授权范围，因此我一直在尝试通过编辑清单文件来做到这一点.
I have understood that I have to authorize the scope, so I've been trying to do that by editing the manifest file.
授权范围文档说，
"在授权流程中，Apps脚本会向用户显示所需范围的人类可读描述.例如，如果您的脚本需要对电子表格进行只读访问，则清单可能具有范围 https://www.googleapis.com/auth/spreadsheets.readonly .在授权流程中，具有此作用域的脚本会要求用户允许该应用程序查看您的Google Spreadsheets"."
"During the authorization flow, Apps Script presents human-readable descriptions of the required scopes to the user. For example, if your script needs read-only access to your spreadsheets, the manifest may have the scope https://www.googleapis.com/auth/spreadsheets.readonly. During the authorization flow, a script with this scope asks the user to allow this application to "View your Google Spreadsheets"."
就我而言，我编辑了清单文件appscript.json以添加范围 https://www. googleapis.com/auth/spreadsheets ，然后将其保存，将Google App Script项目发布为API可执行文件，最后我再次运行Powershell代码，但仍然遇到与上面相同的错误.在所有这些流程中，没有要求我允许任何操作.我无法理解授权脚本具有所需权限所缺少的内容.
In my case I edited the manifest file appscript.json to add the scope https://www.googleapis.com/auth/spreadsheets, then I saved it, published the Google App Script project as API Executable, and finally I run the Powershell code again, but I still get the same error as above. During all this flow, I was not asked to allow anything. I cannot understand what is missing authorizing the script have the required permission.
我还在OAuth同意屏幕上添加了电子表格范围，但似乎没有任何区别.我怀疑我应该使用服务帐户来完成此操作，因为从Powershell脚本调用了我在Google上的脚本，因此我看不到要通过OAuth客户端验证的方法.我不想相信这一点，因为了解OAuth2的配置花了我很多时间:(
I also added the spreadsheets scope to OAuth consent screen, but it seems to do not make any difference. I am suspecting I should use a Service Account to accomplish that since I see no way to go through a OAuth Client Verification since my script on Google is called from Powershell script. I dont want to believe on that because getting to know how config OAuth2 took me a lot of time :(
一些注意事项:
-
当我直接从Google脚本编辑器直接运行该方法时，Powershell调用run方法的功能就可以正常工作.
The function that the run method calls by Powershell just works fine when I run it directly from Google Script Editor.
脚本项目被部署为API可执行文件
The script project is deployd as an API executable
在GCP项目中启用了Google Apps脚本API
Google Apps Script API is enabled in the GCP project
它与标准GCP项目相关
It is associated to a Standard GCP project
OAuth凭据是Web应用程序类型
The OAuth credential is Web Application type
用于将值从Powershell写入和读取到Google Sheets的脚本很好用
The script for writing and reading values from Powershell to Google Sheets works fine
Google脚本:
function toSpreadsheet(text2write)
{
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HIL_APP");
var LastRow = sheet.getLastRow();
for (var i = 1; i < LastRow; i++)
{
sheet.getRange(i+1, 8, 1).setValue(text2write)
}
return "myreturn"
}
function fromPS(params)
{
Logger.log(params)
var rtn = toSpreadsheet(params)
return rtn
}
清单文件:
{
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets"
],
"timeZone": "America/Argentina/Buenos_Aires",
"dependencies": {
},
"webapp": {
"access": "ANYONE",
"executeAs": "USER_DEPLOYING"
},
"exceptionLogging": "STACKDRIVER",
"executionApi": {
"access": "MYSELF"
},
"runtimeVersion": "V8"
}
Powershell代码:
Powershell code:
function doit{
$json = ".\client_id.json"
$jdata = get-content $json | convertfrom-json
<#
$jdata | ForEach-Object {
$_.PSObject.Properties.Value
}
#>
$ClientID = $jdata.web.client_id.ToString()
$ClientSecret = $jdata.web.client_secret.ToString()
$refreshToken = "1//04VvG_FTyDGhiCgYIARAAGAQSNwF-L9IrZ-o1kaZQQccvzL5m4TUTNz6b9Q4KCb16t4cH11gGCshWZWvgaCoMlg73FgpLAGOYTEk"
$grantType = "refresh_token"
$requestUri = "https://accounts.google.com/o/oauth2/token"
$GAuthBody = "refresh_token=$refreshToken&client_id=$ClientID&client_secret=$ClientSecret&grant_type=$grantType"
$GAuthResponse = Invoke-RestMethod -Method Post -Uri $requestUri -ContentType "application/x-www-form-urlencoded" -Body $GAuthBody
$accessToken = $GAuthResponse.access_token
$headers = @{"Authorization" = "Bearer $accessToken"
"Content-type" = "application/json"}
$spreadsheetId = "1htbeGlqZ4hojQBWl9fxE4nW_KZI9uVwi0ApzNOIbwnY"
$currentDate = (Get-Date).ToString('MM/dd/yyyy')
$currentTime = (Get-Date).ToString('HH:mm:sstt')
$json = @"
{
"range": "HIL_APP!A1:G1",
"majorDimension": "ROWS",
"values":
[[
"HIL_NAME",
"$env:ComputerName",
"$currentDate",
"$currentTime",
"$env:UserName",
"input from user",
"attempt"
],]
}
"@
$write = Invoke-WebRequest -Uri "https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/HIL_APP!A1:G1:append?valueInputOption=USER_ENTERED" -Method Post -ContentType "application/json" -Body $json -Headers @{"Authorization"="Bearer $accessToken"}
$read = Invoke-WebRequest -Uri "https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/HIL_APP!A1:G1" -Headers @{"Authorization"="Bearer $accessToken"}
$read
Write-Output "read: " ($read.Content | ConvertFrom-Json)
$scriptId = "1eF7ZaHH-pw2-AjnRVhOgnDxBUpfr0wALk1dVFg7B220bg_KuwVudbALh"
$json = @"
{
"function": "fromPS",
"parameters": ["myparam"],
"devMode": true
}
"@
$resp = Invoke-WebRequest -Uri "https://script.googleapis.com/v1/scripts/${scriptId}:run" -Method Post -ContentType "application/json" -Body $json -Headers @{"Authorization"="Bearer $accessToken"}
$resp
Write-Output "script response: " ($resp.Content | ConvertFrom-Json)
}
$error.Clear()
clear
doit
推荐答案
要使用Apps Script API运行Google Apps Script(GAS)功能，需要进行一些复杂的设置.在这种情况下，我想提出如下测试以运行GAS功能.此流程可能太小心了.
In order to run the function of Google Apps Script (GAS) using Apps Script API, it is required to do a bit complicated settings. In this case, I would like to propose for testing to run the GAS function as follows. This flow might be too careful.
- 将Cloud Platform项目链接到Google Apps脚本项目. 参考
- 通过Apps Script API中的scripts.run方法运行GAS功能的安装. 参考
- 将要运行的脚本放入Google Apps脚本的脚本编辑器.
- 在这里，请通过脚本编辑器运行该函数并确认脚本是否有效.这样，可以避免脚本的问题.
- Link the Cloud Platform Project to Google Apps Script Project. Ref
- Install for running the GAS function with the scripts.run method in Apps Script API. Ref
- Put the script you want to run to the script editor of Google Apps Script.
- Here, please run the function by the script editor and confirm whether the script works. By this, the issue of the script can be avoided.
放置以下脚本以进行测试运行.这用于Apps Script API的第一次测试.
Put the following script for testing to run. This is used for 1st test of Apps Script API.
function test() {
return "ok";
}
放置以下示例脚本以检索访问令牌.用于测试它.请在脚本编辑器中运行此命令，然后复制返回的访问令牌.
Put a following sample script for retrieving the access token. This is used for testing it. Please run this at the script editor, and copy the returned access token.
function getToken() {
Logger.log(ScriptApp.getOAuthToken());
}
使用获取的访问令牌测试运行test()的GAS功能.在这种情况下，通过替换$accessToken = $GAuthResponse.access_token来使用powershell脚本.
Test to run the GAS function of test() using the retrieved access token. In this case, the script of powershell is used by replacing $accessToken = $GAuthResponse.access_token.
- 发生错误时，请确认Apps Script API的设置.在这种情况下，可以说GAS脚本是正确的.
- 如果未发生任何错误，请测试运行要运行的功能.在这种情况下，所需的范围已经包含在访问令牌中.这样，可以避免范围问题.
参考文献:
- 将Cloud Platform项目链接到Google Apps脚本项目
- 使用Apps Script API执行功能
- Linking Cloud Platform Project to Google Apps Script Project
- Executing Functions using the Apps Script API
References:
这篇关于如何授权GAS范围编辑Google Spreadsheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
