Just be aware that when you start using formulas that pull data from other sheets, the number and order of rows can fluctuate without Glide’s knowledge, and the true Row ID column WILL eventually become out of sync from the data that is being pulled in via a formula. You may favorite one business today, then delete a different business from the source sheet, which reorders the data in the copied sheet. All of a sudden your favorite on one business is now on a different business, because in Glide’s eyes, the true row id used for favorites didn’t change, but the associated business for that true row id changed in the copied sheet.
In situations like this, I recommend using scripts to fill the second sheet, but only let the script copy over the data without ever altering the order of existing rows in the second sheet. It would have to check for existing businesses ID’s and Add, Update, Delete as necessary. Don’t ever do a straight carbon copy of the data and let a business potentially get detached from the true Row ID it was assigned by glide in the second sheet. It will look good now, but will absolutely break later and you’ll spend too much time trying to figure out why.
Also, would this break my app since I am changing the data and headers?
Last time, I had to change this sheet from lookups to excel arrays as I have now and it cleared all my components. I had rebuild my entire app. Thanks!
That looks like more of a direct sheet copy script. I’m talking about a script thay only adds new rows that haven’t been added yet, but also checks for updated data or deleted data in the master sheet. I used to have a script set up to copy student names to a few other sheets, but later changed to copy student ID’s instead. Finally I ditched the script altogether and changed my app flows, so I no longer needed the script.
The script I used would only add missing students to other sheets if they didn’t already exist in those other sheets. It did not touch any existing rows or headings. It only copied a single column to identify the student, but there were other columns of data related to each student. I used this because I needed the student names/ID’s to exist in the other sheets, but I could not risk the student name/ID jumping around in those sheets if the order was ever changed in the master sheet. Using a direct copy formula or unique formula would have caused a hole host of issues for me because the student names/ID’s would eventually become misaligned from the other columns of data in those other sheets.
These are a few posts that are related to the formula I used to use. The third post is a modified version of the original script to fit a particular situation. This script ONLY adds missing student names to other sheets. It does not handle any updated names or deleted names. If a name would have ever been changed, then it would copy to the other sheets as a new name with the other columns empty instead of updating an existing row. That’s why I eventually migrated to using ID’s instead of names, but like I said, I don’t use any script at all, because I changed how the app works.
Ok so there is no script for this out there? I have to create it myself from scratch?
I just need the template then I enter the information specific to my sheet, kind of what I do for Google Sheet formulas.
No, I don’t have a specific template other than what I’ve provided. You would have to write it to your needs or do some google searching to see what else is out there. I try to avoid scripting if at all possible. Coding is my day job and Glide is a nice way to get away from writing code for awhile. Unless someone else wants to jump in and put something together.
At this point I’d probably consider different approaches to your app flow, because it seems like a lot of work just to get favorites to work.
Oh, I figured out favorites already. Added another ROW ID for the public sheet.
Essentially my app is built from the public sheet. It’s a copy (Google Sheet Array) of my business sheet, but without the email address and another private data column. I created this second sheet so this private information isn’t downloaded by users. You were saying data will move around when altering the business sheet.
That’s the whole problem. Try setting a favorite on the first business (public sheet). Then move that business from the first row to the the last row in your business sheet. That will alter the entire order of all the businesses in the public sheet, but the row id’s will not move with that changed order because the rowid column is separate from the data coming from the business sheet. That will break the favorites for every single one of your users and they will instead have favorites set to businesses that they didn’t set a favorite for.
That’s why I say it’s a lot of work just to deal with favorites.
If you can absolutely guarantee that a business entered on the business sheet will never ever move or be deleted, then you won’t have a problem and favorites will work on the public sheet, but if there is even a slight chance that the order of businesses could change, or if you would ever add a business in the middle of the list or delete a business, then you will most certainly have issues down the road and will spend a lot of time trying to figure out why everybody’s favorites are all of a sudden not working correctly. That’s why I don’t recommend mixing static column data with data that comes from another sheet via a formula unless all static columns are calculated or determined based on the data that’s coming from that other sheet. That’s just not going to happen when Row ID’s come into play.
I would reconsider separate apps. One for the public and one for business owners. I think you can protect certain columns from public view for the public app, but then keep it accessible in the business app. I’m not sure, but it may require a paid plan to use protected columns. That way you wouldn’t have to deal duplicating a sheet.
Thanks for that! Two apps are really not an option for me, it wouldn’t make sense for what I’m doing.
I’m just wondering what everyone is doing to hide their private columns, I’m sure it’s not two apps. Row owners would work but since my app is a directory type of app those rows need to be seen by all users.
I guess what’s left is looking into getting a script made.
Jen - I wouldn’t be so hasty to dismiss the idea of making two app. From following your journey building this app, I suspect that having two apps might solve many of the problems and challenges you’ve been facing.
Using two apps is more common than you might think. I’ve started doing it for some of my customers, and I know that @V88 does it as well. The idea is that you have one app that is Public and requires no login or authentication at all, and a second that requires a login.
In your case, your Business Owners would get the second app, and everyone else would get the first.
But won’t the second app (sheet) still be using data copied from the first app (sheet)?
Im also getting a chance to look over my public glide sheet, if a row is deleted from the business sheet, I’m not seeing how the Public Row ID won’t carry over when that row gets deleted from the public sheet. Is it not favorited using the Public Row ID?
Here is my understanding based on what I know of your setup, and the comments that Jeff has made…
In your Business Owners sheet, you have a “true” RowID column
In your Public sheet, you use an arrayformula to copy the RowID from your Business Owners sheet. (NB. This is a RowID in name only - it won’t function like a Glide RowID column)
In your Public sheet, you also have a “true” RowID column
The problem comes when you enable favourites in your Public sheet. Because you have a “true” RowID column in that sheet, Glide will automatically use that as the key for favourites - and you can’t change that.
So if a user sets a sets a favourite in one sheet, there is no guarantee that they’ll see the same favourite it the other sheet - because they are using different keys.
(PS. It’s quite possible that I’ve got this completely wrong - I’d actually prefer that you take advice from Jeff on this. He’s much infinitely more knowledgeable in this area than I am)