Weāre excited to tell you about three features we just shipped:
Lookup for multi-relations
Until now the Lookup computed column would produce the value of a column in a single relation. You can now use lookup to produce all the values of a column in a multi relation. As an example of how this might be useful, say you wanted to show a photo carousel of all the employees working in an office. You can now lookup the Photo column in the Employees multi relation, and bind the resulting column to an Image component. Another use case is to get emails to filter by.
Background Refresh
So far, Glide would refresh your app data from the spreadsheet whenever the spreadsheet was manually edited. If you used formulas that produced different values at different times without requiring manual edits, such as NOW, IMPORTRANGE, or GOOGLEFINANCE, your app data would not stay up-to-date. With Background Refresh, you can tell Glide to periodically refresh your data from the spreadsheet, no matter whether there was a manual edit or not, so those formulas will now stay up-to-date, too. This is Pro feature.
Note that due to the way Google Sheets works, if you use an IMPORTRANGE, and the spreadsheet that is imported used something like NOW or GOOGLEFINANCE, that imported data can still be out of date.
G Suite Shared Drives
You can now create an app from a spreadsheet thatās in a G Suite Shared Drive. This is a Pro feature, so to publish the app youāll have to upgrade.
I have a couple of followup question with the new Lookup for multi-relations.
Is it safe to assume that this new feature could be used as a replacement for an array of columns (image 1, image 2, image 3, etc.) and that it can be used for any function that supports an array of columns?
Iām trying to find a solution to the Tasks demo in my concepts app. I had originally tried to use a filter to check if the logged in users email is contained in a list of emails in a column, that I build from a concatenated and delimited list of emails. The concatenated list comes from users who have completed certain tasks. Unfortunately the filter function currently does not let me do that since there is no global user email value I can compare the column to using ācontainsā. What Iām thinking of doing instead is using Per User Data on a list that points the Lookup column. The lookup column would contain an array of emails that I get from the form responses of users who completed a specific task. If I understand the Lookup with multiple correctly, I think this would work, at least for the list of completed tasks.
That leads me to my last questions. My example uses a List Relation. I donāt see a per user data option for a List Relation like I do with and Inline List. Is this by design? Also, I see that I can point a List Relation to a Lookup Column, but I have no options for setting the Title, Details, or Image. Not sure if this would be useful or possible. Just an observation.
I think this will be very useful to not have a set number of columns for an array. Especially with the photo carousel.
Yes, if you look up a column with primitive values this will work anywhere youād use a column array.
If I understand your users email question correctly then yes, that should work, too.
List Relation is a very neglected component and needs to be completely redesigned or replaced. I donāt know when weāll get to that, but when we do weāll fix all those issues.
On background refresh, Iām playing around with IMPORTRANGE functions on a child app from a master sheet / app (for the want of a better term)ā¦ so indeed running into the refresh kinks for them. I suspect that I simply need to trigger a change / update on the spreadsheet manually.
Any hot tip / recommendation on achieving this in your experience?
Iām investigating this path link but thought Iād ask too.
Do you have background refresh enabled? If you do, it should update the imported data every 3 minutes or so automatically.
The only issue might be that the imported sheet only changes via functions like NOW and IMPORTRANGE. In that case we canāt easily help you, thatās twice removed from the original sheet. Basically it will (should) work if the imported sheet is edited by hand.
Ahhhh ok, thanks for such a quick response. Iāve taken a look and it looks like weāre firing nicely now having discovered Spreadsheet Settings --> Calculation --> On Edit / Every Minuteā¦ so long as the sheet is open. Then I dropped a =now() function with a simple calc et voilaā¦ I can live with that without getting script heavy.
Now that I think about it, this is an edge case as users will be editing real fieldsā¦ not an IMPORTRANGE setting.
Is there a way to make it quicker than a 3 min refresh (ex. on user input/sign in it refreshes data?) having an issue with the current design of my app (2 different pages on login based on data in the spreadsheet attached to the emailā¦) Thank you!!
It shows up in the spreadsheet immediately, but for it to show up back in the app takes up to 3 min sometimes. Iāll share some screenshots.
This is what the data looks like on glide - basically, we copy the App:Logins tab emails & bring them to a āwelcomeā tab in the spreadsheet with an array formula. In the next column, we run this formula:
To check & see if theyāre a paying member or if I have input them manually, in which it will say true or false if they are & X if theyāre not found in there. This part decides which screen the user shows, so when they login their email goes to App:Logins, it takes a few min for it to populate & show the correct page.
These are the 2 different pages the users will see, the first one is once the user logs in for the first time. The second is after they have paid or input manually on the backend.
@Mark If that input causes significant refreshes on other tab sheets via some arrayformulas and or custom functions does the data refreshed back to the firebase tables reflect this? I have a few extensive custom functions that can take up to 20 seconds to complete.
I think what would be benificial is a spin off of the App:Logins sheet. It would be helpful if Glide created a Profiles sheet with unique emails, UUID, initial sign in timestamp, and last sign in timestamp in the first 4 columns. This would be a sheet that is accessible through the data tab. Then we could add any additional column as needed for whatever we want, such as relations, images, and any other profile related items. When a user signs in for the first time, glide would check if the email exists or not, and then only add a new profile record if an email already exists in the profile sheet. That way we would immediately have access to the profile without building our own profile sheet from the app:logins sheet, using scripts, and waiting for the new record to be synced back to Glide.
I donāt know how well that would work in this particular case @carrabre, but have you tried using a Lookup Column in the data tab instead of a vlookup in the sheet?
Glide will reload from the spreadsheet very quickly after the edit happened, so if your update takes 20 seconds, it might not make it. Itās possible that Google Sheets will return an error in that case, in which case Glide will retry after a while. Iād love to hear from you what youāre seeing
The app Iām working on is mostly a spreadsheet app for youth wrestling matches. How much of Glide will be involved is still being decided. Iām still hashing things out but thought of it in regard to your answer when some of the crazy calcs Iām doing were taking so long.
Could you share your app with me? Then I can at least take a look at whether the delay is due to the reload taking so long, or whether itās not being triggered.
Note that it is expected to take up to 3 minutes (plus the time it takes to reload) before you see a change that you made in Google Sheets. Changes made via the app should trigger a reload immediately.
Now that I think about it, this is an edge case as users will be editing real fieldsā¦ not an IMPORTRANGE setting.
