What is the right approach to build individual user database?

Ask for Help from Community
1

Hi, My app collects information about my users. all the information submits to one sheet, I call it “RAW USER DATA”.(about 50 columns).

I would like to filter the information per user name and then calculates the personal information and personal traffic.

for example, I would like to know the first date of activity of the user, the second date of activity, the last date of activity, then calculates the distance between the dates to see if it ok or not ok.
another example, I would like to calculate the subjective answers that the user is sending and compare it to their previews answers.

I was thinking > each time I add a new user to “USERS” sheet, automatically google sheets creates new sheet(by script) with the user profile and takes all the information from the user raw data based on filter (by personal name) -
then I can use my functions to calculate the information.

The conclusions from the user personal sheet will effect the “USERS” sheet like massage showing to each individual user , etc…

But that means that I will have 50+ tabs and also, this will “eat” my rows from glide.

Am I right? Is there more elegant solution?
Thank you.

2

This doesn’t feel like an easy nor necessary approach.

In your users table, you have basic columns storing your users’ raw data. Have you considered adding computed columns to the same users tables for the calculations?

For example, per user (=per row), if you have a start DateTime and an end DateTime in two basic columns, you could create a computed column to determine the duration per user (=per row).

1 Like
3

Thank you!
If I am understand you right,

You mean writing the functions in “USERS” sheet (in different columns then those Glide reads/write from), Based on the information being sent in user raw data sheet.
Right?

4

you can copy calculation results to the glide User sheet, no need for having supporting sheets in Glide…
But… how complex these calculations are? maybe you can do everything in Glide columns?..
also why separate sheets per user??? that will complicate your data.

5

I made an example sheet that explain my situation, I would to share it if that’s ok:

Users tab is where all the registered users are stored.
User RAW submissions is where Glide is writing all the submission.

Some of my required calculations (as an example) are in Users - > D1:I1

Is it possible ?
Thank you.

6

i don’t see any formulas in d1:i1… what calculation do you need??? cells are empty!

7

Right, because I don’t know the formulas.
Right now I am using “support sheet” because I have different sheet per user with duplicated formulas (using old app, not glide, yet).

I don’t know how to use the formulas in the current state.

You can see the needa, like: last submission, or 2nd last submission and so on.

8

ok, I’m back… i finished your spreadsheet

9

Oh wow , Thank you!
I copied the formulas to my original sheet and it did not work.
Then I saw that you created new column called " FULL NAMESubmission date", Could you please explain what is the purpose of it? How can I use is in my original sheet?

Thank you!

10

this is to create the lookup reference (keep that column all the way left in your sheet), in your real app change the name column to the email column, so it will work, even if there are users with the same name.