Is there any way to copy layouts across tabs? I have a spreadsheet where each tab is allotted to a user (per user display via email id is not feasible for my specific requirement), and they all have the exact same columns. All tabs also need the exact same Glide layout. New tabs will frequently need to be created as new people join, so it’s difficult for me to set up a new layout as each tab is added.
What I am looking for is this - I set up the layout in the first tab, and then copy that to the other tabs. Like how we would duplicate a properly formatted tab.
Is it possible to get all of your data into one sheet? Since the layouts would be the same, if all the data could be joined into one sheet, then you could create a names sheet with a list of names in one column and second column of names to use as a reference column to the main data sheet. Use the reference to join the the two sheets. You would only need one tab that lists all of the names. Clicking on a name would bring you into the details for each name where you can add an inline list using the reference you created. That way you don’t have to keep changing the app whenever you add new users. Maybe that’s not what you want, but just an idea.
Try creating a separate tab, listing the columns headers used on each current user tab. For example column A would consist of all the column headers on the current user tabs. On the current user tab, use the transpose function in the first cell of the first row. i.e. =TRANSPOSE(‘Milestone Category’!A1:A128) ‘Milestone Category’ would be replaced with your tab name and the range would consist of the start and end rows on the column headers. This technique worked for me and gave me the ability to update the column headers in one place.
I think that the @mmgt2005 soloution will work fine for duplicating the header row. If more rows (i.e. with formulas) should be duplicated I beleive that the only solution is to use first tab as Template and run the duplication by a script.
You could use my method, but instead, set up a reference column for each user to each respective sheet. This at least prevents you from having several tabs in the app. Unfortunately, I don’t think this will help with your layout problem because layouts are tied to each individual sheet.
What is the reason you can’t have all the data for users on one sheet. If your app is set to use email/pin or whitelist logins, then you can set the per user setting then the only data visible to the user is their own, not everyone else’s.
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?
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.
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.
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?
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?