CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetspermissionsgoogle-calendar-apicustom-function
Chip Luxury - E.W.A
Chip Luxury - E.W.A

Reputation: 11

Function from script editor works fine but not from sheets custom function

I wrote a function that adds an all-day event.

When the function running from script editor it's working fine, the all-day event is inserted to the calendar.

But when I'm running the function in the active spreadsheet =addevent(a1,b1,c1,d1)

I'm getting an error saying :

Exception: The script does not have permission to perform that action. Required permissions: (https://www.googleapis.com/auth/calendar || https://www.googleapis.com/auth/calendar.readonly || https://www.google.com/calendar/feeds) (line 11)

After searching the net I found how to add manually those permission by editing the oauthScopes in the JSON file:

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/calendar.readonly",
    "https://www.google.com/calendar/feeds",
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/spreadsheets"
    
  ],
  
  "timeZone": "censored",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "Calendar",
      "serviceId": "calendar",
      "version": "v3"
    }]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
  


}

But I am still getting the same error in the spreadsheet...

Upvotes: 0

Views: 886

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

Answer:

While it is not possible to run a script which requires authorisation directly from a cell in a Sheet, you can set up the script to run on button click instead.

More Information & Workaround:

As a button does not have the same authorisation restrictions as custom functions, you can use them to run code with the scopes you need. To set this up:

  1. Go to the Insert > Drawing menu item and create a shape; any shape will do, this will act as your button.
  2. Press Save and Close to add this to your sheet.
  3. Move the newly-added drawing to where you would like. In the top-right of the drawing, you will see the vertical ellipsis menu (⋮). Click this, and then click Assign script.
  4. In the new window, type whatever the name of your function is, without parentheses (for example myFunction), and press OK.

References:

Upvotes: 3

Marios
Marios

Reputation: 27390

That is because you are not allowed to use a custom function to perform actions that require authorization such as adding an event to your calendar.

From the official documentation:

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data.

However, there are some workarounds that you could try. For example, you can create a custom menu option within your spreadsheet file or a time-driven trigger.

Upvotes: 2

Related Questions