GAS自定义函数跨表格取数权限错误求助

我在spreadsheet1的自定义函数中尝试从另一个表格拉取数据，遇到如下错误：

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

我查阅资料后执行了以下操作：

• 在函数注释区域添加@NotOnlyCurrentDoc
• 在appscript.json文件中配置"oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.readonly"]
• 按照文档提示启用Google Sheets API

但问题仍未解决——即使通过自定义菜单运行函数并授予权限，错误依然存在。

官方文档对表格服务有明确说明：

Spreadsheet: Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

除了将所有数据合并到单个表格外，是否有其他解决方案？个人项目尚可接受合并，但职场场景中合并表格并非可行方案，求帮助！

待调用的自定义函数 #

/**
*@customfunction
*@NotOnlyCurrentDoc
*/
function altWrktsTblSrch5(paramWrkout)
{
  //SpreadsheetApp.setActiveSheet & openByUrl do NOT open the spreadsheet when it is a DIFFERENT spreadsheet being called BUT these 
  var dbSpreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit?pli=1#gid=45618254');
  
  //var sheet_alt_wrkts = dbSpreadSheet.getSheetByName("Alt Workouts TABLE").getRange(2,1,100,6);
  var range_alt_wrkouts = sheet_alt_wrkts.getValues();
  var avlbl_non_sqntnl_alt_workts_array = range_alt_wrkouts.filter(e => e[3] && e[3] && e[4] == 'Yes' && e[5] == 'Non-sequential'); 
  var cols = [1, 3];
  var alt_wrkouts_array = avlbl_non_sqntnl_alt_workts_array.map(r => cols.map(i => r[i-1]));
  console.log(alt_wrkouts_array);
  var argWrkout = paramWrkout;

  //"Virtually" flattens the array without altering it and finds the ABSOLUTE index position of the desired element
  var index = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(argWrkout); //'Walking' is passed in by the code in the function at the top
  console.log("Index: " +index );
  var numCols = alt_wrkouts_array[0].length;
  console.log("Number of columns: " +numCols);
  var rows = parseInt(index / numCols);
  console.log("Number of rows: " +rows);
  var cols = index % numCols;
  console.log(cols);
 

  //Test to confirm whether at the last row of the TYPE of alternative activity if NOT normal actions 
  if (alt_wrkouts_array[rows][cols-1] != alt_wrkouts_array[rows+1][cols-1])
  {
    index2 = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(alt_wrkouts_array[rows][cols-1]);
    console.log("Index2: " +index2);
    numCols2 = alt_wrkouts_array[0].length;
    console.log("Number of columns2: " +numCols2);
    rows2 = parseInt(index2 / numCols2);
    console.log("Number of rows2: " +rows2);
    cols2 = index2 % numCols2;
    console.log(cols2);
    return console.log(alt_wrkouts_array[rows2][cols2+1]); //returns to the first workout of the current alternate workout
  }
}

内容的提问来源于stack exchange，提问作者Drew254