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

google-apps-script, google-sheets

Solution

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")');

- Clear the cell with the importdata formula,

- add the importdata formula back in.

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

Problem

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

Original source