Importrange

[Ralf]

Understand that changes in importrange do not occur in Glide without reloading the sheet. What is the best way force updates automatically?

[George_B]

You would need to create a script that does the same thing as importrange, and run it with a Google App Trigger.

[Ralf]

@George_B Thanks! I will use a trigger to “refresh” the IMPORTRANGE formula. What is the best way to refresh a formula?

[George_B]

I don’t know of a way to invoke a refresh. What I said was that you would need to write a function that does the same thing as the IMPORTRANGE formula does. I posted something along those lines back in the Spectrum world. Here is a link to that post:

I missed the caution about using IMPORTRANGE · Glide

[spencersRus]

Does this help the import range? Works with images.

How can I cause my images to update whenever they change?

In the glide together meeting today, someone asked about using Dropbox and how glide was caching pics. You asked how this might be used. Here is what I want to do: I want to update the pic as “App updated on” - and have the date when I updated the app as the picture. So, when I change the pic. Then it will be updated on the app. (Just my thought) Potentially I have a friend that has a “student of the week” picture that would change periodically. So she would would need the same link, but the …

[George_B]

@spencersRus No, not from my experience.

[david]

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.

[gvalero]

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

[George_B]

@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.

[Ralf]

Thank you All!

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

[gvalero]

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

[George_B]

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.

[Ralf]

@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!

[Wolfieee_Wolf]

@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

[Ralf]

@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

[Wolfieee_Wolf]

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?

[Wolfieee_Wolf]

@Ralf

SpreadsheetApp.CopyPasteType.PASTE_NORMAL,

It was a copy and paste issue.

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

[BlakeACroft]

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.

[George_B]

@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. ))

[BlakeACroft]

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