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.
Depends. Could be either/or. If you choose Public with email, then User Profiles are available. If you choose Public, then they are not. Which to use is very much a case by case decision.
Case-dependent, for sure. But for Business Owners (in this case) to have a login, do you mean having the settings reflect Owners being white-listed with password required access?
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)
Also true, but not quite what I mean. What I’m getting at is:
Say you have a Business sheet with three businesses (Business A, Business B, Business C) sorted in that exact order.
Then you use a formula to copy them to a second public sheet in that same order.
Now when you add a RowID to the second public sheet, it will assign the RowID column to those three rows.
Meanwhile a user decides to favorite Business A on the second public sheet.
Later you decide to change the formula that fills the public sheet so that it sorts in desending order. (A, B, C becomes C, B, A). The order of businesses change in the second public sheet, but the order of the RowIDs don’t change at all.
Now the user that had set their favorite for Business A has their favorite set for Business C, because that favorite is attached to that RowID.
@Jen_NYCP I’m just trying to prevent a headache because it’s not IF, but WHEN it happens. Trying to track down why all favorites broke for all users would be a nightmare if you have no idea why.
With the two app method, you could get rid of the duplicated sheet altogether, and possibly use protected columns on the public app. Again, not sure if protected columns is a paid only feature. Guessing it probably is.
I think rather than continuing down a rabbit hole of workarounds upon workarounds, it might be worth stepping back to see if there are better ways to approach this. Unfortunately, there is a bit of gray area in Glide where you have to choose if you want to sacrifice some data security to have an app available to the public, or have proper security, but the only option becomes a private app. I think you are getting stuck deep in that gray area with all that you are trying to achieve with a single app. I’ve hit that same wall too in one of my apps where I want to secure data with Row Owners, but I want to have the ability to add multiple owners to a row. For now I had to remove Row Owners on a couple of the tables to make it work how I want, so I may not be able to go widespread with my project and only use it personally instead. I may eventually have to step back and rethink the entire flow.
There are a few cases where duplicating a table can be useful and I don’t have a problem with that, but any time you start mixing the duplicated data with other static data, then that’s where you can start to have problems. I think rather than trying to duplicate data, you may be better off rethinking your flow and instead have data separation. You can have your main Business sheet that can be used for Public, but take out all of the private data and put it in a separate sheet. Then use some sort of RowID, Key, or Identifier to link the public business data to the private business data. On the private sheet, you can then add your Row Owners or whatever else you need for data security. Then if a business owner signs into your public app, you can have a relation between the public business sheet and the private business sheet. If the relation returns anything, then you can allow the business to edit their public data or their private data. I think this would still get you the functionality you want without the hassle of duplicating data, using scripts, or coming up with more wild workarounds.
HI @ThinhDinh I was just scanning through this thread because the favorites in one of my sheets uncheck themselves on their own as soon as I check them! I “think” I may have previously deleted the RowId column and then re-added it. Do you think that’s the cause of my issues? Cheers.
I fear that may be the reason. I’m not entirely sure if Glide just attaches the favorite to the specific rowID string, but if that’s the case then I think that might be the reason why.
I haven’t tried this again, can you try exporting your data from the app (with the new option Glide added a while ago) to see if favorites data is in there?
@Jeff_Hager Thanks Jeff! I thought the Public Sheet RowID would actually move with the business name.
The main business sheet is linked to a google form, so not sure how I can separate that data as it comes in.
I also have to see if all the business data I need, plus chat, business hub, and user profiles sheet can fit in a free app. Right now it’s overflowing.
Apologies for the delay. My export just isn’t appearing. This is all a little frustrating. Are you aware of any way to “reset” the favorites column? I wonder if I delete the existing rows then add as new again?