Exception: Specified permissions are not sufficient to call SpreadsheetApp.openById

I have an iOS shortcut that appends things to a to-do list in google sheets (Code.gs:) and then I have a macro that organizes my raw to-do list whenever I select a tab from a dropdown in column D (macro.gs). Each code works when they're in their own file but when in the same file, Code.gs works fine but Macro.gs does not work and gets this error:

Exception: Specified permissions are not sufficient to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets at unknown function

Any help is greatly appreciated!!

Code.gs:

var financesheet = SpreadsheetApp.openById("IDDDDDD").getSheetByName("Inbox");


function doGet(e) {
  
  var account = JSON.parse(e.parameters.account)
  var detail = JSON.parse(e.parameters.detail)


  var formattedTime = Utilities.formatDate(new Date(), "GMT-4", "h:mm a");
  var formattedDate = Utilities.formatDate(new Date(), "GMT-4", "EEE, MMM d, yyyy");

 financesheet.appendRow([formattedDate,account,detail]);
 
}

Macro.gs:

function onEdit(e) {
  // Log the event to check if the function is triggered
  Logger.log('onEdit Triggered');
  
  // Ensure the edit is made in the "Inbox" sheet
  var sheet = e.source.getSheetByName("Inbox");
  if (!sheet) return;  // Exit if the edit is not in the "Inbox" sheet

  var editedRange = e.range;
  
  // Log details about the edited range to verify which column is being edited
  Logger.log('Edited Range: ' + editedRange.getA1Notation());

  // Check if the edited column is column D (index 4)
  if (editedRange.getColumn() === 4) {
    var valueC = editedRange.getValue();  // Get the value of column D (4th column)
    
    // Log the value of column D to the Execution Log
    Logger.log('Value in Column D (Row ' + editedRange.getRow() + '): ' + valueD);
    
    // Check if column D has a value
    if (valueD != "") {
      var rowValues = sheet.getRange(editedRange.getRow(), 1, 1, 3).getValues()[0];  // Get values from columns A, B, and C
      
      var destinationSheet = e.source.getSheetByName(valueD);
      
      // Append values from columns A, B, and C to the end of the destination sheet
      if (destinationSheet) {
        destinationSheet.appendRow(rowValues);
        
        // Delete the row from the Inbox tab
        sheet.deleteRow(editedRange.getRow());
      }
    }
  }
}

Solution

The declaration of financesheet is in the global space, outside of any function. It will get evaluated when any function in the script project gets run.

The onEdit(e) and doGet(e) functions are simple triggers that run automatically. When either of them runs, the financesheet declaration gets evaluated, even though it's not within that specific function.

The onEdit(e) simple trigger executes in a restricted context where the code cannot call services that require authorization. The .openById() method requires authorization, which explains why an error gets thrown when onEdit(e) runs.

To make it work, move the declaration of financesheet inside the function that needs it. If you're appending data to the spreadsheet the script project is bound to, you may also want to use .getActive() instead of .openById(), like this:

function doGet(e) {
  const financesheet = SpreadsheetApp.getActive().getSheetByName('Inbox');
  const formattedDate = Utilities.formatDate(new Date(), 'GMT-4', 'EEE, MMM d, yyyy');
  const account = JSON.parse(e.parameters.account);
  const detail = JSON.parse(e.parameters.detail);
  financesheet.appendRow([formattedDate, account, detail]);
}