2

I want to link two spreadsheets with IMPORTRANGE, let's say A with B. B is the source. I want A to import numbers from B every 6 hours, because the numbers in B are constantly moving and since the sheet is really big, it would slow down sheet A.

Is there a way to do that?

4
  • didn't try it out, but a IFTTT date&time plus google drive seems promising
    – malarres
    Feb 12, 2015 at 10:45
  • Do you need to run the update in the background, i.e. when you're not viewing the spreadsheet? Feb 13, 2015 at 9:20
  • yeah. data is constantly being fed into.
    – jason
    Feb 13, 2015 at 12:01
  • You can do this with Google Apps Script (needs JavaScript knowledge). Also, you could find this useful: stackoverflow.com/questions/20444102/…
    – appl3r
    Feb 13, 2015 at 16:43

1 Answer 1

1
+50

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.

3
  • Vidar. Thanks for the solution. I will try out your script.
    – jason
    Feb 13, 2015 at 23:24
  • According to Change a spreadsheet's locale, time zone, recalculation, and language the recalculation doesn't affect IMPORTRANGE as the article says that iMPORTRANGE is recalculated every 30 minutes. By the other hand, the new Google Sheets doesn't allow non-deterministic values as parameters. Is your script still working for you? (I'm not scanning the site for your answers, it "emerged" because a new answer was posted) Mar 30, 2016 at 14:54
  • No, it seems you are right. It now gives an error saying that the non deterministic functions can't be used. I'll see if I can rework this answer to be compatible with the new Spreadsheets. Thanks for noticing! Mar 30, 2016 at 15:08

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.