A copy of the spreadsheets and current workflows would yield a better answer... but here are a couple approaches.
Things to consider:
- How exactly do users input data? Do they do basic data entry one cell at a time? Do they input one row of data (one cell at a time) then finish/exit the sheet? Do they do multiple rows all in one go (for ex, there is a raw data table from somewhere, that they copy/paste, then have to reformat and add formulas)?
- Do the number of users vary significantly? For ex, are there only 10 active users, and the number is fairly constant? Or are there anywhere from 1 to 1000 active users at any time?
- How exactly is the desired sheet being used? When does the data with the formatting and formulas need to be visualized? What exact purpose do the formulas and formatting serve?
- Do they users have a large variability in skill with google sheets (do the final sheet need to be very simple with a lot of things locked down)? Note that with a large number of users, the skill distribution naturally increases, regardless of the average skill level, thus requiring a simpler, or more locked down, solution
- What is the overall volume of data? If there are 1000+ rows being done weekly/daily, these overall approaches won't be ideal.
- How frequently/regularly is data added? Once per day per user? 10 times/day? Randomly as new data comes in?
Based on the above, there could be a lot of different potential solutions that vary in complexity.
The main idea for these approaches, is there should be
- a way for users to enter/copy/paste in their data easily
- then from some kind of template, the new table is generated with the desired formatting and formulas
- then, somehow the desired data is aggregated to the "master" sheet
Also use row or column grouping to make navigation more simple.
Here are a couple approaches, in no particular order, that vary in complexity:
- Simple approach with a single sheet - users add data one cell at a time
- prepopulate all the formulas (drag them down), then lock those columns. Highlight the "input" columns. Allow users to input their data in the input columns
- Something I don't like about this, is that users will have to navigate through extra columns, thus wasting time (though it depends when they need to see the final data, and also if they are only doing 1 row per day, an extra 5 minutes wasted might not be a big deal). But on a single sheet, there can be the "input" columns reserved for the left half. Then the right half can be locked down, and this can be the "visual" component, where the users will be actually locking at the data. Note that if they want to see the raw/input data as well, these data can be referenced in the "visual half". Of course if there is a ton of data, this isn't very scalable, however from the question, it doesn't seem like scalability is much of a concern.
- Simple approach with multiple sheets (usually 2) - users copy/paste data (can be fixed or variable) from one source this sheet, then need to reformat/add formulas
- Set up 'Sheet1' where the data can be copied (for the other source) and pasted in directly. So at a high level, the top half of 'Sheet1' will look like the data you want (with formatting/formulas), the bottom half is the raw data that gets pasted in.
- On 'Sheet1', create the desired table with formulas/formatting. For example, let's assume 3 cases, users paste: 1 row, 10 rows, n rows. Starting at row 1* 2, 10* 2, or n* 2, let users paste into here. Set up the row 1, row 1:10, row 1:n to populate the desired table (lock this range). By set up, I mean create all the formulas/formatting you want, along with all the original data.
- Now, on 'Sheet2', users can copy/paste that table to the bottom of the range.
- Note, a script could also be created to automatically append this new data from 'Sheet1' to 'Sheet2'.
- Slightly more complex approach, but this would work for 10 users (not 1000), that need to work simultaneously. This approach will limit the possibility of one person messing up the whole sheet
- Create a sheet for each user, and lock all the other sheets. So there is 1 "master" sheet, with 1 additional sheet per user. Each user only has access to their sheet
- Users add data to their sheet, the correct table is generated, this is then pasted or appended with a script to the master sheet.
- Note the script could be triggered in many ways, for ex. on edit, button click, click run, etc.
- Very dumbed down, and locked approach, works at very small scales.
- Users submit info into a Google Form, that pushes over to a Google Sheet
- That Google sheet will reformat/add formulas, or can push to another sheet that will do the formatting/formulas
- Another simple approach
- 'Sheet1' for people to input/paste in their data. Everyone just scrolls to the bottom and enters in their data
- 'Sheet2' is locked, prepopulated with formulas and formatting, and just grabs all of the data from 'Sheet1'
