我创建了一个链接到Google表格表的Google表单。提交后,从链接表中的"更新表单更新"列A-D列A-D。现在,我希望一个自定义脚本在提交时调用外部API,并从响应列编写数据。我正在尝试调用天气API,以便在用户提交其表格时记录天气数据。
下面是我用来从公共元API获得温度的功能,当我从打开的Google Sheet文档中的单元格中调用它时,它可以正常工作。我从Google文档中无法获得的是如何在表单提交中触发它,并将其链接到E列和当前表单答案行。据我了解,不可能从单元格调用此函数,我需要以某种方式参考单元位置(触发脚本 列的行(?
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;
}
尝试以下:
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.
}
谢谢@cooper,这是我有效的最终代码:
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
}
脚本设置为在表单上触发