Cell Formula gets erased

[Simon_Blackburn]

I’m a courier and have made an app to keep track of my earnings and using a google sheet for my data.

Column A is a date column.

I have cell B2 containing the total number of deliveries for the day which is populated via the appsheet form.

Cell B3 contains the number of failed deliveries for the day which again is populated from the form.

Cell B4 (formated as currency) on the sheet contains the formula =sum(B2-B3)*1.10 to give me the amount earned for the day.

When I enter the contents of the form for the day, the data is sent to the google sheet for cells B2 & B3 but the formula in cell B4 gets erased and thus isn’t displayed in my app.

Can anyone explain why and maybe propose a work around?

Thanks.

[Steve]

Row 1 of the worksheet should not be used for data at all; it should contain column names.

[Simon_Blackburn]

Sorry Steve, I obviously meant B1, B2 and B3. I’m half asleep. Apologies for the confusion.

[Steve]

Not so obviously, as that’s just as wrong.

I’m not inclined to guess what you mean.

Good luck,

[Lynn]

Also, why are you using rows and not columns. I dont see how your setup would work.

[LeventK]

@Simon_Blackburn
Your back-end gSheet for your AppSheet App shall be in this structure explicitly

Sheet Columns	A	B	C	D	E
Sheet Row#1 - Column Name/Header	ID	DATE	DELIVERIES	FAILED	EARNINGS
Sheet Format	Plain Text	Date	Number	Number	Currency
AppSheet Column Type	Text	Date	Number	Number	Price
AppSheet Table Key?		-	-	-	-
AppSheet Initial Value	=UNIQUEID()	=TODAY()	0	0	-
AppSheet AppFormula	-	-	-	-	=([DELIVERIES] - [FAILED]) x 1.10
Sheet Row#2	AbR5CTYu	11/26/2020	10	5	$5.50
Sheet Row#3	ER34kaX0	11/27/2020	17	3	$15.40

That’s all you need to do. Very simple, very straight-forward and very very basic.

[Simon_Blackburn]

Thank you for correcting what I had attempted. Your help is very much appreciated.

[LeventK]

You’re welcome, it’s my pleasure to be helped of.
@Simon_Blackburn
You don’t need to construct any formula in your gSheet, construct them directly in the AppSheet column structure (referred to as AppFormula)

[Simon_Blackburn]

I’ve recreated my app and G-sheet just as you advised and its great so thanks again.

If I wanted to add a column to add a running total of my pay on a daily basis, how would I do this directly in appsheet so its like in the image. It’s quite easy to do in the sheet but I wouldn’t know where to start in app sheet.

[LeventK · Accepted solution]

@Simon_Blackburn

---

OPTION#1

---

You can construct it like this:

Sheet Columns	A	B	C	D	E	F
Sheet Row#1 - Column Name/Header	ID	DATE	DELIVERIES	FAILED	EARNINGS	RUNNING TOTAL
Sheet Format	Plain Text	Date	Number	Number	Currency	Currency
AppSheet Column Type	Text	Date	Number	Number	Price	Price
AppSheet Table Key?		-	-	-	-	-
AppSheet Initial Value	=UNIQUEID()	=TODAY()	0	0	-	-
AppSheet AppFormula	-	-	-	-	=([DELIVERIES] - [FAILED]) x 1.10	=SUM(SELECT(TableName[EARNINGS],[DATE] <= [_THISROW].[DATE]))
Sheet Row#2	AbR5CTYu	11/26/2020	10	5	$5.50	$5.50
Sheet Row#3	ER34kaX0	11/27/2020	17	3	$15.40	$20.90

---

OPTION#2

---

You can create a Virtual Column and name it i.e. [WEEKDAY] with below expression and then with the Table UX for this table, you can group the table as per this Virtual Column and aggreate for [EARNINGS] colum

SWITCH(
    WEEKDAY([DATE]),
    1, "Sunday",
    2, "Monday",
    3, "Tuesday",
    4, "Wednesday",
    5, "Thursday",
    6, "Friday",
    "Saturday"
)

[Simon_Blackburn]

Thanks. I used the formula as suggested and it work perfectly. Thank you. I can see how that works and hope to be able to create something similar in the future from it.

[LeventK]

Excellent! You’re welcome @Simon_Blackburn, it’s my pleasure to be helped of.