Specific email in an array

Hi everyone, I’m creating an array using emails. But, I’d like one of those emails to always be set to the email of a specific user. Example. User 1 is signed-in user. User 2 is jane@gmail.com
Any insights? TIA!

I would use an arrayformula to set the User 1 value on all rows within the sheet. Unfortunately you can’t create an array column within glide.

Just be sure to delete any empty rows and add an if to the array formula to only fill the email if another column has a value in it. Otherwise you’ll run into other issues.

1 Like

Thank you, I’m not sure how to do this. How do I set an array formula on all the rows?

Hi, if I get you and Jeff’s idea right, this would be the set up I recommend:

image

The first column is the user type, set “User 1” to any rows you want to set a fixed “jane@gmail.com”, other rows can be anything.

The second column is the signed-in email, which is automatically captured.

The third column contains the formula:

=ARRAYFORMULA(IF(A2:A<>"",IF(A2:A=“User 1","jane@gmail.com”,B2:B),""))

This fills the column if:

  • Column A is not blank
  • If column A is set to “User 1” then the final fixed email will be jane@gmail.com
  • If column A is anything other than “User 1” then it will grab the signed-in email.

Make sure you delete empty rows before doing this.

Feel free to correct me if I got any of those points wrong.

3 Likes

Ok… That makes sense. Gonna try it and let you know. Thanks

1 Like

Feel free to ask me if you need further assistance.

If you have a specific condition to set a user to type “User 1” then the first column can also be done in an ARRAYFORMULA.

The problem that Jeff mentioned, as I understand, is that when you use ARRAYFORMULA and don’t delete the empty rows first, new rows from the app will be added to (last empty row + 1).

E.g: If you have 1000 rows in the sheet but only 3 at the top have data, new rows will be added to 1001, 1002, 1003, not 4, 5, 6.

1 Like

Interesting…what’s your use case for this scenario?

It’s for users to send a message to the admin.

2 Likes

Cut and pasted the formula, but it keep giving me an ERROR.

Here’s the sheet where I set up the formula above, hope it helps.

I have given you access to edit the sheet, so if you can, paste some random data for your case and I will work on it.

1 Like

Formulas don’t paste well here. Usually it’s best to use the code blocks.
image

Usually the biggest issue is that the double quotes are converted to different double quotes. All that should need to be done is to just replace all double quotes with double quotes. Weird, but it works.

2 Likes

You’re the MVP!!! Thanks, yet, again! You will have made my app successful with all of your help!

2 Likes

Thank you so much for your help! Extremely useful!

1 Like

No worries Renee, glad to help. Just post your issues here and we will try to assist, or you can email me at ariesarsenal@gmail.com.

1 Like

Morning! I wanted to update you on this. So, the Array formula isn’t quite working. Whenever a new row is added I get this:


Only if I physically type the word “User 1” in the next row does it work. But that doesn’t work for the app. What am I doing wrong? Thanks for your help!

It says there is data in C3. What is writing to that C3 cell? You clear it and the formula will work. Just make sure there is nothing below the formula cell in that column.

Also, it would be a good practice to have your ARRAYFORMULA in the header column. As in:

={"User 2";ARRAYFORMULA(IF...)}

You will never have to worry about accidentally deleting your first data row.

That’s what’s confusing. I deleted all the empty rows, so there is no C3.

Also, not quite sure what you mean by


I’m definitely more tech savvy than the average person, but I still have a LOOOOONG way to go. Thanks!

Can you send me a copy of what you are working on so that I can have a look (also I will show you the header column trick).

1 Like

The app is writing something in column C which is giving the error

1 Like

This is a copy, so feel free to change it. Thanks!