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

0
votes

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;
}
2
google-apps-scriptgoogle-sheetsgoogle-forms
Did you try Menu: Edit/Project Triggers and setup a formSubmit for this function? – Cooper
I found this piece of functionality, but I guess since function is called no directly from a cell but by a project trigger, I need to reference the cell in a spreadsheet where I want my function to returm tempr value... - this is a problem... – Tony100500

2 Answers

1
votes

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.
}
0
votes

Thanks @Cooper, here is my final code that worked:

function GetMETAR() {
  var ss=SpreadsheetApp.getActiveSheet();//set active sheet where form answers are stored 
  var lr = ss.getLastRow();//since form pastes last answer to last row we get its number
  var rg = ss.getRange(lr,8,1,5);//here I set range where I will paste info from API CALL
  var url = 'https://avwx.rest/api/metar/UUWW?options=&format=json&onfail=cache';//API url
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});//API call
  var json = response.getContentText();//response JSON to text
  var data = JSON.parse(json);//JSON text parse
  var tempr = data.temperature.value;//parsing values
  var wind_spd = data.wind_speed.value;
  var baro = data.altimeter.value;
  var dew = data.dewpoint.value;
  var wind_dir = data.wind_direction.value;
  var values = [[tempr,wind_spd,wind_dir,baro,dew]];//cobining values in array to paste
  rg.setValues(values);//pasting in range
}

Script is set to be triggered on form submit