Google-sheets – Google Spreadsheets showing “shared with xxx” count greater than actual

The IMPORTRANGE function does not seem to cache its results, so it will refresh whenever the source spreadsheet changes.

But we can circumvent this by writing our own function:

function customImportRange(spreadsheetUrl, rangeStr, timestamp) {
  var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var range = sheet.getRange(rangeStr);
  return range.getValues();
}

Install this function by clicking Tools → Editor, paste the code, and click the Run button. This will popup a dialog asking you to give the script permission to run.

The script does exactly the same as IMPORTRANGE, but, being a Google Apps Scripts function, Google Spreadsheets will cache its results, and not fetch any new data as long as the parameters to the function are unchanged.

That means that we can change the timestamp parameter every 6 hours to trigger a refetch, which again means that we need a formula that generates a new value every 6 hours.

Given that we put =NOW() in cell B1, and the following formula in cell C1:

=DATEVALUE(B1) * 10 + ROUNDUP(HOUR(B1) / 6)

Cell C1 now displays a number that changes every 6 hours, meaning we can use it as input to our function. Put the following in cell A2:

=customImportRange("https://docs.google.com/spreadsheets/d/1qFryUlGfGT8dp_lx9sSE_7suCV0t8cAg9btM1AOPnSI/edit#gid=0", "'Custom function'!A4:F6", C1)

The parameters are:

1. The full URL to the source spreadsheet
2. The range notation for the data you want to fetch
3. The cache key

I have put up an example spreadsheet to demonstrate this, feel free to copy it for your own experimentation.

To have the spreadsheet update also when it is not opened in a browser, you should go to File → Spreadsheet settings and set Recalculation to On change and every hour.

My experiments on this would suggest that there is a max limit on the number of items that you can import using importrange() which id not linked to the total number of cells in your Google Sheet.

This figure seems to be around 175,000 cells. I'm not sure if this is the offical max, but that's the most I have been able to import without getting a 'too large' error.