Hello!
So the scenario is, that users of my app are able to sign up for an event, created by other useres, by submitting a form, where their email is being added to a Participant array. (Participant 1, Participant 2 and so on)
I do now use a counter in google Sheets (=ZÄHLENWENN(M2:AA2;“@”) (it’s the german version) that counts all the fields that do have an email inside.
Users who see the event can see the progress of how many participants have signed up.
I use the participant counter so the app knows which participant colum to use for the next perticipant.
The Problem here is, that i haven’t figured out a way yet, how the macro for the COUNT IF will automatically be in a new row, in case a user creates a new event.
What I have tried so far: Making an invisible text entry when the Event -Creating Form is submitted, but this hasn’t worked since the entry has a " ’ " in front, so google sheets wont regognize the macro. Is there any other opportunity rather than adding the macro individually every time?
What also wont work, is to pre genereate the makro for empty rows, because glide App will recognize it and create the next row underneath…
Is there any reason why you don’t just write new rows to an event participants sheet then use a relation column to link the event sheet to the participants sheet? A simple rollup column on that relation would give you the count of participants and you wouldn’t have to mess with any sheet formulas. Also, you wouldn’t be limited by the number of participant columns you have and you wouldn’t have to create a component for each column. With your setup, I could potentially see problems if two people sign up for the same event at the same time. Especially with the delay introduced by using sheet formulas.
If you would rather stick with your current setup, I would use a glide IF THEN column to determine the count. It’s much faster than a google sheet formula, but you still have the slight issue of two people signing up at the same time and potentially overwriting the same column.
To explain your original problem, you can’t create google sheet formulas in glide. It’s only recognized as text. Not a formula.
Well the reason would be, that i am not 100% secure about the all the functions, because i have been using glide apps for a couple of days and am still leraning. But thats why i am very thankful for your answer. I will try it out right now and report the results.
Just to clarify:
As I understand, the participants sheet is supposed to be another sheet, different to the original “User” sheet? So every time a user creates an event, glide will create a new row in the “Event-sheet” and a new row in the Participants sheet?
Yes, the users sheet would just be a list of all users, such as user profiles. A user can use the add or form functions to create a new event in the Events sheet. There you can add a Row ID column, which will automatically assign a random character ID to each event. When viewing the event’s details, you can add a form button to add participants, which you will set up to write new rows to the event participants sheet. In that sheet, all you need is a column to hold the event ID and the user’s email. You could set this up with an Add Row action, which can automatically pull in the event ID and user email without any additional input. If you need more details from the user, I would use a Open Form action, auto fill the important details, and add any additional entry components for additional info needed from the user. Then all you need is a relation column in the event sheet to link the Row ID to the event ID in the participants sheet. With that you can create your rollup count column and also show an inline list of all participants in the event details.
But wait, don’t i create a new row for every participant then?
Because that would mean i will have to create a new participant sheet for every event… i guess that is not what i want… or am I missing something here?
You don’t need a new sheet for each event. What I described is only 3 sheets (Users, Events, Participants). The Participants sheet will list all participants in all events. The key here is the Event ID column to differentiate which event each participant belongs to. The relation links the Event sheet to the event in the Participant sheet. It would look something like this.
Oh ok, so it will not matter in which order the participants will be listed in this sheet in order to create a proper roll up (count) for each event ID?
what i still don’t get, is in case a user is creating a new row in the event sheet, how, or what value/ colum do i use to relate to the participant sheet?
and also, do the rows of participants for a certain event delete themselves after the event was deleted ?
And also, is there a function that will create a random event ID, because as i reckon the Event ID is definately not the ROW ID and therefore has to be created somehow, because there will be the possibility, that the given name of the event might be the same as other events, taking place another time in another location.
No, order does not matter. The relation only looks for a matching ID.
The row id column in the event sheet will be automatically filled when a new event is created. The row id is what you pass to the participants sheet to fill the event ID column.
No, deleting an event will not delete any related rows in the participants sheet. You would have to figure out some google scripting to handle that or clear it out manually. Otherwise, the rows would still be there, but not visible because there would no longer be an event to relate to them.
ok so the clue is, that the column in the participants sheet named “event ID” will not be marked as row ID, instead it will just be a text form… is this corrrect?
OK Thank you again! it worked out good so far! There is only one more thing to perfection: I can’t accomplish that the User who is organizing the event, will automatically be added to the participants sheet with the new row ID (event ID). I have managed, that the email will appear in the list, but i think because the row ID of the event isn’t existing at that point, the user who’s organizing the event will have to manually fill out the participation form too, in order to be listed in the participant list with the matching Event ID… The goal is that the Progress Bar will show 100% in case the event reached 10 Participants including the organizing user…
or is there no way to do this, other than setting the max automatically at one less…?
I assume you are using an add row action on form submit to add the organizer to the Participants sheet? If so, I think I understand your problem. Keep the Row ID column for the moment until we know this works, but in the Events sheet, create another basic column and name it Unique ID or Event ID in the Events sheet. In the form, add a Unique ID Special Value component and set it to fill the new column. This is similar to Row ID, as it’s a unique value, but it’s not automatic and is instead set by the special value component. Now change the Add Row action to write to the Event ID column in the Participants sheet using the new Unique ID column we created. It should get set sooner in the Events sheet, so you won’t have an empty value when it writes to the Participants sheet.
yes that is the function i am using. So basically instead of using the row ID I use the new type of ID, also for relations and round up and so on as well as for the regular participants?
And there is another Problem that occured, maybe I didn’t create the proper relation, but I want to make an inline List of all participants with information from their user profiles such as the Username and picture. Do I have to copy these Information into the Participant List through submitting the form (that creates a new row in Participant list) or is there a way to display the Profiles of Users who are in the Participant list and are assigned to a certain Event by using relations?
Correct. This would replace the Row ID for everything as long as it’s working for you.
To get an inline list of all the users with their info from the user profile sheet, what you would do is take the existing relation from Events to Participants, then create a Lookup column to get an array of all participant emails from that relation. Then using that Lookup column, you can create a second relation to link the lookup array of emails to the email column in the Users sheet. You will then be able to use the second relation as your source for the inline list.
I do have another problem now: i deleted all rows (except for the header) in the participants sheet and now there are still two data rows in the glide data sheet (in participants) i cant get rid of them…
Try the reload button in the editor. If that doesn’t work, try reloading the browser window. If still no luck, then double check that you don’t have extra rows of data somewhere in the middle or bottom of the sheet.
Thank you again! But for some reason it will only display the User, who is logged in… not sure why, because for this example i do have 3 Names in the array, coming from the User Sheet. everything else works good so far. Also, could I use the same function to hide the “participation-Form” for Uers who are already in the list?
Are you using Row Owners on your user sheet? Row Owners is a security feature that prevents users from viewing data that does not belong to them.
You could create a template column that joins the Event ID and the signed in user’s email from the user profile sheet in the Events sheet. Create a similar template joining the Event ID and Email column in the participants sheet. Then create a relation in the the Events sheet that joins the two templates in both sheets. Set the visibility on the form button to only show if the relation is empty.