EDIT: I need help combining functions into 1 and adding a trigger.
In my spreadsheet I have rows 4-100 for customer service calls that are filtered from a "ServiceData" worksheet by either choosing a "Service Month" or "Service Day" (ie. "7/11" shows only 5 rows where "July" would show 65 rows) . Each row item has corresponding Place IDs for origin/destination in column K and L with an order # (as in 1st, 2nd, 3rd... service call of the day) in column J .
I'm using the following custom function travelTime() in cells M4:M100 to calculate driving duration and distance between 2 place IDs:
function travelTime(origin,destination) {
var API_KEY = PropertiesService.getScriptProperties().getProperty('keyMaps');
var baseUrl = "https://maps.googleapis.com/maps/api/distancematrix/json? units=imperial&origins=";
var queryUrl = baseUrl + "place_id:" + origin + "&destinations=" +
"place_id:" + destination + "&mode=driving" + "&key=" + API_KEY;
var response = UrlFetchApp.fetch(queryUrl);
var json = response.getContentText();
var time = JSON.parse(json);
return [[ time.rows[0].elements[0].duration.text,
time.rows[0].elements[0].distance.text ]] ;
A major issue is that many unnecessary service calls are being made to the API when I'm making edits to the "ServiceData" spreadsheet (ie. service date changes when a particular day is over-scheduled) and not needing the travel time updated until I'm done working through a schedule . After researching quite a bit there seems to be several options I could be using; caching, looping, arrays, and putting everything into a script then attach to a button to only run when ready. Considering I'm a newbie, putting all these options together are definitely beyond my skill level and could really use some help.
EDIT with new functions:
So after more researching I have been able to put together the following functions that when each are run independently work great. Now the problem I'm having is putting these all together in particular adjusting the original travelTime()into newTravelTime(). I have made an attempt towards the right direction below but can't figure out how to get the API call in there .
function newTravelTime() {//<--**having issues how to write this function
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("MonthlySA");
var sourceR = sourceSheet.getRange(4, 11, sourceSheet.getLastRow()-3, 4);
var sourceV = sourceR.getValues();
var array = [];
for (var i = 0; i < sourceV.length; i++) {
if (sourceV[i][2] == "") {
var origin = sourceV[i][0];//ori place IDs for API query
var destination = sourceV[i][1];//des place IDs API api query
}
array.push([sourceV[i][2]]);
}
sourceSheet.getRange(4, 13, array.length, 1).setValues(array);
I'd like to create a final getTravelTime() with all the functions and add an OnEdit trigger when either "Service Month" or "Service Day" changes in cells B1 or B2 to run them. If there is any advice with my functions themselves I would really appreciate some help, I am very new with this and trying.
///checks if origin/destination are already in the cacheSheet then return travel time to sourceSheet
function getCachedTravelTime() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("MonthlySA");
var sourceR = sourceSheet.getRange(4, 11, sourceSheet.getLastRow()-3, 4);
var sourceV = sourceR.getValues();
var cacheSheet = ss.getSheetByName("TravelTimeCache");
var cacheR = cacheSheet.getRange(2, 1, cacheSheet.getLastRow()-1, 4);
var cacheV = cacheR.getValues();
var array = [];
for (var i = 0; i < sourceV.length; i++) {
for (var j = 0; j < cacheV.length; j++) {
//if origin/destination columns from sourceSheet match columns on cacheSheet
if (sourceV[i][0]+sourceV[i][1] == cacheV[j][0]+cacheV[j][1]) {
sourceV[i][2] = cacheV[j][2]; //column with travel duration
sourceV[i][3] = cacheV[j][3]; //column with travel distance
}
}
array.push([sourceV[i][2], sourceV[i][3]]);
}
sourceSheet.getRange(4, 13, array.length, 2).setValues(array);
}
///if origin or destination are blank, label as 'missing value'
function missingOD() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("MonthlySA");
var sourceID = sourceSheet.getRange(4, 3, sourceSheet.getLastRow()-3, 12);
var sourceV = sourceID.getValues();
var array = [];
for (var i = 0; i < sourceV.length; i++) {
// if ID has a value
if (sourceV[i][0] != "") {
// if origin or destination is blank
if (sourceV[i][8] == "" || sourceV[i][9] == "") {
sourceV[i][10] = 'missing value';
}
}
array.push([sourceV[i][10]]);
}
sourceSheet.getRange(4, 13, array.length, 1).setValues(array);
}
///if cache not found - get the new travelTime for that origin/destination on sourceSheet...
function newTravelTime() {//<--
}
///...and store the new travelTime() in cacheSheet
function storeTravelTime() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("MonthlySA");
var sourceR = sourceSheet.getRange(4, 11, sourceSheet.getLastRow()-3, 4);
var sourceV = sourceR.getValues();
var cacheSheet = ss.getSheetByName("TravelTimeCache");
var cacheR = cacheSheet.getRange(2, 1, cacheSheet.getLastRow()-1, 4);
var cacheV = cacheR.getValues();
var array = [];
for (var i = 0; i < sourceV.length; i++) {
var duplicate = false;
for (var j = 0; j < cacheV.length; j++) {
if (sourceV[i][0]+sourceV[i][1] == cacheV[j][0]+cacheV[j][1]) {
duplicate = true;
}
}
if(!duplicate){ //if origin/destination columns from sourceSheet are NOT matched on cacheSheet
array.push([sourceV[i][0], sourceV[i][1], sourceV[i][2], sourceV[i][3]]);//columns with new data
}
}
//add new data to last row of cacheSheet
cacheSheet.getRange(cacheSheet.getLastRow()+1, 1, array.length, 4).setValues(array);
}
