Instead of adding tabs for each user. If you have one tab with the headers, “Header/Column Tab” in the first row, each user could be added in the rows below maybe by using a form at the beginning of the app. The form would reference the “Header/Column Tab” and consist of another field which captures the users email automatically. This would allow you to use the user data option and keep the same Glide layout for each user. How are your users currently being added to your app?
The rows must be visible to multiple users. We have two people following the same lead - first one sets up the meeting and the second one meets the customer.
Just a couple of things to point out. My original suggestion does not require the use of Per User Data, but there would be no restrictions in what a user can see. If you don’t care who sees data, then I think you can accomplish what you want with the use of references. Keep in mind that you can use an array of references. If you need to restrict who sees what data, using Per User Data is still an option. Using an array of emails, you can give multiple people access to the same record (lead). Without seeing your sheet, it’s hard to picture what you want to achieve. I’m just giving my personal opinion of how I would like it to flow. I think having multiple tabs at the bottom of the app, that can dynamically change, can be a bit of a headache when you have to modify the app for each new sheet you create. If you have a simplified version of your sheet, along with a more detailed explanation of how you expect the app to work, I think we could get you set up pretty easily.
@Jeff_Hager do you mind elaborating on this? I want each record to be editable by multiple users as well. Would that be possible with references?
The thing I am struggling with is if I pull all the data into one sheet, it will soon run to >5,000 rows in that sheet with each record belonging to 2 individuals (Every day, about 200 records/leads are input into the spreadsheet.) The spreadsheet itself may become unusable unless accessed strictly via the Glide interface. My team wants to continue using the spreadsheet also at least for some more time. Is there any workaround?
I would need to see a sample sheet and get clarification on what users are supposed to see. Do you plan to have all data visible to everyone (then I’d create a sheet with a list of users and use references), or would you like to use per user data so only 2 users can see their own leads (then I would create 2 email columns for each lead (Email 1, Email 2) and use the (Email) column for per user data)?
With a data set that large, you are looking at maxing it out within 6 months to a couple years. I know google sheets has limits, but I have no experience with datasets that large and I don’t know how Glide would react, although I think it can handle a lot:
- Up to 5 million cells for spreadsheets that are created in or converted to Google Sheets 40,000 new rows at a time.
- Maximum number of columns of 18,278 columns
- Number of Tabs: 200 sheets per workbook
- GoogleFinance formulas: 1,000
- GoogleFinance formulas ImportRange formulas: 50 cross-workbook reference
- Formulas ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data
- Maximum string length is 50,000 characters
I think you would also run into problems since you still want to use the sheet the way your user are used to. You could probably take the data from several sheets and merge them into one for use within the app, but you wouldn’t be able to update within the app and have it reflect on the individual sheets. The alternative is to keep the sheets separate, but then we are back to where we started. I think it comes down to either your users learning new procedures and using the app, or stick with what you have been doing in the past. I don’t see a good solution that gives you the best of both worlds. I would consider maybe making a copy of your sheet and joining everything together, then creating a test app against it, just to see how the app reacts to a large dataset. The only way to have Glide seamlessly update with new users and data is to have a spreadsheet where you aren’t always changing the headings or adding/changing spreadsheet tabs.
I know this doesn’t really give you an answer, but it might give you something to think about. Like I said, make a copy of your sheet and play around with building an test app to see what works or doesn’t work. That way, your aren’t messing with your production data until you are ready to build an app against it.
Thank you for the thoughtful reply @Jeff_Hager
I’ve been thinking about this and initially I thought I would go with per-user data, but then decided that having a HOME page with buttons for each user would be a more desirable solution. Each button would then show 2 inline lists (phone call follow-ups and appointments) for that user. So basically the first solution that you have recommended.
Of course I would have to pull all the data into one sheet for this to work. I am creating a test app with a calendar view for all the appointments for the whole team (which has not been possible so far with our spreadsheet) to entice the team. Hopefully that works out.
Also, since the dataset is large, I am also thinking of trimming the flab by removing unnecessary records (unviable leads) every month or so using a script. I’ll move those to another sheet that Glide won’t use or maybe even another spreadsheet for further analysis.
I do have a couple of questions though.
1) Each user will have about 1000 leads that they must call, so the phone call relation is going to pull 1000 records or even more. I’m not sure how Glide handles this internally and whether it would hit some limit causing the app to break down. Can Glide handle a relation with more than 1000 records?
The only way I can get my team to switch completely to Glide is to retain their ability to view their data separately (like how we’re doing now with the individual sheets for each user), so it’s crucial that when I pull all data into one sheet the Glide relations never break down.
2) Every day we must manually allocate leads to each team member. So I would still need to retain access to the main leads sheet (which has everyone’s data) in Google Spreadsheets itself where someone would append a bunch of records to the bottom of the dataset. I’m guessing this would not cause any problems at all since we are only appending records and not fiddling with headers or adding new tabs, is that correct?
We will add the ability to copy a tab, but the copied tab will take its data from the same sheet.
In the app I’m currently working on I have 5 tabs that all have the exact same structure with different filter formulas to populate them from a master tab. These are the formulas in cell A1 and A2 for each of the 5 sheets. The only difference in the other 4 sheets is a change in the K= value. So the structures are exactly the same. The data is just filtered differently on the 5 sheets. Couldn’t a simple routine be run to check the values of row 1 to make sure they match the source sheet? This would allow the tab copy to be much more useful.
If Glide gave you the ability to filter at the tab level, I guess similar to screen variants if I’m understanding that correctly, then having the 5 extra sheets wouldn’t be necessary as you could create tabs that point to the same sheet.
I’m most certainly missing something here and please forgive me if that is the case, but if the only difference is in the K value, i.e. the story category, wouldn’t a reference be enough in this scenario? With a tab called Category and a category relation from the Stories tab, show the Category tab in Glide with the category relation as an inline list. Or was there a specific reason why you had chosen to build multiple tabs with filters?
Would appreciate it if you could help me with the below. I have a Users tab with a Phone call relation.
Each user will have about 1000 leads that they must call, so the phone call relation is going to pull 1000 records or even more. I’m not sure how Glide handles this internally and whether it would hit some limit causing the app to break down. Can Glide handle a relation with more than 1000 records?
Ref post in this same thread:
A relation with 1000 rows shouldn’t be a problem.
So this would accomplish the purpose of screen variant if I’m not mistaken? Would you have an estimated timeline for the release of this functionality? I’m in two minds as to whether to build another app using the same spreadsheet, only to have a different view of the final details of individual list items.
The desired end result would be 5 menu items that when each is selected it would display all the stories that were in that given category from the master stories sheet. I’m not sure how I could pull that off with just a relation to the master stories sheet. I’m not visualizing what you have in mind but open to suggestions or ideas.
Is this what you had in mind?
No, I have it that way, with the inline lists relation, but the client wants to be able to just select a category off of the new drop down menu, you know the upper left hand corner 3 bars menu. To pull that off I need to create a sheet for each category. They want a single click to open the list of each kind of story. It actually will be 2 clicks but that is the request.
I’m not sure what it is that you think would give you the equivalent of screen variants. Duplicating tabs won’t, and like I said, the new, duplicate tab will be bound to the same sheet as the original. I can’t give you a timeline on when we’ll ship tab duplication.
It seems what would actually serve your purpose is to have Glide treat all of your per-user sheets as one big sheet. Then you’d have the advantage of not requiring separate screen configurations for each user, while allowing your users to edit their individual spreadsheets, too. (We don’t have anything like that planned, I’m just thinking out loud.)
Could you please check now? I have added 2 menu options - Adventure and Young Kids. It should bring up just that filtered list from the main sheet. I’ve just created a new tab and given it the category name, then filtered by the required category. And moved to the menu section.
If I’m not mistaken this is the required functionality?
I was referring to the view at the detail level.
Ref thread discussing a similar problem where @George_B has articulated this better.
I already have all data in one main sheet, but some people would need to only view(not edit) some fields of the individual records, while others would need to edit all the fields.
Yes, that is exactly the case that Screen Variants would solve. Unfortunately I can’t give you a timeline on when we’ll ship them.