🆕 G Suite Shared Drives, Background refresh, Lookup for multi relations

Happy Monday, dear Gliders!

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.

image

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.

9 Likes

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.

1 Like

Perfect! Thanks!

Hi @Mark,

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.

Thx,
Paul

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.

1 Like

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. :+1:

:face_with_monocle: Now that I think about it, this is an edge case as users will be editing real fields… not an IMPORTRANGE setting.

I have a Pro app but do not see the option to change the Background Refresh. Which screen should I be able to select this from?

Settings > General

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

All Glide apps automatically reload from the spreadsheet after user input.

I don’t follow what you mean by “2 different pages on login based on data in the spreadsheet attached to the email”.

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:

=ArrayFormula(IFERROR(VLOOKUP(A2:A,Allowed_Users!A$2:B, 1, FALSE) = A2,"X"))

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?

1 Like

@Jeff_Hager Yeah that is what i am doing basically - just a watered down version of the profile sheet you described.

I have not tried a lookup column, I will try it though & let you know how it goes!

1 Like

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 :wink:

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.

We might be working on something like that :wink:

There’s also this video which shows a different way to do user profiles: https://www.youtube.com/watch?v=LFBkC9_yASM

1 Like