# Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts **URL:** \ **Category:** Ask for Help\ **Created:** [May 25, 2020, 1:10pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727 "2020-05-25T13:10:24Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [June 20, 2020, 4:47pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/21 "2020-06-20T16:47:38Z")
Each part of that determined whether the statement is true or false and returns a boolean value. If it’s true then return 1, false returns 0, so you can wrap it inside an arrayformula to calculate a sum. ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 10, 2020, 10:01pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/22 "2020-07-10T22:01:49Z")
Maybe a stupid question. But can I do an array formula for Today() function? Need to use this for my calculation ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [July 10, 2020, 10:07pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/23 "2020-07-10T22:07:32Z")
Yes you can, what are you trying to calculate? ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 10, 2020, 10:22pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/24 "2020-07-10T22:22:25Z")
I’m trying to make a formula that calculates: What age (in week) the user (puppy) is right now. I automatically get the date they sign-up and during onboarding they have to enter the age (in week) at that date. So the I want to do a formula that calculates how many weeks to add to get the CURRENT age (hope this makes sense). E.g. age at onboarding (in weeks) + weeks passed since onboarding = current age (in weeks) ---
**Author:** ![Erni\_D](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/erni_d/32/9023_2.png) [@Erni\_D](https://community.glideapps.com/u/Erni_D)\ **Post date:** [July 10, 2020, 11:29pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/25 "2020-07-10T23:29:44Z")
Hi Hebba . I have this formula working in a project, it gets the age by years, months and days. Maybe is something like you mention (gettin de age) think you can change it for your needs. ``` =datedif((H2:H),today(), "Y")&" Years "&datedif((H2:H),today(), "YM")&" months & "& datedif((H2:H),today(), "MD")&" days" ``` ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [July 10, 2020, 11:34pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/26 "2020-07-10T23:34:13Z")
Hi Hedda, I make an example [here](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=932333313). The formula is in cell C1. ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 11, 2020, 7:43pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/27 "2020-07-11T19:43:35Z")
Wow, that’s so kind! ---
**Author:** ![Axta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/axta/32/10479_2.png) [@Axta](https://community.glideapps.com/u/Axta)\ **Post date:** [July 12, 2020, 12:10pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/28 "2020-07-12T12:10:14Z")
Thank you @ThinhDinh for sharing your knowledge. 1. In my situation the array formula is not pasting the values down (see picture). What should I change? 2. Since this data (number of redemptions remaining) is a changing number (you start with 5 redemptions and it subtracts 1 every time a user presses on “redeem” button), how can I paste all data down AND allowing the data to change when a user takes an action in the app? ![Screenshot 2020-07-12 at 14.05.17](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/7/70716f6fda37c7abba1c374fd232ade512132438.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [July 12, 2020, 10:31pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/29 "2020-07-12T22:31:18Z")
With your point 2, you can’t overwrite an arrayformula, it will lead to error for the whole column. You should do that in the editor with math and rollup. ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 21, 2020, 11:32am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/31 "2020-07-21T11:32:51Z")
Hi again, it works very well. the only problem is that it takes quite a while before this info updates in the app. E.g. I ask all new users to fill in the age and then it should automatically show certain content. However since I have this array calculation it takes a while before any content starts showing. Is there a workaround here? Thanks 🙏 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [July 21, 2020, 11:52am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/32 "2020-07-21T11:52:53Z")
As of now we don’t have a way to calculate with dates in the Editor, so I have to bring them back to the Sheets. If I have a workaround I’ll let you know. ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 21, 2020, 7:18pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/33 "2020-07-21T19:18:19Z")
Cool, thanks! ---
**Author:** ![Robin\_Andersson](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robin_andersson/32/5397_2.png) [@Robin\_Andersson](https://community.glideapps.com/u/Robin_Andersson)\ **Post date:** [July 26, 2020, 9:18pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/34 "2020-07-26T21:18:06Z")
Evening. This opens up possibilities for glide users for sure! Thank you. I have a little problem: I’m trying to make an arrayformula that not only saves rows neatly like you help with in this tutorial, but one that also includes a calculation based on what number a cell in a row has. I want basically to make the value in column E to be multiplied by 1.05 if the number in column F is 2, multiplied by 1.08 if the number in column F is 3, -=- 1.11 if the number in column F is 4, etc. Up to 15. And put the result in the D column. (Date, email and ID is occupying row ABC fyi). I’ve tried and searched google around a quite while now and I find no one talking about this kind of combination. I’m guessing this would be possible for someone more experienced than me, to combine this kind of Arrayformula that won’t cut all your 500 glide-rows in a second, with an advanced nested if/ifs or something. Thank you for your time ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [July 26, 2020, 9:25pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/35 "2020-07-26T21:25:52Z")
This should be pretty easy with nested ifs in an array formula. You can also do this within Glide by creating an if then column to get your multiplicative value, then a math column to do the math…unless you need the result within the sheet for some reason. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [July 27, 2020, 3:06am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/36 "2020-07-27T03:06:18Z")
Hi Robin, I created a sheet for your case here. > **[ThinhDinh's Glide Lab](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=2096876261)** > > Cloudinary lab > > \ **Author:** ![Robin\_Andersson](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robin_andersson/32/5397_2.png) [@Robin\_Andersson](https://community.glideapps.com/u/Robin_Andersson)\ **Post date:** [July 27, 2020, 1:32pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/37 "2020-07-27T13:32:29Z") Thanks for that Jeff, I ended up using Glide’s built in options as you pointed out was possible, so I could bypass using the sheet skills offered by @ThinhDinh, which I anyway learned a bunch from for the future. Thanks guys much appreciated ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [July 27, 2020, 3:44pm UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/38 "2020-07-27T15:44:49Z")
Yeah, he’s definitely a good resource to have around here. 💪 ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 29, 2020, 8:52am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/39 "2020-07-29T08:52:24Z")
Hi again, this have been working great however getting an error now if the the user signed up today. Is there a way to work around this? ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [July 29, 2020, 8:54am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/40 "2020-07-29T08:54:12Z")
Can you copy the formula out here for me? I cleared that sheet like a week ago and just want to make sure I will work on what I provided you. Thank you. ---
**Author:** ![HeddaBO](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/heddabo/32/8531_2.png) [@HeddaBO](https://community.glideapps.com/u/HeddaBO)\ **Post date:** [July 29, 2020, 9:16am UTC](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727/41 "2020-07-29T09:16:25Z")
Of course, thank you! 🙏 =ArrayFormula({“Age now (weeks)”;ARRAYFORMULA(OM(G2:G\<\>"",G2:G+ROUND(DATEDIF(H2:H,TODAY(),“D”)/7,0),""))}) [Previous page](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727.md?page=1) [Next page](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727.md?page=3)