Google sheets importData, importRange speed / interval - ways to improve it?

3
votes

According to google documentation (https://support.google.com/docs/answer/58515?hl=en) current refresh/recalculate times are as follows;

To change how often some Google Sheets functions update:

Open a spreadsheet. Click File > Spreadsheet settings. Under "Recalculation," choose a setting from the drop-down menu. Click Save settings. Note: External data functions recalculate at the following intervals:

ImportRange: 30 minutes ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour GoogleFinance: 2 minutes

Is there any way in which we can speed this up? For example deleting the cell and then replacing it will force a refresh of the data. Maybe this could be done via a script for example. Can a script even be used on its own to pull in data from another sheet?

Does anyone have any ideas of how this could be done? Ideally I want a refresh of every 5 minutes.

Many thanks

2
google-apps-scriptgoogle-sheets

2 Answers

10
votes

I actually found a solution which seems to work quite well;

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1").getRange('A1').setValue('');
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1").getRange('A1').setValue('=IMPORTDATA("https://www.dropbox.com/9329382983/file.csv?dl=1")');
  1. Clear the cell with the importdata formula, 2. add the importdata formula back in.

You can then set this a script to run every x minutes.

1
votes

from my understanding, everything recalculated when values are added or edited. so maybe a script that will write a random value to a cell you aren't using (ie: Z500 or something)
then set a Time-driven triggers to trigger that script
MAYBE this'll work?