I have a Powershell script that calls a Google App Script function.
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.
The Authorization Scopes Documentation says,
"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"."
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.
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 :(
A few considerations:
The function that the run method calls by Powershell just works fine when I run it directly from Google Script Editor.
The script project is deployd as an API executable
Google Apps Script API is enabled in the GCP project
It is associated to a Standard GCP project
The OAuth credential is Web Application type
The script for writing and reading values from Powershell to Google Sheets works fine
Google script:
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
}
manifest file:
{
"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 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
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.
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 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.