CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
aabujamra
aabujamra

Reputation: 4636

Required permissions: https://www.googleapis.com/auth/spreadsheets

I've created a simple code on appscript to get Google Drive file names out of the URL

function FileName (URL) {
  var ss = SpreadsheetApp.openByUrl(URL);
  return ss.getName();
}

When I run that I get an error:

Exception: You do not have permission to call SpreadsheetApp.openByUrl. Required permissions: https://www.googleapis.com/auth/spreadsheets (linha 6).

I've already enabled Drive and Sheets API in the Advanced Google Services area, so the "https://www.googleapis.com/auth/spreadsheets" should be ok, but it is not.

How could I make that work?

I've created a sample sheet in this link with the problem replication.

Upvotes: 2

Views: 5833

Answers (2)

stackwise82
stackwise82

Reputation: 1

What worked for me is just granting myself permission using a directive from https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes

Particularly: Auth Scopes Google Documentation

From this example, I just added the "oauthscopes" at the highest level in the appscript.json as it states. I deleted the email scope, kept the spreadsheet scope and deleted the ".readonly". When I hit save, and re-ran (from a function assigned to an "image over cells" button), Google prompted me to authorize permissions to my Apps Script file.

Upvotes: 0

Marios
Marios

Reputation: 27348

Explanation of the issue:

You are using a custom function.

It is very well explained in the official documentation:

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

As the message clearly states, you are not allowed to use services that require authorization such as SpreadsheetApp.openByUrl(URL) within a custom function.

There is also a table that particularly points out that SpreadsheetApp.openByUrl(URL); can not be used within a custom function:

enter image description here

Workarounds:

You can use regular functions in multiple ways:

  1. Implemented as custom menu buttons,
  2. Execute them from the script editor,
  3. Execute them via simple triggers,
  4. Create an addon menu etc.

Regular function version of your script:

function FileName () {
  var URL = "spreadsheet_url"
  var ss = SpreadsheetApp.openByUrl(URL);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  sheet.getRange('A1').setValue(ss.getName());
}

Upon execution (see screenshot), this script will set the value of cell A1 of Sheet1 to the name of the spreadsheet file.

enter image description here

I would advice you to look for the functions I used in this script in the official documentation to see what each of them does.

Update:

I just opened your sheet and saw that you are using SpreadsheetApp.openByUrl(URL); but this method works only for spreadsheets hence the class name SpreadsheetApp. If you want to open a file ID then you need to use getFileById(id). I see in the first column you have file Urls but there is not method to open a file by its URL. The id from a file like that:

https://drive.google.com/file/d/1d-2L5kGZWbUa_p7iLgBBa59QqZiyIhgp/view?usp=sharing

is:

1d-2L5kGZWbUa_p7iLgBBa59QqZiyIhgp

Therefore you need to use

var files = DriveApp.getFileById('1d-2L5kGZWbUa_p7iLgBBa59QqZiyIhgp')

A code snippet will be like that:

function FileName () {
  var URL = "1d-2L5kGZWbUa_p7iLgBBa59QqZiyIhgp"
  var file = DriveApp.getFileById(URL);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  sheet.getRange('B1').setValue(file.getName());
}

Please familiarize yourself with GAS because this question just became a question that needs more focus.

Upvotes: 7

Related Questions