Form Data Clearing Sheet Formatting & Not Updating Sheet

Hi everyone,

I’ve done a search in this forum and on the Goog and I can’t seem to find an answer to the issue I am experiencing. I’m hoping someone can help…

I have created an app with quite a few formulas in the Google Sheet and the calculations are working perfectly. What is happening though is the answers to the calculations are not in the correct format (as per the Google sheet).

The form input asks for a time duration ie HH:MM:SS

The input lands in the TIME GOAL column. From there, in the sheet, the duration is divided by 42.2 and the answer should then be 04:37 but instead, it is showing as a decimal, which in turn messes up the rest of the calculations. See image:

When I go into Google Sheets and update the formatting for that column back to duration, it fixes it in the glide sheet and everything works the way it should. See below after it has been changed in sheets:

Is there a way to ensure that the formatting of the Google Sheet cells are maintained when a new submission is made from a form within Glide?

I’ve also noticed that the Google sheet is not being updated with the submissions from the from. There are 9 rows in the Glide sheet, but there is only the first one on the Google Sheet (I added this one manually in the sheet while building it). See below:

(I have read on a previous post that this may have something to do with using arrays in the formula in sheets.

I’m new to Glide and I’m super excited by the possibilities and would love it if I could make this work. Any suggestions or help would be massively appreciated.

Regards,
Brad

Hola @BigBradBrown

It seems that you need to format your columns on the Glide Data Editor. To do so

  • Click on the name of the column
  • Click on the down arrow net to the name
  • Click on Edit
  • Change column type to Date and time > just time.

I believe that will show what you need.

Also, I don’t think this is a bug so I’m afraid you should change the category of you post.

Howzit @SantiagoPerez! Thanks for the reply :slight_smile:

I’ve tried that but it changes the numbers to the time of day, 04:30:00 pm and not duration as in 4 hours and 30 minutes.

Have you scrolled down to the last row and checked if they are there?

Do you need these calculations in the Gsheets? if not, try to make them in Glide using the math column. (Your columns need to be time columns for them to work)

That I have. It’s only showing the first row in Sheets and all 9 submissions in Glide.

In that case, you are right it may be a bug. Would you mind reporting it?

https://glide-help.zendesk.com/hc/en-us/requests/new

I tried to do the calculations within Glide but the math option is not supporting the calculation. This is what one of them look like in GSheets:

It’s a pity duration is not one of the time options. :cry:

I just made some tests and I see what you mean now. I still don’t this part of it is a bug, though.

@BigBradBrown

Have you checked this thread? Fun with Dates
@Jeff_Hager and @ThinhDinh have made great stuff with time. They may be able to give you better insight.

I believe that that formula can be replicated in Glide. If I understand it correctly you want 0.98 to be multiplied by whatever is in your column C if it is not empty, right?

If so, I am sure it can be done.

That is correct. There are quite a few similar formulas in that GSheet. I literally got it working today using Google and a LOT of trial and error. So much so that I’m not sure how it works, I just know that it does :rofl: Any help would be greatly appreciated.

I’ll definitely take a look, thanks.

If you’re using an arrayfornula then you should be able to use a TEXT formula to wrap around your results and format it with the syntax “H:MM:SS” (I assume that’s what you’re showing).

Scroll all the way down your Sheet. They are most likely down there. Clear all empty rows so this won’t happen again.

1 Like

awesome @ThinhDinh! I went digging and found them. Looks like it’s sorted. Thank you.

I read that about 4 times and have no idea what that means, nevermind how to do it :rofl:

I spent my morning trying to figure it out and I am pleased to report I have managed to do it. Yipppeeeeee!!!

Thank you so much for your help and suggestions. This is the first time I’ve built something like this and I am so freaking excited that it’s working.

Cheers,
Brad

2 Likes

I am so happy reading this. Keep it up, tell us if you face any other problems :wink: