Google-sheets – Does IMPORTDATA() refresh when an Apps Script runs even if the sheet isn’t open

google sheetsgoogle-apps-scriptimportdata

We have a Google Spreadsheet that pulls data from an external source via an IMPORTDATA() function and then a Google Apps Script that is on a timed trigger that performs other things with the imported data.

It's failed a few times which makes us wonder whether the IMPORTDATA() function is refreshing the data if the sheet is not manually opened? Or in other words when the Apps Script fires from it's timed trigger does the sheet automatically refresh it's formulas?

Best Answer

The IMPORTDATA() function has a latency of approximately 1 hour, as explained in this post on the Google Product Forum.

You mentioned the fact that a script was being fired by a time based Google Apps Script trigger. I was thinking, if you're using a script in the first place, why not fetch the CSV file as well, like so:

Code

var url = 'url to public CSV file';
var text = UrlFetchApp.fetch(url).getContentText();
var csv = Utilities.parseCsv(text);

The var csv is turned into a 2d-array and can be used like any other array. This way, you always have the latest CSV file.

References

Related Solutions

Google-sheets – How to link two sheets so that entering a numerical value in one cell of Sheet 1 produces an ‘x’ in the same cell of Sheet 2

From inside the Google spreadsheet, go to Tools -> Script editor then try this:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 1) { //checks the column
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var XSheet = ss.getSheetByName("Sheet2");
     ss.setActiveSheet(XSheet);
     SpreadsheetApp.getActiveSheet().getRange(r.getRow(), r.getColumn()).setValue("X");
     var MainSheet = ss.getSheetByName("Sheet1");
     ss.setActiveSheet(MainSheet);   };
 };
}

This assumes your sheets are named Sheet1 and Sheet2, as is the default, and it's set to only watch column E (5) on Sheet1 for edits and only add an "X" to the corresponding cell in Sheet2.

Google-sheets – How to get Google Sheets to auto-update a reference to another sheet

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.