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?

  • didn't try it out, but a IFTTT date&time plus google drive seems promising – malarres Feb 12 '15 at 10:45
  • Do you need to run the update in the background, i.e. when you're not viewing the spreadsheet? – Vidar S. Ramdal Feb 13 '15 at 9:20
  • yeah. data is constantly being fed into. – jason Feb 13 '15 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 '15 at 16:43
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.

  • Vidar. Thanks for the solution. I will try out your script. – jason Feb 13 '15 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) – Rubén Mar 30 '16 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! – Vidar S. Ramdal Mar 30 '16 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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