Gsheet Formula Glide Magic...with a leaky cauldron...šŸ¤“

:wave: 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. :nerd_face:)

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 :white_check_mark:ā€¦HOWEVERā€¦it doesnā€™t render/activate the formulaā€¦instead it sends this: '=NOW() ā€¦and the ā€™ is recognized as an Automatic Text entryā€¦ :woozy_face:

ā€¦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!

1 Like

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.

1 Like

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?

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.

1 Like

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.

2 Likes

I need to learn how to write (better) scripts.

excuse me,
if so, may I know the script used so that the entered formula will always be [=Now] without the character [']

1 Like

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.

1 Like

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.

1 Like

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"

What exactly are you trying to do with that formula? Do you want to format a date/time object?

Convert Time Duration to Day, Hour, Minute in Google Sheets.

Shouldnā€™t you use text instead?

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?

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.

Iā€™m not sure what you mean here. You can just erase the empty rows.

If I use arrayformula with range H2:H20 then when there is new data from Glide, the data will be inputted in row 21.

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.

1 Like

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.

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.

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.

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";ā€œā€))}