# Gsheet Formula Glide Magic...with a leaky cauldron...🤓 **URL:** \ **Category:** Ask for Help\ **Created:** [December 23, 2020, 9:21pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232 "2020-12-23T21:21:55Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\ **Post date:** [December 23, 2020, 9:21pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/1 "2020-12-23T21:21:55Z")
👋 Hey Gliders! So…I thought about a way to input formulas into Google Sheets directly from the submission of a form (I know there are other ways to do this…let’s just say I was bored. 🤓) So anyways - first I created a Text column in the Data Editor, inputted “=NOW()” formula, hit Done and then created a Template column in the Data Editor (let’s call it “Formula Template” for example) and matched the Text column to the Template column… Then I created a form and created two entries: a checkbox asking the user to confirm their submission, and a Column Template (“Formula Template”) that auto-magically inputs the “Formula Template” into the appropriate column in my Google Sheet… Yes…it worked…sent the Formula [=NOW()] into the Google Sheet ✅…HOWEVER…it doesn’t render/activate the formula…instead it sends this: '=NOW() …and the ’ is recognized as an Automatic Text entry… 🥴 …anyone out there know how to make it so “=NOW()” gets input, minus the ’ ? Thanks for any and all suggestions or help! Thought this could be a neat workaround for some use cases out there! Cheers! ---
**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\ **Post date:** [December 23, 2020, 9:39pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/2 "2020-12-23T21:39:54Z")
I think any template columns or lookups, etc will show up in the sheet as text (so they get the ’ added in front). Is there a reason you don’t want to use Arrayformulas for this if you need the formulas to show up in the sheet? Just set up an Arrayformula that will add the formula to a column whenever a new row is added. ---
**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\ **Post date:** [December 23, 2020, 9:44pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/3 "2020-12-23T21:44:35Z")
Thanks for the reply! Yeah, that’s what I currently do; was just wondering if there was actually any use(s) to this. Maybe on a per-cell-input basis? ---
**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:** [December 23, 2020, 11:19pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/4 "2020-12-23T23:19:35Z")
I kind of use the same method to populate cells when arrayformula doesn’t work (custom functions etc.), but with Scripts. Glide would always write the =NOW() as text. ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [December 23, 2020, 11:22pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/5 "2020-12-23T23:22:58Z")
I do this with scripts. I save all my formulas that cannot be arrayformulas on a separate tab and when specific columns are edited my script takes the correct formula and adds it to the row. ---
**Author:** ![ehdubya](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ehdubya/32/24087_2.png) [@ehdubya](https://community.glideapps.com/u/ehdubya)\ **Post date:** [December 23, 2020, 11:52pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/6 "2020-12-23T23:52:39Z")
I need to learn how to write (better) scripts. ---
**Author:** ![antoniedik](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/antoniedik/32/44061_2.png) [@antoniedik](https://community.glideapps.com/u/antoniedik)\ **Post date:** [August 2, 2022, 3:25pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/7 "2022-08-02T15:25:29Z")
excuse me, if so, may I know the script used so that the entered formula will always be [=Now] without the character ['] ---
**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:** [August 2, 2022, 4:11pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/8 "2022-08-02T16:11:24Z")
With a math column, you can get the current date and time without having to mess with Google sheet formulas. There is a Now function built in. Just enter a value, such as ‘x’ and then replace it with Now. ---
**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:** [August 3, 2022, 12:49am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/9 "2022-08-03T00:49:49Z")
The solution I talked about in that comment was exclusively for things that I can not do in Glide. Nowadays, I rarely use Sheets anymore. ---
**Author:** ![resepsionis\_ganteng](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/resepsionis_ganteng/32/39081_2.png) [@resepsionis\_ganteng](https://community.glideapps.com/u/resepsionis_ganteng)\ **Post date:** [August 3, 2022, 12:58am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/10 "2022-08-03T00:58:40Z")
> [@Jeff\_Hager](#): > > With a math column, you can get the current date and time without having to mess with Google sheet formulas. There is a Now function built in. Just enter a value, such as ‘x’ and then replace it with Now. I have the same problem, I want to enter the formula [=INT] but there is always ['] and the formula doesn’t work. here is the formula I want to use. > =INT(H#)&" Day “&hour(mod(H#;1))&” Hour “&Minute(mod(H#;1))&” Minute" ---
**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:** [August 3, 2022, 1:05am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/11 "2022-08-03T01:05:23Z")
What exactly are you trying to do with that formula? Do you want to format a date/time object? ---
**Author:** ![resepsionis\_ganteng](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/resepsionis_ganteng/32/39081_2.png) [@resepsionis\_ganteng](https://community.glideapps.com/u/resepsionis_ganteng)\ **Post date:** [August 3, 2022, 1:09am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/12 "2022-08-03T01:09:03Z")
Convert Time Duration to Day, Hour, Minute in Google Sheets. ---
**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:** [August 3, 2022, 11:49pm UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/13 "2022-08-03T23:49:17Z")
Shouldn’t you use text instead? > **[TEXT - Google Docs Editors Help](https://support.google.com/docs/answer/3094139?hl=en)** > > Converts a number into text according to a specified format. Examples Make a copy And make it an arrayformula? Should work with yoru original formula anyway but I don’t know why you use “H#”? It implies you are not using an arrayformula right? > [@Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts](https://community.glideapps.com/t/tutorial-arrayformula-in-google-sheets-good-practices-how-to-overcome-arrayformula-restrictions-with-scripts/9727): > > Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job. > > > **1/ Definition** > > > > Arrayformula is defined as “a range, mathematical expression using one cell range … ---
**Author:** ![resepsionis\_ganteng](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/resepsionis_ganteng/32/39081_2.png) [@resepsionis\_ganteng](https://community.glideapps.com/u/resepsionis_ganteng)\ **Post date:** [August 4, 2022, 12:37am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/14 "2022-08-04T00:37:34Z")
H is a column in google sheet that contains duration time data. and # will be replaced with the row number in the google sheet. I don’t use arrayformulas and prefer to use manual formulas from the Glide template. because if you use an array formula, glide will enter data at the end of the row or outside the range of the formula array. ---
**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:** [August 4, 2022, 12:38am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/15 "2022-08-04T00:38:11Z")
> [@resepsionis\_ganteng](#): > > because if you use an array formula, glide will enter data at the end of the row or outside the range of the formula array. I’m not sure what you mean here. You can just erase the empty rows. ---
**Author:** ![resepsionis\_ganteng](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/resepsionis_ganteng/32/39081_2.png) [@resepsionis\_ganteng](https://community.glideapps.com/u/resepsionis_ganteng)\ **Post date:** [August 4, 2022, 1:16am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/16 "2022-08-04T01:16:24Z")
![array](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/b/a/bac0b61513efa6cc344d3749d2b0e2b0ebc0618e.jpeg) If I use arrayformula with range H2:H20 then when there is new data from Glide, the data will be inputted in row 21. ---
**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:** [August 4, 2022, 1:17am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/17 "2022-08-04T01:17:35Z")
Arrayformulas shouldn’t use a defined range. Arrayformulas will apply across all rows. Use H2:H instead of H2:H20. Then delete all empty rows. Any new row will automatically have the arrayformula applied and you won’t be limited by a predefined range. ---
**Author:** ![resepsionis\_ganteng](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/resepsionis_ganteng/32/39081_2.png) [@resepsionis\_ganteng](https://community.glideapps.com/u/resepsionis_ganteng)\ **Post date:** [August 4, 2022, 1:25am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/18 "2022-08-04T01:25:17Z")
![array2](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/0/9/0907fb0058cf6dd3a1ecc86fc0d8bafe6280ef69.jpeg) data from glide is entered in row 1001. I want to run the formula automatically without manually deleting empty rows. that’s why I use manual formulas when saving data from GlideApp. But in front of = there is always a ['] which causes the formula to be plain text. ![array2](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/6/a68440a4679560429c219a8dfb2fff050fcfaf6b.jpeg) I use the column with the template type from Glide to apply the Google sheet formula then I use [set column value] to the Duration column. ![array2](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/4/44b358f90a8190d90f3e94a0f6ce97510d57abe5.jpeg) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [August 4, 2022, 1:36am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/19 "2022-08-04T01:36:18Z")
> [@resepsionis\_ganteng](#): > > data from glide is entered in row 1001. > I want to run the formula automatically without manually deleting empty rows. The way to avoid that is to use an IF condition in the arrayformula so that it only applies to non-empty rows. But even better is to not do this in a Google Sheet at all. Just use Glide computed columns and all these issues go away. ---
**Author:** ![resepsionis\_ganteng](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/resepsionis_ganteng/32/39081_2.png) [@resepsionis\_ganteng](https://community.glideapps.com/u/resepsionis_ganteng)\ **Post date:** [August 4, 2022, 1:39am UTC](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232/20 "2022-08-04T01:39:16Z")
This is the formula I use. > ={“Duration”;ARRAYFORMULA(IF($H$2:$H\<\>“”;int($H$2:$H)&" Hari “&hour(mod($H$2:$H;1))&” Jam “&Minute(mod($H$2:$H;1))&” Menit";“”))} [Next page](https://community.glideapps.com/t/gsheet-formula-glide-magic-with-a-leaky-cauldron/20232.md?page=2)