Importrange

@spencersRus No, not from my experience.

We’re introducing a periodic background refresh for Pro apps in the coming days that should make the IMPORT (IMPORTXML, IMPORTRANGE, etc.) and real-time formulas like NOW and google finance formulas refresh every few minutes. We’ll announce this in a day or two, please try it if you have a Pro app that uses these formulas.

3 Likes

Hi guys,

Have you ever tried this?:

  1. Open you Spreadsheet and click File -> “Spreadsheet” settings.
  2. In “Recalculation” section, choose your best setting from the drop-down menu: On change / On change and every minute / On change and every hour.
  3. Click “Save Settings”.

It’s the 1st thing you should do when you are working with functions based on changes.

Days ago, I carried out a full test with a sheet with 150.000 records to verify the funcionality and speed of Query() and ImportRange().

In my tests, a change in my raw data caused an updating 2-3 min later in my other sheets where Query() function is used. Of course, more data -> more time // few data -> faster updates. I did’t use any script or Add-In for this, I just let Google engine work alone.

As a tip and curiosity, If I modify/create a record in my raw data and later, go to a sheet where a Query() function is and wait for the updating, my laptop and Chrome crash after 15 min. Instead, If I close my spreadsheet after I modified a record and reopen it 3-4 min later, all my sheets and updated automatically without any crash in my Windows 7.

I hope it helps you.

Saludos

2 Likes

@gvalero Do some tests where you don’t have the spreadsheet with the ImportRange() formulas open in a browser. It won’t refresh the data from the other sheet no matter what settings you have.

1 Like

Thank you All!

This worked for me:
Install time based trigger
copy now() function from one cell to another

I don’t think so George!

Try using this:

=Query(ImportRange()) and you will see how fast the data is updated.

In my spreadsheets, If I modify or add a new record (I have 150.000 records in my source spreadsheet), my other sheet gets new data in less than 1 min.

That is my sintaxys to get it:
=query(IMPORTRANGE(“1mEoXgU2StLrYa9TbvX00RdGFHsa1RFV_WP2Tnc7dtnw”,“Sheet 3!A1:F9”),"Select * ", -1)

I set up a timer trigger to change the date/time in some cells and I can see how my query retrieves the new data from my source spreadsheet every 1 min without problem.

Let me know if this solves your problem.

Saludos

Gavp

2 Likes

Glad to hear your combination of query and importrange is working for you. It’s too late for my solution at the moment. I’ll keep it in mind for future projects.

Edit: @gvalero @Ralf I did some testing and can confirm that if you write a script that touches the sheet involving formulas that deal with the NOW() function the sheet will in fact recalculate (even if not open in a browser) and the IMPORTRANGE() will refresh with new data. You have to create a Google App Timed Trigger to run your script function every min. From what I saw it still is dependent on Glide’s refresh cycle of up to 3 min, but I didn’t run a timer on it. However, in my opinion it appeared to take longer than 1 min to refresh at times.

1 Like

@George_B That is exactly what I did, thank you!

@gvalero It is the triggering of the now() function which solves the refresh problem, the query function has no relevance!

@Ralf Can you explain your steps on how you got it to work? I’m not sure I fully understand what I have to do to get it to work

@Wolfieee_Wolf
This is the installable trigger which you have to run once:

function everyMinutes() {
ScriptApp.newTrigger(“myFunction”)
.timeBased()
.everyMinutes(1)
.create();
}

This is the script which copies the now()function from L3 to L2 every minute:

function myFunction() {
var ss = SpreadsheetApp.openById(‘1Y9jOorP-HXvI7X4PiPhZQ0_ynxk-bB3Bmf-nOLTh’);
var sheet = ss.getSheetByName(‘NameOfTab’);
sheet.getRange(‘L2’).activate();
sheet.getRange(‘L3’).copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

Put the now() function in L3.

L2 and L3 could be any free cell

1Y9jOorP-HXvI7X4PiPhZQ0_ynxk-bB3Bmf-nOLTh is the Workbook ID

Thanks for the code.

I can’t seem to get it to work.

keeps saying Illegal character. (line 2, file “Now Copy”)

I just copy and pasted it. could that be the issue?

It was a copy and paste issue.

fixed now. i just typed it all out and it seems to be working fine

How in the world did you guys get this to work? I tried using the script editor. Is that where I need to be? Or do I post this code into a cell??? Please help if you can.

@Ralf when you put code into these messages you need to put them in as a code block. Surround the code with 3 backticks. Otherwise when someone copies and pastes it they end up with invalid string quote characters. Try it yourself first with the code you posted, copy and paste it into your script editor and then copy and paste my edited one.

function everyMinutes() {
  ScriptApp.newTrigger("myFunction")
    .timeBased()
    .everyMinutes(1)
    .create();
}
// This is the script which copies the now()function from L3 to L2 every minute:
function myFunction() {
  var ss = SpreadsheetApp.openById('1Y9jOorP-HXvI7X4PiPhZQ0_ynxk-bB3Bmf-nOLTh');
  var sheet = ss.getSheetByName('NameOfTab');
  sheet.getRange("L2").activate();
  sheet.getRange("L3").copyTo(sheet.getActiveRange(), 
           SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

This is a screenshot of what this editor looks like when I edited the Google Script you provided after I surrounded it with the backticks and changed the single and double quotes with the proper ascii characters.

@BlakeACroft @Wolfieee_Wolf the above code may solve your issues.
!! DISCLAIMER !! I did not write or test the above code. I’m just making it easier to copy and paste into a Google script of your own. ))

1 Like

What is this mess it is asking me about changing to a cloud platform or something like that??

@BlakeACroft Hard to help when you refer to the message you are receiving as “something like that” !

I suggest you take a look at some Googles scripting tutorials and get the basics down before you dive into the deep end with some sample code that you get from a board like this.

This series is pretty good: https://www.youtube.com/playlist?list=PLv9Pf9aNgemv62NNC5bXLR0CzeaIj5bcw

1 Like

I got a “Illegal character. (line 11, file “Code”)” error

Put double quotes instead of simple quote to the getSheetByName function.

1 Like

@Christophe_HK @BlakeACroft It is not the single quote that is giving the error. I missed the Illegal characters around the “L2” and “L3” when I copy, pasted and fixed the original code. For some reason this forum software replaces all the single and double quotes with some other character that looks like single and double quotes but is not the same ascii values I guess. Javascript, which is what Google Script is based on allows either single for double quotes to designate a string.

1 Like

Thanks for this guys. I will investigate implementing on my apps that use importfeed.

I don’t understand why my PRO apps are not refreshing the today function. My app.native.zone before would show today’s moon when I visited from browser. Now I have to open the spreadsheet for it to be updated in the app. @david , did this feature get cut from PRO apps? Or should do I update my method?