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.
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.
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.
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.
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…
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