Google Sheet Entries keep going to bottom of sheet in my glide app

Report a Bug
1

I have a calculation that is on my google sheet. I set up this calculation on all the cells. But when I enter the numbers to be calculated, it goes to bottom of the google sheet in the app. Where there’s no calculation. How do I fix this? I need it to go start at the top and to be able to do the calculation every time the information is entered.

Here is a screenshot of everything

Screen Shot 2022-10-15 at 6.23.50 PM
Screen Shot 2022-10-15 at 6.23.50 PM731×187 10.8 KB

Screen Shot 2022-10-15 at 6.25.40 PM
Screen Shot 2022-10-15 at 6.25.40 PM1160×87 4.96 KB

2

I would recommend doing the calculation in glide if you can.

Rows with formulas are populated rows, so glide will fill the first empty row. Use an Arrayformula and delete all empty rows.

1 Like
3

Thank you. I use an array formula and it’s fixed.

4

You really should consider the first thing that Jeff said…

1 Like
5

I couldn’t figure out how to write the formula in glide, that’s why I went the google sheet. Is thing going to cause issues?

6

It depends.

The thing to understand about having formulas in your Google Sheet is that there will always be a delay before the results of the formulas are available in your App. This is because when data is entered via the App, it needs to sync from the App to Glide servers, from Glide servers to your Google Sheet, then the calculation happens, then the data has to sync back to Glide servers, and finally back to the App. This process can take anywhere from several seconds to several minutes.

If however, you do calculations in Glide with computed columns, then they compute inside the App on the user device, and results are available instantly.

Your formula looks pretty simple, I suspect you could replace it with two Glide columns:

  • A Math column to do the modulus
  • An if-then-else column that returns the result of the math column if Sum is greater than zero

heh… “Sum” suggests another Google Sheet formula, which would probably be better done in Glide :wink:

There’s nothing fundamentally wrong with Google Sheets formulas - and you can continue to use them if you want, and your App will work fine (just a bit slower in some cases than it otherwise could be), but the more you learn about Glide the more you’ll come to realise that they (Google Sheet formulas) are very rarely necessary.

2 Likes
7

Yes I see the delay, so you’re correct.

These are the two formulas

=ARRAYFORMULA(IF(LEN(A2:A)=0,A2:A+B2:B+C2:C))
=ARRAYFORMULA(IF(LEN(D2:D)=0,IF(D2>0,1+MOD(D2:D-1,9),0)))

I don’t know how to convert them in glide. I’m new to it so I’m having a hard time with it.

8

Okay, so with computed columns in Glide, the result of the computation is automatically applied to all rows in your table. So you don’t need to bother with arrayformulas, or similar.

So let’s start by removing that, to make them a little easier to work with:

=IF(LEN(A)=0,A+B+C)
=IF(LEN(D)=0,IF(D>0,1+MOD(D-1,9),0))

Next it helps to try and describe the formula in plain english. Taking the first one, which I guess you have in column D, I would read that as:

  • If the Month value is not empty, then add each of the Month, Day & Year values together

Assuming that’s correct, then you would do that in Glide as follows:

  • create a Math column using the formula Month + Day + Year
    – Glide will prompt you to choose replacement values for each of Month/Day/Year. You should choose the appropriate column for each
  • Next create an if-then-else column:
    – If Day is empty, then 0
    – Else Math column
  • Your if-the-else column will contain the same value as your existing Sum column in your Google Sheet

For the second one, I read it as:

  • If the Day value is greater than zero, take the Mod,9 of the Day value -1
    Again, assuming that’s correct, then we’d have two similar columns as per the first example
  • A Math column: Mod(Day-1,9)
  • An if-then-else column:
    – If Day is greater than 0, then math column
    – Else 0

I might not have those exactly right, but hopefully that gives you the idea.

2 Likes
9

I’m going to work on it. It’s little bit hard to understand, but I think I can play around with to figure it out.

Thank you.

10

Okay. If you get stuck with it, post a screen shot of what you have in the Glide Data Editor, and I’m sure we can help.

11

Ok I fixed it. I have this formula for sum: Month+Day+Year, and this formula for the other equation:1+Mod(Sum-1,9). That equation was to make sure that any sum amount would break down to a single digit answer. I don’t even need to use the if then equation for this since this was to fix an issue in Google sheet. Glide doesn’t have that same issue. Thanks again!

1 Like