I’m trying to make a multi-user calculator app, and have built vlookup() functions into the G-Sheet to check an item’s price in a separate sheet; ultimately so
(1) the user can select some items and their relevant quantities
(2) the G-Sheet searches for the item(s) price(s) (prices stored in a separate sheet tab)
(3) the G-Sheet calculates total cost and reports it in the app
I want every user to have their own unique instance of the app, so they login and the items they previously selected are retained, and did this by setting the app public with email.
I setup all the formulas and copied them 150 rows down (so calculations work for the first 150 users), but whenever a user signs up to the app it creates a new entry at row151 - screenshot below for ref
- Is it not possible to preload formulas into rows that Glide will use for users?
- I’ve seen some apps build formulas into the columns directly in Glide, selecting “Math” in the column type for eg, must I do this to ensure the formulas are applied to every entry in that column? Is there a work-around? How do you build complex functions into Glide like vlookup() functions?
I’ve seen many discussion threads of multi user and calc apps (link below for eg), and have the app working where all users see the same entries at the same time, but I can’t seem to find concrete steps on putting together a multi user glide app
Thanks in advance for any tips
Eg thread: Multi User Calculator - #12 by Koenner
I think it would be much easier if you built that functionality into glide with relations and lookups to your price data and use math columns to do the calculations. Trying to do something like that in a google sheet is going to cause problems and be a lot more work. Plus you would have lag in your app because it would have to sync data to the glide server, then to the google sheet, perform the calculation, then sync back to the glide server, and finally back to the app.
To quickly answer your question, when adding a row to a sheet that is already populated with formulas, Glide will always look for the first empty row (rows with formulas are not empty). You would have to design your flow so you are actually editing a row instead of adding one for that to work, but you could have a lot of collisions if multiple users are in the app and happen to edit the same row at the same time.
Also, arrayformulas do help with your current problem, since it automatically applies the formula to all rows and any new rows, but you would have to delete all empty rows to ensure that new data isn’t placed in a new row at the bottom of the sheet. But, I’d still recommend moving all of the functionality into glide for simplicity and speed.
Just want to back up what Jeff said. If you’re new to Glide, it all might seem a bit daunting. But Jeff speaks words of wisdom - arrayformulas might solve your immediate problem, but if you continue down the path you’ve started with this there will come a time in the not-too-distant future where you’ll slap yourself on the forehead and wish you had taken Jeff’s advice.
There is a learning curve, but trust me - it’s worth the effort.
Gotcha. Noticed that tends to be the behaviour so I’ll definitely research and implement relations and lookups, I thought that might be the way to go and you’ve both confirmed that!
Thanks for the advice, and additional context too. Makes these things much easier to wrap your head around!
Yes… Always. Listen. To. Jeff.
You may want to check out user specific columns too. Working with user-specific columns - Glide Library