0

I'm trying to write a custom function for Google Sheets but am running into permission errors left and right. Just to get things started, I typed this into the top left cell: "=testFunction()". In the function, I tried to highlight and select a row based on the user, and came up across the following issues:

  1. I don't have permission to call the Session.getActiveUser() method, so I can't do anything based on the user accessing the document. Also, the getUrl function doesn't return the actual URL, so putting something like "#Jeff" at the end won't work. Note: I did eventually get around this because much to my surprise, the Spreadsheet.getViewers function works.
  2. I don't have permission to run the Spreadsheet.setActiveSelection() function, so I can't select a row.
  3. I don't have permission to change the background for cell B if I run the function from cell A. This means I need to put my broken highlight function in every cell that I want to change color.

It's getting frustrating to look up how to do something, test it, and watch a permission error pop up three out of four times.

Considering that I'm the owner of this document, is there any way to change what I have permission to do?

If not, is there any documentation for what functions I can run?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user688260
  • 9
  • 3
  • Related: http://stackoverflow.com/questions/40009418/how-do-i-get-permission-to-delete-rows-in-google-sheets – Rubén Oct 13 '16 at 03:31

2 Answers2

1

custom functions are not permitted to access user-specific services

JuricaJaman
  • 11
  • 1
0

What I've noticed is a custom function from a cell can't do auth-required methods, but you can get your script to run if you let the script be triggered.

Example: I wrote this script function associated with my sheet.

function getTheUser() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  sheet.getRange('a1').setValue(Session.getActiveUser());
}

if I put this in a cell 

=getTheUser()

the cell gets marked with an "error: You do not have permission to call getActiveUser (line 9, file "Code")"

But if I go to the project's scripts and create a trigger for the function, the function will run OK any time that trigger condition is satisfied.

My plan is to use triggers to get the auth-required functions to run.

Mike D
  • 33
  • 1
  • 4