How to acquire permission for opening spreadsheet using openById or openByUrl

Question

I was trying to store user entered data to other spreadsheet so I declared some global variables for calling sheets of other spreadsheet and called spreadsheet using openByUrl function. It worked.

After this I tried adding custom menu to my current spreadsheet from where I have to take user entry and tried to add menu to menu bar onOpen but execution failed. Stating error - Jun 18, 2023, 4:58:57 PM Error Exception: You do not have permission to call SpreadsheetApp.openByUrl. Required permissions: https://www.googleapis.com/auth/spreadsheets at unknown function

And when I comment out variable declaration it runs.

Codes are as under -

var edUrl = "https://docs.google.com/spreadsheets/d/1qTUKifG4qsk2quRok4XIMYnPO-gzcJlECgCmjxnclaQ/edit";
var ssData    = SpreadsheetApp.openByUrl(edUrl);
var wsClients = ssData.getSheetByName("Clients");
var wsCliPrj  = ssData.getSheetByName("CLI-PRJ");
var wsEstData = ssData.getSheetByName("EstimateData");
var wsPrjEst  = ssData.getSheetByName("PRJ-EST");
var wsEstTc   = ssData.getSheetByName("EST-TC");
var wsItems   = ssData.getSheetByName("Items");


function onOpen(e){
  menu();
}


function menu() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('GHAR');
  menu.addItem('My Menu Item', 'myFunction');
  menu.addToUi();
}


function myFunction(){
 SpreadsheetApp.getActiveSpreadsheet().getRange("A1").setValue("Hello");
}

This is the error shown. Error Required Permission image

I want to add custom menu and designate some functions to those menu in which it will read and write to different spreadsheet.

Welcome to [Stack Overflow](https://stackoverflow.com/tour). — doubleunary, Jun 18 '23 at 13:37

score 0 · Accepted Answer · answered Jun 18 '23 at 13:36

The onOpen(e) function runs in a restricted context where methods that require authorization are not available. SpreadsheetApp.openByUrl() requires authorization, and will thus cause onOpen(e) to error out.

You are not calling SpreadsheetApp.openByUrl() in onOpen(e), but it appears in the assignment of a global variable which gets evaluated before onOpen(e) runs.

You can avoid the issue by moving all global variables inside an initializer function or by using a getter. For sample code, see How to use global variables while avoiding permission errors?

How to acquire permission for opening spreadsheet using openById or openByUrl

1 Answers1