Hi, I currently have a runOnEdit script (below) that is set to trigger on changes in Column A, then pull the value from Column D and run it through a function and return the value in Column E. It works great. However, because I am using Zapier to push changes into the trigger column, the runOnEdit function is not working. Based on research, I know that onEdit wont detect changes from API services. So I would like to create a similar function that runs hourly on Google Sheets that basically checks a timestamp column (B) and compares it to another timestamp column (C). If it meets the if condition, then I want it to return the function value in the return column (E) for each cell. I'm using the timestamps to determine whether the trigger column was updated recently in which case I want the sub function to run and if it has not been run recently, then I want it to stop and leave the current value that exists in the result column E.
This is my current runOnEdit script that works great.
function runonEdit(e) {
var sh = e.source.getActiveSheet();
if(sh.getName() !== "Copy of DataFinal" || e.range.columnStart !== 1) return;// 1 is A, 2 is B, 3 is C and so on
var v = e.range.offset(0,5)
var value =v.getValue()
var o = e.range.offset(0, 4)
if (value == "") {
o.setValue("")
return null;
}
var timedis = gettime(value)
o.setValue(timedis)
}
function gettime(f) {
var res= UrlFetchApp.fetch("https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins="+f+"&key=MYKEY");
var content = res.getContentText();
var json = JSON.parse(content);
Logger.log(content);
var time=json.rows[0].elements[0].duration.text;
var dis=json.rows[0].elements[0].distance.text;
var timedis=time+"|"+dis
return timedis
}
Here is the attempt at my new script to run hourly. But i can't seem to figure it out. '''
function runtimedisfunc() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Copy of DataFinal'); //source sheet
var testrange = sheet.getRange('A:Z'); //range to check
var o = testrange.range.offset(0, 5)
var tsza = testrange.range.offset(0,2)
var tstdfunc = testrange.range.offset(0,3)
var v = e.range.offset(0,4)
var combaddvalue =v.getValue()
var timestampza= tsza.getValue()
var timestamptdfunc= tstdfunc.getValue()
if (timestampza < timestamptdfunc){
return null;
}
if (combaddvalue == "") {
o.setValue("")
return null;
}
if ((timestampza != "") || (timestampza > timestamptdfunc)){
var timedis = gettime(combaddvalue)
o.setValue(timedis)
tstdfunc.setValue(new Date())
}
}
function gettime(f) {
var res= UrlFetchApp.fetch("https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins="+f+"&key=MYKEY");
var content = res.getContentText();
var json = JSON.parse(content);
Logger.log(content);
var time=json.rows[0].elements[0].duration.text;
var dis=json.rows[0].elements[0].distance.text;
var timedis=time+"|"+dis
return timedis
}
'''
function runtimedisfunc()
bound to an installable time-driven trigger? – ziganotschka