How to add a row to a related sheet when a new row is created?

I think there are a couple of ways to approach this so I wanted to check in with the experts here first.

I have a social app where a user can add a group. The Groups sheet contains important info about the group and a separate Members sheet contains a list of people, the groups they belong to, if they are an admin of said group, etc. (Groups can have multiple admins).

When a new group is created, I want to also create a new row in the MEMBERS database for the creator / admin.

What do you think would be the best way to pull this off? The group will get “orphaned” if it does not have a member, so the row in Members needs to be created immediately.

How about creating a group with a column for “admin email”, which takes the signed-in user who created the group by default.

Then when the group is created, show a form button to the admin to create new rows for members?

I like the suggestion from a UX perspective because after creating a group it makese sense to have a flow where you would share your group and invite members.

I am not sure I completely understand the implementation of your idea. But I should first point out that the groups are location based and are currently added from a map based browse UI:

So, with the current design I don’t think that I would be able to show the group creator a second form after creating a new group since I am using the add functionality from the map component:

I am open to other ideas but if I can’t the user know that they can add their own group in this context then the feature may be tough to discover.

Can’t you add the signed-in user value to a column then have an If Then Else column to tell whether the signed-in user is an admin of the group?

If signed-in user’s email is group admin email then “Your group”, else “Others”, for example.

Then using an in-app filter, I imagine you can let users filter groups they have added and start adding members from there?

1 Like

I have a similar issue. My app has a list of contacts and a list of companies on two separate sheets. The use-case is when a user adds a new contact. The new contact form has select ‘company’ choice or a new ‘company’ text field. If the user enters a new company and clicks submit, it will save the new contact, but I would also like it to append the new company to the ‘company’ list.

You could use a =Unique("company column in contact sheet") formula in your companies sheet.

I think you could add another column and call it “Group creator/admin” and capture the email address of the person who creates this form in that column. Then you could have a relation from this sheet to your user profiles sheet to link admin.
You user profiles sheet always has the emails of everybody who has come in on the app anyways, you could create a multiple relation in that sheet and relate it with the new group admin column.

I hope this solves your doubts?

1 Like

@Manan_Mehta Unique can be a little scary if you also have related columns in the same sheet as the unique formula. If a contact would be deleted or the sort order changed, this could potentially bring in the unique values in a different order and break the link between the unique column and any other company information in the company sheet. I think this would be better off with a script or Zapier or Integromat integration to only add company names that are not already in the sheet. @Jeremy_P is there any other additional pieces of information in your company sheet, or is it just a list of companies? You should be able to also build your company choice component off of the contact sheet (I think it only shows unique values), but if you need it to to be written to the company sheet to build out additional company information (address, phone, etc.), Then I would recommend the the script/zapier/integromat option.

1 Like

I suppose I could introduce the concept of an additional role. The creator could have the role of “group owner” and the owner or other admins could make someone else a “group admin”. I would have to add an extra bit of login whenever I need to make certain functions permission based but that’s not a big deal.

1 Like

Oooh never heard of that service. Looks like it could be handy for things like sending a new user a welcome email, sending a new group owner a message with some tips, etc.

1 Like

I don’t have any personal experience, but from what I’ve read, quite a few people use it and seem to like it better than Zapier. I’ve read it’s easier to use too.

2 Likes

Both Integromat and Zapier can work well here. The right action to look for is create a new row when a new row is created in spreadsheet.

For example.

1 Like

Yes I agree on that. Usually when I use this, I never allow deleting in my app and that could be a limitation.
As another robust solution, I use Apps Scripts to append rows based on conditions. That works like a charm but can get pretty complicated to understand.

1 Like

Hmmm. Looks like you get throttled to a 15 minute interval time for Integromat and Zapier until you get up to their mid-range plans.

@Manan_Mehta I could not find pricing for Apps Scripts - is it free? How fast is the update interval? Are you open to sharing any of your code? I understand if not. :slight_smile:

function Rcomments() {
    var ss = SpreadsheetApp.getActive().getSheetByName("Request Comments");
    var values = ss.getRange("A2:AQ").getValues();
  //notification 1 on Pluhg Creation
   for (var i in values) {
     if (values[i][2]) {
       
       var row = i
       var time = new Date();
       var ID = values[i][1];
       
       var Receiver = values[i][6];
       var message = values[i][9];//Message
       
       
       if (values[i][6] != '' && values[i][8] == '' ) {
       Utilities.sleep(1000);
       var row = +i + 2
       ss.getRange(row, 9).setValue("Sent");
           var ss = SpreadsheetApp.getActiveSpreadsheet();
           var sheet = ss.getSheetByName('Notifications')
           sheet.appendRow([time,ID,Receiver,"Comment",message]);          
       SpreadsheetApp.flush();
       }
     }
   }
}

This is a script I am using to create new notifications into a new sheet when there’s a new row in another sheet. It may be complicated to understand and I might have to do a complete post to walk through this.

Apps Scripts are free. Google has daily quotas on the number of triggers and scripts you can run. I have triggered to run this script every minute. That’s the smallest time interval Google offers.

You can modify this script in your case exactly the way you need it.

2 Likes

Oh sweet, thanks for sharing that. Given the complexity of that code snippet (for me at least), I will likely hold off on trying to test this for a little bit. I don’t want to implement code that I don’t fully understand and I just don’t have the time to spend on this right now.

The price is right though :wink:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#appendrowrowcontents

Can check this thread out if it’s helpful.

1 Like

Follow-up:

After a private beta, I learned that the Integromat is not going to work for me. A couple of these “scenarios” running every minute creates so many operations that I would need to upgrade from the $29 / mo plan to $99 / mo.

I throttled the scripts back to run every 15 minutes and my users ended up joining their newly created groups as members because they were not added as admins fast enough.

Before giving up on this method, I guess I should consider migrating to apps scripts and roll it out to more users.

1 Like

Why are you using Integromat scheduling? Why not just look for new rows?