Google Sheets IMPORT functions · error message

Google Sheets: Not allowed to reference a cell with NOW or RAND

Policy / accountFormula and range problems

Last verified

The exact message
Error: This function is not allowed to reference a cell with NOW(), RAND(), RANDARRAY(), or RANDBETWEEN()

What it means

Import functions are not allowed to depend on volatile functions. If the URL, query or range argument of IMPORTXML, IMPORTHTML, IMPORTDATA, IMPORTFEED or IMPORTRANGE refers, directly or through other cells, to NOW(), RAND(), RANDARRAY() or RANDBETWEEN(), Google returns this error.

This is deliberate. Those functions change on every recalculation, which would force a new external fetch every time and create the request floods that Google throttles. People usually hit it when trying to force a refresh by appending a timestamp or random number to the URL.

Google documents one exception: TODAY() is allowed because it updates at most once a day.

What it looks like

=IMPORTXML("https://example.com/rates?t=" & NOW(), "//td[1]")

Error
This function is not allowed to reference a cell with NOW(), RAND(), RANDARRAY(), or RANDBETWEEN()

=IMPORTXML("https://example.com/rates?d=" & TODAY(), "//td[1]")

Why it happens

  • A cache-busting parameter built with NOW() or RAND() is appended to the import URL.
  • The URL or range is built from a cell several steps upstream that contains a volatile function.
  • A template copied from the web uses a timestamp to force refreshes.
  • A named range used in the formula resolves to a cell containing a volatile function.

How to fix it

  1. Find the volatile function feeding the formula by tracing each referenced cell.
  2. Replace NOW() with TODAY() if a once-a-day refresh is enough.
  3. If the value only needs to be set once, convert it to a static value with Paste special > Values only.
  4. For controlled refreshes, change a plain value in a cell the URL references (for example a manual refresh counter) instead of using a volatile function.
  5. For scheduled refreshes, use a time-driven script that writes a new static value into that cell.

How to stop it recurring

Accept Google's documented refresh behavior (import functions check for updates every hour while the file is open) unless you truly need more. Where you do, drive refreshes from a plain cell that a person or a schedule updates, never from a volatile function.

Official documentation: support.google.com/docs/answer/12188454. See all Google Sheets error messages or the Google Sheets limits and quotas.

Related errors

Definitions

Quick answers

What does "Not allowed to reference a cell with NOW or RAND" mean in Google Sheets?

Import functions are not allowed to depend on volatile functions. If the URL, query or range argument of IMPORTXML, IMPORTHTML, IMPORTDATA, IMPORTFEED or IMPORTRANGE refers, directly or through other cells, to NOW() , RAND() , RANDARRAY() or RANDBETWEEN() , Google returns this error. This is deliberate.

How do I fix "Not allowed to reference a cell with NOW or RAND"?

1. Find the volatile function feeding the formula by tracing each referenced cell. 2. Replace NOW() with TODAY() if a once-a-day refresh is enough. 3. If the value only needs to be set once, convert it to a static value with Paste special > Values only. 4. For controlled refreshes, change a plain value in a cell the URL references (for example a manual refresh counter) instead of using a volatile function. 5. For scheduled refreshes, use a time-driven script that writes a new static value into that cell.

When an IMPORT formula cannot read the page

IMPORTXML and IMPORTHTML only see the HTML the server sends. An Autonoly extraction step reads the page in a real browser and writes rows to Google Sheets. Sites that block automated visitors can still block it.

Also see Google Sheets integration.