2

I am trying to use IMPORTDATA in GoogleSheet with URL and time stamp

=IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt?t"=TEXT(NOW(),"ddmmyyyhhmmss"))

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

1

1 Answer 1

1

The usual recipe to do that is to replace the now() in the formula with a reference to a cell, and fill that cell with a timestamp using a short script that runs on a time-driven trigger.

The recently introduced lambda functions let you mask volatile functions such as now() by wrapping them in what is in effect a semi-static closure, so something like this may work as well:

=let( 
  baseUrl, "https://www.amfiindia.com/spages/NAVAll.txt", 
  now, lambda(volatile, volatile)(now()), 
  postFix, "?t=" & text(now, "ddmmyyyhhmmss"), 
  refresh, F1, 
  importdata(baseUrl & postFix) 
)

The formula expects that you Insert > Checkbox in cell F1. Toggle the checkbox to recalculate the formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.