Google Sheets – How to Grant Permission to Self-Defined Functions

google sheetsgoogle-apps-scriptpermissions

I have in Google Sheets a table with the name of a contact in column 1 and want to retrieve their email address in column two.

This is the code I have used:

function email(name) {
  var contacts = ContactsApp.getContactsByName(name);
  var emailAdd = contacts[0].getPrimaryEmail();
  return emailAdd;
}

I now try to test this function in a Sheet, by writing =email(A1) (for example) in column 2, and get the following error message:

You do not have permission to call getContactsByName.

How can I overcome this problem? I have tried to go into Resources>Advanced Google services and enabling the contacts API, without more luck.

Best Answer

Custom functions can't call services that require authorization. Source: https://developers.google.com/apps-script/guides/sheets/functions.

The way to overcome the problem is to use another way for calling the function, like assign the script to a custom menu, an image, an installable trigger or by running it from the Script Editor.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Max & Color', 'getmaxColor').addToUi();
}

function getmaxColor() {
  var ss = SpreadsheetApp.getActive(), aRange = ss.getActiveRange(); 
  var data = aRange.getValues(), colors = aRange.getBackgrounds();
  var dest = aRange.offset(0, data[0].length, 1, 1);
  var index = getmaxIndices(data);
  var max = data[index.i][index.j], color = colors[index.i][index.j];
  dest.setValue(max).setBackground(color);  
}

function onEdit() {
  var ss = SpreadsheetApp.getActive(), col = ss.getActiveCell().getColumn();
  if(col < 7) {
    var aRange = ss.getRange("A1:F7"), data = aRange.getValues();
    var colors = aRange.getBackgrounds();
    var dest = aRange.offset(0, data[0].length, 1, 1)
    var index = getmaxIndices(data);
    var max = data[index.i][index.j], color = colors[index.i][index.j];
    dest.setValue(max).setBackground(color); 
  }
}

function getmaxIndices(data) {
  var max = 0, indexI = 0, indexJ = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    for(var j=0, jLen=data[0].length; j<jLen; j++) {
      if(data[i][j] > max) {
        max = data[i][j], indexI = i, indexJ = j;
      }
    }
  }
  return {"i":indexI, "j":indexJ};
}

Explained

The onOpen() adds a menu item. Select the range you want to evaluate and select the menu option Max & Color. The values and backgrounds are collected as is an offset range. Then the indices (2d-array) of the max value are obtained, through the getmaxIndices(). Afterwards the corresponding max value and color is set.

The onEdit() does almost the same, but it has a fixed range. It will only fire if a cell is edited and if the edited cell belongs to the A1:F7 range.

Note

The reason for getting the error message, can be read in this answer: custom function

Example

I've added the sample code into your example file.

Best Answer

Related Solutions

Google-sheets – Download list of contact information and check if Google Account

Google Sheets – Change Cell Color in a Range

Code

Explained

Note

Example

Related Topic