Solved: Calculate Driving Miles Using Google App Script - Page 2

weowens1984 · 03-04-2022 01:58 PM

I recently came across the need to calculate the driving distance between two points to calculate shipping cost. Since Appsheet only has the capability to calculate distance in a straight line I turned to Google App Script.

First I tried creating a custom function and had Appsheet input the spreadsheet formula. This worked for a minute until I had 100+ rows and it started hanging up my app while it was waiting for all the spreadsheet formulas to load. Additionally, at times if you opened the spreadsheet, the cells with the formulas would say "Error Loading". I found that this was probably due to too many requests sent to Google Maps.

My most recent attempt uses a trigger that when Appsheet adds a new row to the table, GAS sends a request to Google Maps a single time and puts the value in the appropriate cell for the new row. This works perfectly other than when you save a form you don't see the updates from GAS until manually syncing. This method sped up my sync times significantly.

Does anyone have any other ideas or recommendations?

Joseph_Seddik

No. Sorry for not being clear.

Reiterative Calculation is just an option that you need to enable for this solution to work.

RC is another thing. It is just a commonly-used notion for RowColumn, i.e the address of your cell.

Suppose that your sheet formula is in the cell E2 (RC is E2). This cell currently has a formula to calculate the distance that is =Calculate(). What I'm proposing, after enabling Reiterative Calculations, is to change the formula for the cell E2 to the following:

= IF(E2 = 0; Calculate(); E2)

The purpose is to make the spreadsheet calculate the formula only once.

View solution in original post