How to authorize scope for GAS to edit Google Spreadsheets

Question

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:

1. The function that the run method calls by Powershell just works fine when I run it directly from Google Script Editor.

2. The script project is deployd as an API executable

3. Google Apps Script API is enabled in the GCP project

4. It is associated to a Standard GCP project

5. The OAuth credential is Web Application type

6. 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

Answer 1

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.

Flow:

1. Link the Cloud Platform Project to Google Apps Script Project. Ref
2. Install for running the GAS function with the scripts.run method in Apps Script API. Ref
3. 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.

4. Put the following script for testing to run. This is used for 1st test of Apps Script API.

   function test() {
     return "ok";
   }
   

5. 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());
   }
   

6. 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.

   • When an error occurs, please confirm the settings of Apps Script API. In this case, it can be said that the GAS script is correct.
   • When no error occurs, please test to run the function you want to run. In this case, the required scopes have already been included in the access token. By this, the issue of scopes can be avoided.
7. When above test is done and your script for using Apps Script API works, please retrieve the refresh token using the scopes. The scopes can be seen at the script editor. By this, the valid access token can be retrieved by the refresh token. And your script can be used at the local PC.

References:

• Linking Cloud Platform Project to Google Apps Script Project
• Executing Functions using the Apps Script API