I’m looking for a way to delay the clear fields action.
The app shows the user the results of the ratio calculation from the top row of the GSheet. I have an onchange trigger which clears those results when a new row containing user email, timestamp, amount of “A” and amount of “B” is added. This also clears the results from the UI. I need those results to “stick” in the UI for some amount of time as the operation the user is performing will take, lets say, 5 minutes and the user may need to refer back to the results.
My goal is to avoid having the user have to click on the form submit because they will probably forget and then the next user will have erroneous data populated which could cause confusion and/or mistakes.
So, perfect world = User enters amount of “A” and hits Calculate Dose button. Dose is calculated and displayed on the same screen and data is added to new row on Gsheet. Displayed dose data is available to user for “X” time or until app is closed.
Why don’t you use user specific columns to store the entered data, so each user can see their own version of the results? Then you don’t have to worry about each user seeing what another user entered. You wouldn’t have to worry about clearing the fields then. Each user could clear them themselves whenever they want.
you can copy one of the values at the end of the script, and write to a copy column… then in Glide, add if else column that will compare if these values are the same… this way you will know if data is sync to glide and block some elements if not…
but as @Jeff_Hager said… is much easier in your case to use user-specific columns… unless the calculations are impossible to made in Glide table
and why 5 min ? script will run it in a blazing speed and glide will sync it in max 5 to 10 sec…
Also, you don’t need to use script to clear the row… use set column - clear value action
The Glide table will not perform the calculations required, I tried.
5 Minutes or until the app is closed is required as the task being performed can involve multiple steps and I would like them to be able to refer back to the result until they are finished.
I’m having trouble visualizing how your app is set up, but I was picturing a single row with user specific columns that take the user input and calculate the results. Then they can click a button with an add row action to save it permanently if they want. The next time they go to that screen, you could have a custom action to first clear the user specific values and then show the details screen. I wasn’t imagining any need to delete a row.
Again, I’d probably need screenshots or a video to better understand what you have and what you want.
Quick background on this project: This task is something that’s going to be dumped on someone who probably doesn’t want to do it and probably doesn’t give a sh*t. I’m trying to make it as simple and easy as possible so our product is utilized properly.
User enters gallons of fuel added to tank. Hits the calculate button. As the calculation is completed automatically by the Gsheet all the button does is takes them to the results tab, but the user doesn’t need to know this.
So I need these results to be: A) User specific so others can use the app simultaneously. B) Recorded in the Gsheet in a new row and C) Available to the user while they complete the task and then automatically cleared perhaps by a custom action as you mentioned.
A. Do calculations in user sheet, so nobody can see it and others can do their own in the same time… this way it dont need to be usc
B. Add row with record to a data sheet
C. Users can edit or delete that record
As @Uzo said, you could use the user profile sheet, and filter by signed in user, so you wouldn’t need USC columns, but that’s only if you require users to sign in. But if you are trying to keep it simple, I’m guessing you don’t want to have users sign in.
I would still use a user specific column to store the number of gallons. The math seems like it would be reasonably simple, so you could use a math column to calculate, in real time, the amount of enerburn needed based on the user specific column value and display the results as the user is typing. This would eliminate any delay in waiting for the sheet to recalculate and sync the result back to glide and back to the app. All you would need to save, is have a button that would write the entered gallons and the calculated values to a new sheet row. I could probably work it out in a few minutes if you have a copyable app that I can copy and modify to show what I mean.
And if you have a problem calculating using math column… use YC column with Excel formulas… as @Jeff_Hager says… you will have a delay if calculations are made in gs…
I think he is using some query or import range to do calculations… that’s why he insists to use gs… he might be scrapping current gas prices from some websites
No fuel prices involved.
I’m using GS because the glide sheet won’t do formulas like
=(MRound(((I2*128)/2500),0.5)) or =IF(J2>128,(ROUNDdown((J2/128),0)),0)
What is a YC column?
This is a quick and dirty example. I’m not 100% sure on the math, but it matches all the examples in your sheet. Let me know if that looks correct and makes sense.
I don’t accept 1oz deviations so I made some changes.
OK, I reworked the formulas and I think I have it matching the dosing table exactly. Liters is a little weird because according to the chart, everything less than 1 liter is calculated to 2 decimal places, but anything larger than 1 liter is calculated to only 1 decimal place, so I had to calculate it twice to round properly based on 1 or 2 decimal places and use an IF column to determine which calculation to use for the template results based on if the calculated liters are larger than 1 or not (could have probably done all of that in a single math column, but the formula would have been really ugly, so for simplicity I split it up and used the IF column). I also had to fix the gallon and ounce calculations to account for those edge cases where ounces round to 128 ounces instead of 0 and the gallons fail to round up when it should be, or rounds up when it shouldn’t.
I think it’s all matching the dosing chart if you want to check and compare.
For testing purposes, I added a bunch of rows to the ‘Main’ table so I can check multiple calculations at once. Those rows labeled as DELETE can be deleted.
So what my version of the app demonstrates is that a user can enter the gallons and as they type, the enerburn amount is calculated in real time. The fuel added value is written to a user specific column, so every user would see their own calculations without interfering with any other users. You could add a button to clear the column or the user could just clear it with the backspace key. Either way it will not interfere with any other users who may be using the app at the same time. Since they can clear it themselves, I see no reason to have some sort of time based trigger to clear the column, as it will be a unique experience for each user.
The Save button just uses an Add Row action to write the data to another table. You could write the Fuel Added value along with all of the calculated values to multiple columns which would be very simple to do, but my example only writes the Fuel Added value and recalculates the Enerburn values in the Log table (I only added a couple of calculated math columns to save time). I did that only because I figured you may want to allow a user to edit a saved entry, so if they edit an existing row, you would need to recalculate Enerburn values in that log table. That part is pretty much up to how you want that part of the app to flow.
Hopefully this gives you enough to play with and see how you can integrate some of that into your app.
