Why does my script ask for permission to access all my spreadsheets, not just the one I specified?

openById triggers the 'spreadsheets' OAuth scope, which Google defines broadly — there is no scope that grants access to a single file by ID. Any script that reaches outside its bound spreadsheet will request this wider scope on first authorization. That's a platform constraint, not a bug in your script.

Can I use openById on a file owned by someone else?

Yes, as long as the account running the script has been granted access (at least viewer) on that file. If it hasn't, openById throws a 'You do not have permission' exception. The sharing must be done in Drive before the script runs — there's no way to bypass it in code.

What's the difference between openById and openByUrl?

openByUrl accepts the full browser URL of the spreadsheet. It extracts the ID internally and calls the same underlying service. openById is marginally more explicit and slightly faster since there's no URL parsing. Either works; the quota impact is identical.

My source sheet name has spaces in it — do I need to escape them?

No. getSheetByName takes a plain JavaScript string, so spaces are fine: getSheetByName('My Orders Sheet') works as written. The only thing that will break it is a mismatch in capitalization or a hidden trailing space in the actual sheet tab name.

// Sheets · Apps Script

Pull data from another spreadsheet in Google Sheets.

Use Apps Script's SpreadsheetApp.openById to read a range from a separate Google Sheets file in one efficient getValues call — no IMPORTRANGE, no cross-file cell loops.

“I need to read data from a different Google Sheet file into my script without using IMPORTRANGE or manually copying ranges.”

The script

copy · paste · trigger

pullFromSheet.gs

Apps Script

// Pull a named range from a separate spreadsheet file.
// Requires the Sheets OAuth scope (added automatically on first auth).

function pullDataFromOtherSheet() {
  var SOURCE_ID = '1A2B3C4D5E6F7G8H9I0J_exampleSpreadsheetId';
  var SOURCE_SHEET = 'Orders';
  var SOURCE_RANGE = 'A2:D200';

  var source = SpreadsheetApp.openById(SOURCE_ID);
  var sheet = source.getSheetByName(SOURCE_SHEET);
  var values = sheet.getRange(SOURCE_RANGE).getValues();

  var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Import');
  dest.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Finding the source spreadsheet ID

The ID is the long alphanumeric string in the file's URL, between /d/ and /edit. Open the source sheet, copy everything between those two path segments, and paste it as SOURCE_ID. That string is stable — it doesn't change when the file is renamed or moved between folders.

SpreadsheetApp.openById will throw a permissions error if the account running the script doesn't have at least viewer access on the source file. The first time you run a script that calls openById, Google will prompt for the broader 'See, edit, create, and delete all your Google Sheets spreadsheets' scope — that's expected, and unavoidable when reaching outside the bound spreadsheet.

One getValues call, not a loop

The single costliest mistake I see in scripts that pull cross-file data is iterating cell by cell — something like looping through rows and calling getRange('A' + i).getValue() inside the source sheet. Each getValue() is a separate API call across a network boundary. On a 200-row sheet, that's 200 round trips. Apps Script has a 6-minute execution limit, and a per-user quota of roughly 20,000 read calls per day.

The correct pattern is one getRange().getValues() that returns the entire 2D array at once, then work with the JavaScript array in memory. The snippet above does exactly that: fetch all of A2:D200 in a single call, write it to the destination in a single setValues call. Two Sheets API calls total, regardless of row count.

If you don't know the exact last row, replace the fixed range string with sheet.getDataRange().getValues(), which adapts to however many rows contain data.

Writing the result without overwriting headers

dest.getRange(1, 1, values.length, values[0].length) sizes the destination range precisely to the data array. The four arguments are: starting row, starting column, number of rows, number of columns. Using values[0].length for column count means the destination range matches the source exactly — no extra blank columns, no truncation.

If your destination sheet has headers in row 1, change the first argument to 2 so the data lands in row 2 onward. You may also want to clear the old data first with dest.clearContents() before the setValues call, otherwise stale rows below the new data persist from the previous run.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does my script ask for permission to access all my spreadsheets, not just the one I specified?: openById triggers the 'spreadsheets' OAuth scope, which Google defines broadly — there is no scope that grants access to a single file by ID. Any script that reaches outside its bound spreadsheet will request this wider scope on first authorization. That's a platform constraint, not a bug in your script.
Can I use openById on a file owned by someone else?: Yes, as long as the account running the script has been granted access (at least viewer) on that file. If it hasn't, openById throws a 'You do not have permission' exception. The sharing must be done in Drive before the script runs — there's no way to bypass it in code.
What's the difference between openById and openByUrl?: openByUrl accepts the full browser URL of the spreadsheet. It extracts the ID internally and calls the same underlying service. openById is marginally more explicit and slightly faster since there's no URL parsing. Either works; the quota impact is identical.
My source sheet name has spaces in it — do I need to escape them?: No. getSheetByName takes a plain JavaScript string, so spaces are fine: getSheetByName('My Orders Sheet') works as written. The only thing that will break it is a mismatch in capitalization or a hidden trailing space in the actual sheet tab name.

// one good script a week