Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

one thing I always do with the Arrayformula is to hide it in plain sight. By losing the very first row of data made it so hard to manage the Sheet. So I hoped this idea would help everyone enjoying more from the formula benefit.

Here is how the implementation goes
Screen Shot 2021-02-03 at 21.55.01

Here is how the full formula goes in column C, to find the MON-FRI of the specific week for example:

=ARRAYFORMULA(IF(B:B=“timer”,“week fx”,IF(B:B="","",text(DATE(YEAR(B:B),1,1)-(WEEKDAY(DATE(YEAR(B:B),1,1))-2)+(WEEKNUM(B:B)-1)*7+7,“d-”) &TEXT(DATE(YEAR(B:B),1,1)-(WEEKDAY(DATE(YEAR(B:B),1,1))-1)+(WEEKNUM(B:B)-1)*7+12,“d”&TEXT(DATE(YEAR(B:B),1,1)-(WEEKDAY(DATE(YEAR(B:B),1,1))-1)+(WEEKNUM(B:B)-1)*7+12," mmmm")))))

The structures are:

  1. Put the formula inside the header column cell
  2. Start with first IF() checking if it is the header row by checking the leftmost row if it is the Row ID (where i always put it there, or in this case my leftmost row header is ‘timer’, if so then just show the column title for this one ‘week fx’ (fx refers to “this one have the formula in it don’t accidentally delete it”).
  3. Next IF() is for checking if the most important cell in the row is empty. Means that row supposed to be emptied, so show nothing. But if not, then start showing something according to my formula.

The best part is Glide won’t be affected by the formula inside header column. So you can keep the formula even it is the sheet you may have to add the rows in all the time. The sheet also can keep the additional formulated data alongside the app’s data harmlessly.

2 Likes

Hi @ThinhDinh ! You’re definitely the expert on this, so many cool solutions to things. Robert suggested I check out this thread for my issue. I’ve read through and tried to find other tutorials online to apply to what I’m trying to do and I’m still having trouble understanding how to pull the information I need or really how to decide what information I’m even looking for.

I’m trying to populate a new column on the App: Comments sheet that will match the comment to the item it was commented on and fill in with the owner’s email so I can set up a zap to alert them of any new comments. I don’t know if I need to do one lookup for the unique row id of the item being commented on and then another for the email of the person who posted it or if I can do it all in one but I’m getting very confused.

Any help would be useful. I’m totally in the weeds over here. :frowning:

Hi @dinomeg, to allow this to scale up, I would propose the solution below:

  • In each of the sheets that you have comments component, create a rowID column.
  • Then, create a template column joining the email of the person who added/posted the item and the rowID.
  • Use that template column in the comments component as the “topic”.

Then, you will have something like a@gmail.com-rowID in the topic column in your Comments sheet.

Use an arrayformula to derive the email from the topic, something like:

=ARRAYFORMULA(IF(B2:B<>"",left(B2:B, find("-", B2LB)),""))

Use a Zap/Integromat scenario and send an email to that email you just got from the arrayformula.

1 Like

OK, I think that all makes sense. Thanks so much. I was able to figure out the lookup formula to pull info from a different sheet and that was helpful in getting the data I think I need all on the right sheet but I’ll take a look at this suggestion for next steps.

Thanks again!

1 Like

If you don’t have too many sheets that you have comments on the App then a simple arrayformula with vlookup will do, but let’s say you need to have comments on Articles, News, Videos, etc. - with too many sheets the formula will become big, hence I recommend the method above to scale up easily.

2 Likes

Ooh ok, I gotcha. In that case I think I only have a couple of sheets with comments so far. For some reason some of the new chat rows seem to clock as comments instead of going to the chat sheet but I’ll try to sort it out and experiment with your formula there. Thanks so much for your help!

1 Like

Do let me know if you need more help with this.

Ha ok, I need help. I figured out the formula (whoo!) BUT I can’t figure out how to make sure the whole column always has that formula in it without causing problem. If I preemptively add it to more cells in that column, when I new comment is submitted, it’s entered on the next completely empty line and does’t include the new formula. How can I make it so a new comment creates a new row WITH this formula intact on the same row?

Screen Shot 2021-02-05 at 12.50.49 PM

Jumping in mid conversation, so sorry if I missed something, but are you trying to put an arrayformula in each row? Arrayformulas should cover the entire column automatically, so no need to place the formula on additional rows. Just make sure to delete all empty rows, otherwise new data will be put at the bottom of the sheet. Anything with a formula in it will still be considered a filled row, so new data will look for the first non-filled row.

1 Like

Hey Jeff, yeah, I think that’s what I’m trying to do. I was using a VLOOKUP but didn’t understand how it differed from Array but it sounds like that’s what I need. I currently have this entered and it’s grabbing the right thing but isn’t applying to new rows created by new comments. I also deleted all empty rows and columns.

=arrayformula(VLOOKUP(B2, Listings!A:C, 3, FALSE))

Array formulas require the use of row ranges for a column. You are just specifying a single row item. Try this:

=arrayformula(VLOOKUP(B2:B, Listings!A:C, 3, FALSE))
3 Likes

Yes! That did it! I know I can like your post but can I give you a badge or something? :sweat_smile: I’ve been beating my head against the wall on this one! I’m finding the trouble comes when I know enough to figure out what formula or action I need but not enough to know what’s wrong about it when I’m looking at it.

Thank you so much!

3 Likes

I’ve been there. :wink: Know enough to be dangerous, or know exactly what you need or want, but not exactly how to do it.

1 Like

Hahaha dangerous is right :joy:

1 Like

Are you aware that you could start an arrayformula in the header row using this trick?

={"YourHeaderHere";ARRAYFORMULA(Sheet1!A2:Z)}

Note: Don’t forget the } at the end of the formula

4 Likes

Even better!? Don’t know this trick. Wow.

1 Like

What do I have to change in the script to make the formulas of 3 different rows in the same sheet autofill?

What type of formula are you trying to autofill?

I actually think I figured it out! thanks you!

1 Like

Hi there! Wondering if there is a way to insert through the “User input form” multiple options.

i.e Jack is the User & selects multiple fruits to buy.
Thus the data in GSheets should appear as Column A: User’s Name & Column B: List of Fruits.
Within Glide, how can Jack select "Bananas, Oranges, and more from the option set in order to have it populate into GSheets as a list (Apple, Pear, Banana, etc.)

I’ve been searching the forum but I feel I’m doing a newbie thing. :confused: