Make custom function update cells from API call on google form submit

Question

I have created a google form that is linked to google sheets table. Upon submission the data from the form updates columns A-D in the linked sheet. Now I want a custom script to call external API at the time of submission and write data from response to column E. I'm trying to call weather API in order to record weather data at the time user submitted his form.

Below is the function I use to get temperature from public METAR API and it works fine when I call it from a cell in an opened google sheet document. What I can't get right from google documentation is how to trigger it on form submission and link it to column E and current form answer row. As far as I understand it is not possible to call this function from a cell and I will need to reference cell position (row that triggered script + column E) in a script somehow?

function GetMETAR() {
  var url = 'https://avwx.rest/api/metar/KJFK?options=&format=json&onfail=cache';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);
  var tempr = data.temperature.value;
  return tempr;
}

Answer 1

Try this:

function GetMETAR(name,cell) {
  var name=name || 'Sheet2';//if you don't want to use the parameters you can set these defaults
  var cell=cell || 'B4';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(name);
  var rg=sh.getRange(cell);
  var url = 'https://avwx.rest/api/metar/KJFK?options=&format=json&onfail=cache';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);
  var tempr = data.temperature.value;
  rg.setValue(tempr);//cell that you want the data to go to.
}