Import data from many different spreadsheets (slow)

Question

I have a formula with dozens of importrange, from as many different spreadsheets. It, of course, slows the spreadsheet, makes my main spreadsheet update everytime there is a modification in one of the spreadsheets...

I'd like my IMPORTRANGE formulas to refresh once every 24h instead of each time there is a modification. It seems like importrange doesn't allow that, so I'm looking for something similar (a script, i suppose). I found this: Force IMPORTRANGE to update at certain intervals (I know I would have to modify it a bit since this script exports the data to another spreadsheet and I'd like to import from an another spreadsheet but that should be doable)

I haven't tested this script, but before trying i have a couple questions:

Is there a simpler solution, maybe a formula that doesn't require a script? Or a function that i don't know about...
If a script is required, is this kind of script (in the link above) really the efficient way to go? I would have to modify this script to be able to import from dozens of different spreadsheets.I'm wondering how slow the import process will be, since the script will have dozens, hundreds of imports to do each night (with maybe 5000 cells in each import) ? If it's a matter of minutes, no problem, but if it means hours, not so much.

Context: I run an event agency. For each prospect, we duplicate a "client" template spreadsheet. When the prospect confirms and becomes a client, we paste the url of the "client" spreadsheet in a master spreadsheet (one line = one client = one url = one importrange in my main formula)

Thanks to some importrange formulas and queries, we can centralize on that file in the master file the necessary information from all the events (see "additional information")

It works fine, but of course when the number of clients got bigger, the master spreadsheet became slower, and it will only get worse with time.

Here is the formula that i currently use on the master spreadsheet: =QUERY({IMPORTRANGE(Clients!$K3;"Data!B16:M");IMPORTRANGE(Clients!$K4;"Data!B16:M");IMPORTRANGE(Clients!$K5;"Data!B16:M"); etc etc etc};"Select * where Col1<>''";0)

Where Clients!$K is the column where we paste the "clients" spreadsheets URL. Of course, we have many more IMPORTRANGE formulas (one per client line)

Thank you VERY MUCH for any help regarding this issue!

ale13 ale13 · Accepted Answer · 2020-01-29T11:36:09

The IMPORTRANGE formula retrieves data from another Sheet by connecting to it, which makes is slower than the others. Therefore, if you want to avoid long waiting times, it’s usually best to minimize the number of these external calls.

Since your Spreadsheet is having quite a lot of data, the best alternative would be to use a script.

Workaround:

You could try to use this script and use a trigger which would fire the execution of it to the time mentioned by yourself instead of using the IMPORTRANGE which recalculates the data each 30 minutes.

function importRange() {
  var ss = SpreadsheetApp.openById("15P9QT4ukwQYnsF7RiHH-e1sSHlK7oyKqhbteiw0_sxQ");
  var sourceSheet = ss.getSheetByName("Data");
  var urlsSheet = ss.getSheetByName("URLs");
  var urls = urlsSheet.getRange("B2:B").getValues();
  var j=1;
  for (var i=0;i<urls.length;i++) { 
    if (urls[i][0] != "") {
      var valuesToImport = SpreadsheetApp.openByUrl(urls[i][0]).getSheetByName("ClientData").getRange(1,1,3,2).getValues();
      sourceSheet.getRange(j,1,3,2).setValues(valuesToImport);
      j=j+3;
    }
   }
}

The script works by gathering all the urls, checking if the urls are not empty cells and if so, it gets the data wanted and pasting it on the Data sheet. Since the data that needs to be pasted into the sheet has 3 rows, in order to be able to execute this operation by choosing the appropriate range without overwriting the cells, the j variable has been used.

If you add a trigger on the above function with the following properties, your script will get executed each day during a time frame convenient to you.

To add the trigger you have to click this icon in your Apps Script project and then configure it using the above settings.

Create trigger

Trigger settings

Some tips to avoid the long waiting times:

Use closed range references - so for example, instead of using B16:M, you could use B16:M500 (assuming that the data you want ends at M500).
Use Apps Script;
Migrate to a database - if your Spreadsheet becomes too slow for your liking, you can always try alternatives - like using a database to store your data and perform all the operations necessary.

Moreover, I suggest you check these links since they might be of help:

Import data from many different spreadsheets (slow)

1 Answers