IMPORTRANGE function not refreshing new entries in linked Google sheet

6
votes

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

5
google-apps-scriptgoogle-sheetsgoogle-formsimportrange
Yeah, that's the problem. Mine isn't auto updating. - kjexley
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 - kjexley

5 Answers

1
votes

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

1
votes

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.

-1
votes

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.

-1
votes

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.

-1
votes

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)