Google spreadsheet - #N/A error when imported range has a custom function

Question

In my Google spreadsheet (the newest version, as of August 2014) I'm using =IMPORTRANGE(P5,"Tasks!b6:o7") where P5 cell contains another spreadhsheet's key. One of the cells in the other spreadsheet contains my custom function, that sometimes takes time to calculate (there are dozens of cells using that function). The problem is that in the first spr. that cell's value is not displayed properly, the #N/A error message is displayed instead.

The function is defined as follows:

//  global variable
var ONEDAY = 24*60*60*1000; // hours*minutes*seconds*milliseconds

function _mySimpleCalculateDaysDifference(DateToBeChecked)
{
  var todaysDate = new Date();
  var todaysDayModifier = todaysDate.getHours()<6?-1:0;
  var daysDiff = (todaysDate.getTime() - DateToBeChecked.getTime())/ONEDAY;
  return Math.floor(daysDiff) + todaysDayModifier;
}

It's being called the following way:

=if((H18>0),(H18-F18),if((F18>0),_mySimpleCalculateDaysDifference(F18),""))

where F and H columns contain date values.

Is there any way to solve that problem?

Edit:
I mentioned that the main spreadhsheet is the newest Google spreadsheet, but those being pulled in were 'old'.

Looks like this Issue: code.google.com/p/google-apps-script-issues/issues/… - the issue is about 2.5 years old. — eddyparkinson
In my case the custom function works fine in the original spreadsheet. The problem appears when it's partially imported into another spreadsheet (with IMPORTRANGE). I've recently migrated the 'importing' spreadsheet into the new google spreadsheets. Before it was OK. — ellockie
I've just checked and currently it looks fine, so it's a bit 'moody' behaviour, perhaps depending on Google's available processing power resources (it's Monday morning in Europe)? — ellockie
Yes, the problem is moody. I think values are sorted in a cache for about 10mins, About 10mins after closing the spreadsheet the values look to get dropped and you start to get N/A again. Sometimes they calculate in under a second, other times they can take a few mins, and once in a while the fail to recalculate. — eddyparkinson
It's interesting - I've got another set of spreadsheets like that, and all of them are the 'new' spreadsheets, while those problematic are 'old'. They work fine. I'll check if it helps to use newer versions. — ellockie

ellockie ellockie · Accepted Answer · 2014-09-11T01:28:13

This answer relates to a situation when the importing spreadsheet is the 'new' one, and imported - the 'old' (as of September 2014):

Make sure that both spreadsheets are of the same version, i.e. old<-old or new<-new (although as @eddieparkinson commented, in his case similar issue existed for an old<-old situation).

In my case converting the imported spreadsheet to the new helped and I cannot observe the issue now.

PS
Probably the easiest way to convert is to save it as an Excel file, then import it back into Google Drive and convert into Google spreadsheet. I found it the easiest and most reliable way, although I lost my charts and scripts. There were also problems with validations, so I had to recreate them. If you're not using any of these - it should be OK.

Google spreadsheet - #N/A error when imported range has a custom function

1 Answers