Google Sheets调用OpenWeatherMaps API部分字段无法填充求助

你的问题出在OpenWeatherMap API返回的weather字段是数组，而非单个对象，直接用resJSON["weather"]["id"]这种写法会拿到undefined，所以对应单元格无法填充数据。

具体修正步骤： #

OpenWeatherMap的weather字段是包含天气对象的数组（哪怕只有一条天气数据，也会放在数组里），必须先取数组的第一个元素（索引0），再访问里面的属性。

把你代码里的这些行：

const id = (resJSON["weather"]["id"])
const main = (resJSON["weather"]["main"])
const description = (resJSON["weather"]["description"])
const icon = (resJSON["weather"]["icon"])

修改为：

const id = resJSON["weather"][0]["id"]
const main = resJSON["weather"][0]["main"]
const description = resJSON["weather"][0]["description"]
const icon = resJSON["weather"][0]["icon"]

额外优化提示： #

你的API URL里多了&description=&icon=这两个无用参数，虽然不影响请求，但可以删掉让URL更简洁：

let apiURL = "https://api.openweathermap.org/data/2.5/weather?lat=39.952583&lon=-75.165222&units=imperial&appid={insert API here}"

完整修正后的代码： #

function getCurrentData() {
  // API key
  const key = "{insert API here}"
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const wsLocation = ss.getSheetByName("Location")
  const wsLiveData = ss.getSheetByName("Live Data")
  const location = wsLocation.getRange("A2").getValue()
  const tempCell = wsLiveData.getRange("A4")
  const highCell = wsLiveData.getRange("B4")
  const lowCell = wsLiveData.getRange("C4")
  const feelslikeCell = wsLiveData.getRange("D4")
  const idCell = wsLiveData.getRange("E4")
  const mainCell = wsLiveData.getRange("F4")
  const descriptionCell = wsLiveData.getRange("G4")
  const iconCell = wsLiveData.getRange("H4")
  const humidityCell = wsLiveData.getRange("I4")

  let apiURL = "https://api.openweathermap.org/data/2.5/weather?lat=39.952583&lon=-75.165222&units=imperial&appid={insert API here}"

  const resText = UrlFetchApp.fetch(apiURL).getContentText()
  const resJSON = JSON.parse(resText)
  const temp = resJSON["main"]["temp"]
  const tempMin = resJSON["main"]["temp_min"]
  const tempMax = resJSON["main"]["temp_max"]
  const feelslike = resJSON["main"]["feels_like"]
  // 修正weather字段的访问方式
  const id = resJSON["weather"][0]["id"]
  const main = resJSON["weather"][0]["main"]
  const description = resJSON["weather"][0]["description"]
  const icon = resJSON["weather"][0]["icon"]
  const humidity = resJSON["main"]["humidity"]
  
  tempCell.setValue(temp)
  lowCell.setValue(tempMin)
  highCell.setValue(tempMax)
  feelslikeCell.setValue(feelslike)
  idCell.setValue(id)
  mainCell.setValue(main)
  descriptionCell.setValue(description)
  iconCell.setValue(icon)
  humidityCell.setValue(humidity)
}

内容的提问来源于stack exchange，提问作者Jack Denis