Calculate Driving Miles Using Google App Script

[weowens1984]

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]

I'd prevent the cell formula from recalculating if the cell already has a value. To do this in Google Sheets you should do the following:

1. Enable Reiterative Calculations for you Sheet: File --> Settings --> Calculation. 
   
   
2. Adapt your sheets formula to something like this:
   
   = IF(RC = 0; Calculate(); RC)

[weowens1984]

Hey Joseph,

I assume that when you used 'RC' in your formula you were referring to Reiterative Calculations, how do I pull this as a value? Can you expand a bit on this formula?

Thanks so much for your help!

[Joseph_Seddik · Accepted solution]

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. 

[weowens1984]

Thank you so much. I will give this a try and see where it takes me. 

[weowens1984]

Would I set the max number of iterations to 1?

[Joseph_Seddik]

This will not affect our objective of having the cell calculate only once, because this is controlled by the formula. Just leave it at the default 50. 

[Stephaniebp]

Hello, nice to meet you.
Please, I hope you can help me.

I am using RC for a conditional,
SUM.IF('Request detail'!B:B;RC[-4];'Request detail'!F:F)
I am trying to do sums according to the order code, but the RC does not work for me even though I already enabled it in settings as you explained.

Thank you in advance for your help

[weowens1984]

Rather than providing the RC formula found in Appsheet, can you provide the formula as you want it to appear in Google Sheets?