onChange to Copy Down Formulas

Hello everyone!

BACKGROUND

From the Home screen, users can Add a new entry. (the foster care travel reimbursement process is abysmal)

The components from ‘Date’ to ‘Round Trip?’ are filled in by the user. ‘Miles’ and ‘Claim Estimate’ are calculated in the Google Sheet.

QUESTION

Is there a way to use onChange() to copy down the ‘Miles’ and ‘Claim Estimate’ formulas only when a new entry is created by the user from the app?

In Google Sheet terms, when the user Adds a new entry in the app creating new travel details in a Google Sheet row (columns A-F), I’d like it to copy down the formulas in columns G &H.

Any help would be so appreciated.

1 Like

Have you looked into arrayformula’s? Sounds like your formulas are simple enough. There is the math column in the data tab as well, but you don’t have quite the flexibility to format the result.

1 Like

Thank you! I haven’t looked into arrayformulas yet but will right now.

The reason I’d like to use an onChange (possibly?) is because filling in the cells with formulas preemptively adds blank entries into the app. I’d like it to add the formulas as the user adds new entry information.

I did not know the math column existed! I’m sure that will work for ‘Claim Estimate’ but the ‘Miles’ macro function I stole that uses Google Maps to calculate the distance between two addresses.

Please read https://docs.glideapps.com/all/guides/quick-starts/intermediate-techniques/calculating-columns

1 Like

The docs make it look like it’s exactly what I need but it’s not bringing the formula down. Could the reason be that it’s a custom function?

What does your formula look like right now with the arrayformula wrapped around it?

=arrayformula(GOOGLEMAPS(B2:B,C2:C,“miles”))

B2 is Address 1
C2 is Address 2

Can you try something and add I2:I + right before the call to GOOGLEMAPS? I think you have to trick it into processing an array of rows and it has to be outside of the function call.

1 Like

I feel like we’re actually getting closer even though it gave me errors all the way down. At least it’s populating for the open-ended array now!

To avoid getting empty entries in the app, wouldn’t checking cells B and C help?
=arrayformula(if(B2:B="","", GOOGLEMAPS(B2:B,C2:C,“miles”))

You may also expand the IF condition to check for both cells.

That makes sense to me…but it errors out and keeps the formulas from iterating down the column for some reason.

Your adresses don’t seem to be correct, some of them are followed by “OK”, and others seem to be too short to be real address… You should have a look I think…

OK for Oklahoma. They are the same addresses I’ve been using to calculate the distance with the standard formula which worked fine.

Could you tell us what the error is? Put your mouse on the red triangle on one of the cell in error.

1 Like

Using Jeff_Hager’s trick with the I2:I + it errors with “Internal error executing the custom function.”

Not sure why you have an error, but I’m finding there is still an issue. It seems you cannot use an Arrayformulas to iterate each row as a separate parameter into a function.
You would probably have to change the function to pass in a range and then return a range of rows. I’ll keep playing, but I’m not sure yet if it will work. Arrayformulas are so unintuitive to me sometimes. Something that makes perfect sense in my head won’t always translate well to array formulas.

One alternative would be to create a second sheet and prepopulate the formulas several times. Then use a formula to pull in the data from the first sheet. =Sheet1!A1:F

1 Like

Maybe for someone else’s benefit but I think this may be the custom function issue: https://stackoverflow.com/questions/57945431/how-to-use-a-custom-function-with-an-arrayformula

Regardless, the second sheet strategy then feeding it into the first sheet with the formula array seems to be the closest thing to working!

However, there may be a bug because now when the user adds a new entry in the app it doesn’t write it to the Google Sheet.

It should be writing it. Make sure your arrayformulas aren’t pushing the record to the bottom of the sheet. To fix this, just delete the empty rows.

1 Like

I don’t think you can use custom functions in ARRAYFORMULA.

Ended up going with Jeff’s recommendation to utilize a second sheet.

Created a second sheet and copied the formulas down. Then used the OFFSET formula to pull it back into the ARRAYFORMULA in the first sheet.

(used OFFSET because the creation of new rows in sheet one broke the references in sheet 2)

I’m honestly still scatterbrained after trying so many different things but now it works so thanks to everyone who helped! (Especially Jeff_Hager)

1 Like