
How to Fix Google Sheets #ERROR! Formula Parse Error with IMPORTRANGE

In 85% of cases, the #ERROR! parse error occurs because your spreadsheet locale requires a semicolon (;) instead of a comma (,) as the argument separator, or missing surrounding double quotes around spreadsheet URLs.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit"; "Sheet1!A1:D50")Why Does This Error Happen?
Google Sheets evaluates syntax strictly based on Spreadsheet Settings > Locale. Locales in Europe and Latin America use commas as decimal delimiters, forcing Google Sheets to reject commas as function argument separators and throw a fatal Formula Parse Error.
Step-by-Step Diagnostic & Resolution Walkthrough
1Step 1: Check and Harmonize Argument Separators
If your spreadsheet uses European or international locale settings, replace commas between arguments with semicolons. Ensure both the spreadsheet URL and the sheet range are enclosed in double quotes.
=IMPORTRANGE("SPREADSHEET_KEY"; "TabName!A1:Z100")2Step 2: Explicitly Grant Access Permissions
Hover directly over the cell displaying #REF! or #ERROR!. If an 'Allow Access' blue button appears, click it. You must do this once whenever linking two distinct Google Sheets files under different folder permissions.
3Step 3: Verify Exact Tab Name and Apostrophes
If your target sheet tab has spaces in its title (e.g. 'Q1 Revenue'), you must enclose the sheet name in single quotes inside the double quotes: "'Q1 Revenue'!A1:F20".
=IMPORTRANGE("SPREADSHEET_KEY"; "'Q1 Revenue'!A1:F20")Troubleshooting Matrix & Configuration Rules
| Spreadsheet Locale | Argument Separator | Example Valid Formula | Error Trigger |
|---|---|---|---|
| United States / UK | Comma (,) | =IMPORTRANGE("KEY", "Sheet1!A:B") | Using semicolon throws parse error |
| Germany, France, Czech, Spain | Semicolon (;) | =IMPORTRANGE("KEY"; "Sheet1!A:B") | Using comma throws #ERROR! |
| Cross-domain corporate accounts | Semicolon or Comma | Requires OAuth Grant dialog | Throws #REF! Permission Block |
← Swipe horizontally to view full table →
Still Not Working? Check These Edge Cases
Fix: Wrap IMPORTRANGE inside QUERY or FILTER to selectively pull only the required column ranges rather than entire columns like A:Z.
Fix: Strip out /edit and query parameters. Extract solely the clean 44-character alphanumeric Spreadsheet ID from the URL bar.
Fix: Temporarily remove IFERROR to expose whether Google Sheets is throwing #REF! (authorization required) or #ERROR! (syntax typo).
Frequently Asked Questions
Why does IMPORTRANGE say Formula Parse Error instead of #REF!?
A parse error indicates a typographical or syntax failure that prevents the engine from parsing the formula, such as mismatched quotes or comma vs semicolon locale issues. #REF! occurs when syntax is correct but permissions are pending.
How do I fix IMPORTRANGE loading indefinitely with no error?
Large sheets freeze when requesting entire open-ended columns (e.g. A:Z). Restrict the range to exact cells (e.g., A1:Z500) or check if your Google Workspace organization restricts cross-domain file sharing.