Required permissions: https://www.googleapis.com/auth/spreadsheets in a custom function

Question

I have read through the official documentation that states, you are not allowed to use services that require authorization such as SpreadsheetApp.openById within a custom function. I am using a script to call a spreadsheet in its functions and it's doing that fluently. My custom function is not using the service SpreadsheetApp.openById, but still tells me that I do not have the permission. I just want to know whether it is possible to run the custom function or not, even though I am not calling a spreadsheet in the function itself?

Updated:

My custom function build's a reference id for a particular quotation. I've also tried using https://www.googleapis.com/auth/spreadsheets in oauthScopes in the json file, didn't work. This is the function calling openById:

    var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var dukeid = "1WGbEo1Xr99HwHY_4ZaeTRIgCOuNcjVfqCzZx4dcQX4I"
var duke = SpreadsheetApp.openById(dukeid);
var totalstockd = duke.getSheetByName("Total Stock").getRange(2,3,2500,7).getValues();
  

var avmid = "1a6bm2O_iljHZUoF2BhYeyxUT13tB06-JJCYeYCZZ10Q"
var avm = SpreadsheetApp.openById(avmid);
var totalstocka = avm.getSheetByName("Total Stock").getRange(2,3,1000,7).getValues();

function when(e){

var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var ssName = activeCell.getSheet().getName();

if (ssName=="General Information" && (r==3 || r==6) && c==3){
    
    var rescell = ss1.getRange(r,6);
    var unitcell = ss1.getRange(r,7);
    rescell.clearContent();
   

    if(val[0]=="A"){

      
      var result = sumalt(val,totalstocka,unitcell);
     
      rescell.setValue(result);

    }
    else {
     
      var result = sumalt(val,totalstockd,unitcell);
      
      rescell.setValue(result);
    }
  }
else{
    console.log("No Edit");
  }
}

And this is my custom function:

function QREF(Company,ID){
  var td=new Date().valueOf();
  var year = new Date().getFullYear();
  var hd=new Date(year, 0, 0).valueOf();
  var year2 = year - 2000
  var sec=1000;
  var min=60*sec;
  var hour=60*min;
  var day=24*hour;
  var diff=td-hd;
  var julian=Math.floor(diff/day);
  Logger.log(year2);
  string = Company + ID + "-"+ "" + julian + "/" + year2 + "-";
  return string;
}

Answer 1

Modification points:

• When I saw your script, I noticed that SpreadsheetApp.openById is used as the global. By this, when your custom function is run, SpreadsheetApp.openById is run. So, such error occurs. I think that this is the reason of your issue.

When you want to use both script in your Google Apps Script, how about the following modification?

Modified script:

function when(e) {
  // These scripts are included in a function.
  var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dukeid = "1WGbEo1Xr99HwHY_4ZaeTRIgCOuNcjVfqCzZx4dcQX4I"
  var duke = SpreadsheetApp.openById(dukeid);
  var totalstockd = duke.getSheetByName("Total Stock").getRange(2, 3, 2500, 7).getValues();
  var avmid = "1a6bm2O_iljHZUoF2BhYeyxUT13tB06-JJCYeYCZZ10Q"
  var avm = SpreadsheetApp.openById(avmid);
  var totalstocka = avm.getSheetByName("Total Stock").getRange(2, 3, 1000, 7).getValues();

  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var ssName = activeCell.getSheet().getName();
  if (ssName == "General Information" && (r == 3 || r == 6) && c == 3) {
    var rescell = ss1.getRange(r, 6);
    var unitcell = ss1.getRange(r, 7);
    rescell.clearContent();
    if (val[0] == "A") {
      var result = sumalt(val, totalstocka, unitcell);
      rescell.setValue(result);
    } else {
      var result = sumalt(val, totalstockd, unitcell);
      rescell.setValue(result);
    }
  } else {
    console.log("No Edit");
  }
}

function QREF(Company, ID) {
  var td = new Date().valueOf();
  var year = new Date().getFullYear();
  var hd = new Date(year, 0, 0).valueOf();
  var year2 = year - 2000
  var sec = 1000;
  var min = 60 * sec;
  var hour = 60 * min;
  var day = 24 * hour;
  var diff = td - hd;
  var julian = Math.floor(diff / day);
  Logger.log(year2);
  string = Company + ID + "-" + "" + julian + "/" + year2 + "-";
  return string;
}

• By this modification, when QREF() is run as the custom function, SpreadsheetApp.openById is not run. By this, such error can be removed.

Note:

• If you are using the following script at other function, please be careful this. In that case, please include the script to the function. Or, please include the following script as new function, and call the function from other function.

    var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var dukeid = "1WGbEo1Xr99HwHY_4ZaeTRIgCOuNcjVfqCzZx4dcQX4I"
    var duke = SpreadsheetApp.openById(dukeid);
    var totalstockd = duke.getSheetByName("Total Stock").getRange(2, 3, 2500, 7).getValues();
    var avmid = "1a6bm2O_iljHZUoF2BhYeyxUT13tB06-JJCYeYCZZ10Q"
    var avm = SpreadsheetApp.openById(avmid);
    var totalstocka = avm.getSheetByName("Total Stock").getRange(2, 3, 1000, 7).getValues();