Fix 'You do not have permission to call SpreadsheetApp.openById' in Google Apps Script Custom Functions

TL;DR

Custom Functions in Google Sheets run inside a locked-down sandbox. That sandbox blocks anything requiring OAuth — and SpreadsheetApp.openById() definitely needs it. You can't call it from a =MY_FUNCTION() formula, full stop. Move the logic to a regular Apps Script function, an installable trigger, or a menu-driven function instead.

The exact error

Exception: You do not have permission to call SpreadsheetApp.openById. (line X)

The error appears directly in the cell that called your function — instead of a value, you get this message. Hover over the cell and you'll see it as a red triangle tooltip.

Why this happens

When you call a function from a cell like =MYFUNCTION(A1), Google runs it in what they call the "custom function sandbox." Think of it as a read-only, no-auth zone. Google built it this way deliberately — formulas recalculate constantly and silently, so giving them full OAuth access would mean a spreadsheet could quietly exfiltrate your data every time a cell changes.

The sandbox blocks a lot of things you might expect to work:

• Opening other spreadsheets (SpreadsheetApp.openById(), openByUrl())
• External services that need OAuth (GmailApp, DriveApp, private UrlFetchApp calls)
• Modifying the spreadsheet's structure (inserting rows, setting formatting)
• Anything UI-related (SpreadsheetApp.getUi())

One mental model that works: if it would trigger an OAuth consent dialog, it won't work in a custom function. The Apps Script docs have the full list, but that rule gets you 90% of the way there.

Fix 1 — Use getActiveSpreadsheet() instead (same spreadsheet only)

Need data from the same spreadsheet the formula lives in? Swap openById() for getActiveSpreadsheet(). That one is allowed inside custom functions.

// ❌ Fails in a custom function
function getSheetValue(sheetName, row, col) {
  const ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID');
  const sheet = ss.getSheetByName(sheetName);
  return sheet.getRange(row, col).getValue();
}

// ✅ Works — reads from the current spreadsheet
function getSheetValue(sheetName, row, col) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) return 'Sheet not found';
  return sheet.getRange(row, col).getValue();
}

One catch: you can only read data this way. Writing back to the sheet from inside a custom function is also blocked.

Fix 2 — Move the logic to a menu function or button

Need to open a different spreadsheet entirely? The logic has to leave the custom function sandbox. Attaching it to a menu item is the cleanest approach — a few lines of boilerplate and you're done.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Tools')
    .addItem('Pull Data from Other Sheet', 'pullFromOtherSheet')
    .addToUi();
}

function pullFromOtherSheet() {
  // Normal function — full permissions, no sandbox
  const ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID');
  const data = ss.getSheetByName('Data').getRange('A1:B10').getValues();

  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Results')
    .getRange(1, 1, data.length, data[0].length)
    .setValues(data);
}

The flow: user clicks the menu item → function runs with full permissions → data lands on the sheet → your formulas reference it normally. No sandbox involved.

Fix 3 — Use an installable time-driven trigger

Want this to run on a schedule rather than on a user click? Set up an installable trigger. Unlike simple triggers (onOpen, onEdit), installable triggers run with the full permissions of whoever created them.

// Run this once to register the trigger
function createTrigger() {
  ScriptApp.newTrigger('syncFromOtherSheet')
    .timeBased()
    .everyHours(1)
    .create();
}

function syncFromOtherSheet() {
  const source = SpreadsheetApp.openById('SOURCE_SPREADSHEET_ID');
  const target = SpreadsheetApp.getActiveSpreadsheet();

  const values = source.getSheetByName('Export')
    .getDataRange()
    .getValues();

  target.getSheetByName('Import')
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}

Register it via Extensions → Apps Script → Triggers, or run createTrigger() once from the editor. After that, it fires every hour with no user involvement.

Fix 4 — Refactor to pass data as function parameters

Sometimes the real fix is rethinking the design. Rather than having the custom function reach out to another spreadsheet, pre-import the data you need (via a trigger or a one-off import) and cache it locally. Then your formula just reads nearby cells.

// Instead of this:
// =GET_REMOTE_PRICE("AAPL")
// which tries to openById() internally...

// Do this:
// 1. A trigger pulls prices into column B of a hidden sheet every 15 minutes
// 2. Your formula does a VLOOKUP on that cached data
// =VLOOKUP("AAPL", PriceCache!A:B, 2, FALSE)

This pattern is more robust anyway. Decoupling data fetching from formula evaluation means your sheet stops hitting external services on every recalculation — on a large sheet with hundreds of formulas, that difference is noticeable.

Verification — confirming the fix works

• Reload the spreadsheet (Ctrl+R / Cmd+R) after applying any fix.
• Moved logic to a menu? Run the function manually from the Apps Script editor first. Confirm it completes cleanly before testing via the menu item.
• Using a trigger? Head to Extensions → Apps Script → Triggers and check the Executions tab after it fires. Green checkmarks mean it worked.
• Switched to getActiveSpreadsheet()? The cell should now show a value instead of the error. If it still fails, open the execution log (View → Executions) — there's likely a different error underneath.

Quick reference — what's allowed in custom functions

AllowedNot allowed

`SpreadsheetApp.getActiveSpreadsheet()``SpreadsheetApp.openById()`
Reading cell valuesWriting/modifying the sheet
Math, string manipulation`GmailApp`, `DriveApp`
Public `UrlFetchApp` calls (no auth)Authenticated API calls
`CacheService`, `Utilities``SpreadsheetApp.getUi()`

Tips

Building spreadsheet tools that mix custom functions with background syncing gets complicated fast. Keeping a clear mental model of what runs where — sandbox vs. full permissions — saves a lot of head-scratching. When debugging data structures during these integrations, I'll sometimes use ToolCraft to validate JSON payloads locally. Nothing gets uploaded, which matters when the spreadsheet data is sensitive.

The rule that cuts through everything: if a user would need to click "Allow" in an OAuth consent dialog, it can't live in a custom function. Design around that constraint. Don't fight it.