Not entirely correct.
You can set your spreadsheet to calculate automatically every minute in the setting or on change etc.
I use importrange in many if my spreadsheets and they get auto update all the time. Just make sure you build it correctly to not over the limit of the importrange
Not entirely correct.
Nice. Could you share with us how to do it?
It means that I can have a perfect copy of spreadsheet available to another and I can put email tracking (Google Script, Zapier, Integromat, etc.) to use it even I don’t open the sheet?
Do you now what is the limit of IMPORTRANGE? Is there á documentation?
To answer the first part. Simply yes. Pending on your triggers for the emails you can maybe avoid script and use formmule add on.
As for the limit, not sure but I believe you could search it I. Google.
I usually limit the importrange formula to about 1000 rows and 50 columns and than add a new importrange formula in row 1001 etc.
I’d like to see how you pull that off. I have had no luck with Importrange when the spreadsheet is not open in a browser.
Have you set up your spreadsheet to update every minute?
Later I’ll be at my PC and I’ll make example.
I can tell you that as an example of a use I’m doing is that I have a dashboard I prepared for a customer which is pulling data from 6 different spreadsheets and is also sharing data with 2 more files and its working perfectly.
Another trick you could do is hide a NOW formula somewhere in your file that will encourage the every minute update to work
The problem is that the original spreadsheet has a lot of charts and graphs who’s published URL changes when making a copy of the original.
Also, I have other spreadsheets that link to the original, thus I want to keep the copied Glid app linked to the original spadsheet.
@yinon_raviv I’m still surprised that this is working for you without the sheets in question being open in a browser window. It is my understanding that the onchange event and the recalc sheet every minute spreadsheet setting, only get triggered when the spreadsheet is open in a browser window. To validate this, take those two spreadsheets you have in your example above and create apps for each one. The Original one allow edit within the app, and the one attached to the one that has the Importrange(), pulling the range from the original is view only. Open both apps with the app link, not in developer mode, with both spreadsheets closed (make sure they are not open in browser tabs either). What you are saying is that if you change something in the Original via the app, it will show up in the other app that is linked to the spreadsheet that basically only has an importrange(). Maybe things have changed with Google since I tried this, but for me the importrange() did not refresh so I didn’t see any data updating.
That’s a good point. I’ll test it out today and will let you all know.
I can say that the importrange for sure update when the files are close but not sure if glide app will update itself if the files are closed. Interesting…
see the gif.
in it i present the app, original CRM file, Copy of the CRM file which all its sheets are importrange from the original and a demo dashboard I built with Google Sites.
in the scenario, I add 1 customer to the app while all files are close and show how the demo dashboard which is connected to the copy CRM file is being updated instantly.
I believe this is working for you because your Dashboard has the Google sheet open that it is connected to. Your test was not how I outlined the test should be done. If it works for you that is great, I just wanted to point out that I think what Glide has stated in their documentation is still correct. https://docs.glideapps.com/all/reference/using-sheets/importrange
In my case I had two spreadsheets. One spreadsheet didn’t have any app connected to it. That spreadsheet was updated and maintained by a property manager. My Glide app spreadsheet, had a sheet that used an IMPORTRANGE() function to pull in the data from that other spreadsheet. When running just the Glide app, changes to that external spreadsheet by the property manager were not reflected in the Glide app until I opened the spreadsheet attached to the Glide app (the spreadsheet where the IMPORTRANGE() was) in a browser. That caused the recalc and updated the sheet that has the IMPORTRANGE() function.
If you think about it, what you are saying is that for every spreadsheet ever created by anyone on Google, Google is polling everyone of them every minute and doing a recalc. And if they happen to have an IMPORTRANGE() function, that recalc. is cascading down to all the connected spreadsheets. I do not believe that is the case. The only time a Google Spreadsheet is recalc’ing is when it is open in a browser.
I will point out that it took me a long time to realize this while I was developing the app because I had both sheets open along with the app. Once I was pretty far down the road in development did I start to run into delayed “refresh” issues and finally saw the documentation from Glide. https://docs.glideapps.com/all/reference/using-sheets/importrange
I haven’t personally tested importrange, but I think the recalc every minute option is only useful for things like Now() or Today(). I use those functions to clear out a date column in my app so a past due date no longer shows in a calendar. Could be wrong as I often have the sheet open, but I haven’t seen otherwise in the past several months.
You can set up a timed trigger, to run every minute, that will run a script function of your choice that could be used for all kinds of processes. They can be accessed within the script editor by clicking on the clock icon (or Edit/Current project’s triggers).
You have much more experience with apps than me so you’re probably right. I just offered a potential workaround to the transfer problem.
Will check later when the PC is closed to see if the dashboard getting updated when the DB is closed as well
This is what I am trying to do as well! Did you get an answer?
I created the app with my current work account. The app has been distributed to the entire school community. I am leaving this district for an entirely new job and I want to make one of my colleagues the new owner of the account so that all of the links to the app that are already distributed don’t break.
Is this possible?
In addition to the steps below, I would change the URL for your app, copy it, then set the URL in the new app to match what it used to be in the old app. I think this should keep all of the settings. I would also consider setting up a gmail account for the school, so it’s not tied to a specific person.
However, it still doesn’t solve the problem that the copying the app will also make a new copy of the spreadsheet and not use the original spreadsheet.
And that is a problem because? Are multiple apps linked to the original spreadsheet? If that is the case, then as @Jeff_Hager said in his suggestion, this method would not work.
And that is a problem because?
See my comments above why this is a problem
What if you add a function to activate the script that sends email and require the new user to call it from the menu?
This should make Google to ask the user to grant permissions to send email from his account.