IMPORTRANGE function not refreshing new entries in linked Google sheet

Question

My IMPORTRANGE function is not refreshing with new data once the linked spreadsheet is added to. Instead, I am having to cut and paste all of the formulae again whenever I want to view the new information.

I'm using the new version of Google Sheets and I know there were some issues around the release of this with the IMPORTRANGE function.

I currently have 24 columns of data that I'm importing and the original spreadsheet will just keep growing as it is linked to a form. This is the main reason I'm using IMPORTRANGE, as it will help to keep the original spreadsheet working at maximum speed.

What I'd like to know is, has anyone else had a problem such as this, and if so is there any work around (in apps script/another function)? In worst case scenario, is there an apps script about which would clear the spreadsheet and re-enter all of the formulae on open/on a menu click as it really is a pain updating every column every time a new entry is made.

EDIT - Almost all of the cells I'm trying to import are formulated within the original spreadsheet if that changes anything - EDIT

Streaky

Yeah, that's the problem. Mine isn't auto updating. – Streaky

I've manahged to find a workaround using apps script which will clear the whole sheet at the click of a button and then re-enter the IMPORTRANGE formulae into the respective cells – Streaky

Answer 1

In the spreadsheet settings under "File", go into the Calculations tab and change the Calculation Setting to "On Change and every minute". I had to do this on another importrange sheet and it did the trick for me.

Ornithine

that works - as long as I don't increase the size of the named range that is being imported - what doesn't work is if I change the size of the Named Range in the source spreadsheet - any hints? – Dali

Answer 2

What solves the problem? Setting the same owner for both spreadsheets: the one that you import data from and the one where you use importrange formula. I had the same problem. I updated the source and no response on final spreadsheet. After setting same owner for both spreadsheets refreshing takes few seconds

Highcolored

Answer 3

I've worked out an ultimate (and very simple) solution to any importrange problems. Let's say you have your data in Sheet1 and it's being collected from a google form. You'd like to importange it somewhere else but newly received rows don't trigger importrange refresh. The solution is to move the data using QUERY to Sheet2 (in the same file) and then importrange the data from Sheet2 to another file; it's easy to force refresh of the QUERY function using simple macro, below I pasted simplified version of my function:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getCurrentCell().setFormula('=QUERY(\'Sheet2\'!A:Z;"SELECT *";1)');
};

So basically I clear the A1 cell that contains the QUERY formula and then I fill it with the same QUERY formula. I set this macro to trigger every 1 minute. Works like a charm.

Tarra

Answer 4

Click File > Spreadsheet Setting... and check both sheets are in the same Locale and if responces are comming from Jotforms, set the same Locale there too.

Breadstuff

Answer 5

Make a copy of your master sheet and change url of your importrange function. it's work for me..

=importrange(new_master_sheet_copy_url,string_range)

Shaylyn

Answer 6

A trick that seems to work

Instead of

=importrange("Relevant_Sheet_ID","Archive!a1:p259")

Use the below just adding => &"?"&now()

=importrange("Relevant_Sheet_ID"&"?"&now(),"Archive!a1:p259")

Basically, it fools GoogleSheets to think the data set referred is ever changing by adding a timestamp through a now() function.

Feedback welcomed, seems to work for me, but might not be consistent.

Lajuanalake

A generic importrange function seems to be working fine for me now. Maybe Google cracked it since I asked the question, but it might be something to do with it being linked to a form. I've not got any examples open at the minute which are relevant, but I'll be sure to give you a shout when something comes up – Streaky

"This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()" – Camillecamilo