Google Sheets – Fixing Parameter Mismatch in setValues Method

Getting data by parsing the HTML source of a page is a fragile method: when the pages gets redesign, the import is most likely going to break down because the data you want are not where you expect. It's no coincidence that most of examples on Google's own IMPORTXML documentation page are broken by now.

I recommend using Google Distance Matrix API instead (it is a subset of Google Maps API). It also accepts HTTP requests and can return data as XML; but this XML will have predictable, documented structure for the lifetime of the API version.

Examples of parsing Google's XML response with XPath are found here.

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}