I am working on a project overview/scheduling sheet that I can keep track of all my jobs and make scheduling changes based off weather forecasts in the job-specific location.

I have successfully been able to pull in the weather data through an API from my location sheet, with one location, and place that pulled data into my report sheet.

 
 const key = "99071c9629eded162bb1a2e8b7843888"
 const ss = SpreadsheetApp.getActiveSpreadsheet()
 const wsLocation = ss.getSheetByName("Location")
 const wsLiveData = ss.getSheetByName("Live Data")
 const wsHistory = ss.getSheetByName("History")
 
 const location = wsLocation.getRange("A2").getValue()
 const units = wsLocation.getRange("B2").getValue()
 
 const highCellHa = wsLiveData.getRange("B2")
 const lowCellHa = wsLiveData.getRange("C2")
 
 let apiURL = "https://api.openweathermap.org/data/2.5/weather?q=${location}&appid=${key}&units=${units}"
 
 const resText = UrlFetchApp.fetch(apiURL).getContentText()
 const resJSON = JSON.parse(resText)
    console.log(resJSON["main"])
 
 const tempMin = resJSON["main"]["temp_min"]
 const tempMax = resJSON["main"]["temp_max"]
 highCell.setValue(tempMax)
 lowCell.setValue(tempMin)
 
 wsHistory.appendRow([new Date(),tempMax,tempMin]) 
 }
}

I need to be able to have it pull the weather data from multiple locations however and then place each respective locations data into the report sheet in its own spot. I have tried to use a for loop, but to no avail. Any ideas are welcomed and appreciated!!

CodePudding user response：

In your script, how about the following modification?

Modified script:

function myFunction() {
  const key = "###"; // Please set your key.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsLocation = ss.getSheetByName("Location");
  const wsHistory = ss.getSheetByName("History")
  const location = wsLocation.getRange("A2:A"   wsLocation.getLastRow()).getValues();
  const units = wsLocation.getRange("B2").getValue();
  const date = new Date();
  const reqs = location.map(([a]) => ({url: `https://api.openweathermap.org/data/2.5/weather?q=${a}&appid=${key}&units=${units}`, muteHttpExceptions: true}));
  const res = UrlFetchApp.fetchAll(reqs);
  const values = res.map(r => {
    if (r.getResponseCode() == 200) {
      const temp = JSON.parse(r.getContentText());
      return [date, temp.main.temp_min, temp.main.temp_max];
    }
    return [date, null, null];
  });
  wsHistory.getRange(wsHistory.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

• In this modification, it supposes that the values of location are put to the column "A" of "Location" sheet. Please be careful about this. And, the retrieved values are put to the History sheet.

• In your script, highCell and lowCell are not declared. Please be careful about this.

References:

• fetchAll(requests)
• map()

CodePudding user response：

The apiURL has to be enclosed in back ticks to use template literals ${}

Try this:

function lfunko() {
  const key = "99071c9629eded162bb1a2e8b7843888"
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const wsLocation = ss.getSheetByName("Location")
  const wsLiveData = ss.getSheetByName("Live Data")
  const wsHistory = ss.getSheetByName("History")
  const locations = wsLocation.getRange(2, 1, wslocation.getLastRow() - 1).getValues()
  const units = wsLocation.getRange("B2").getValue()
  const highCellHa = wsLiveData.getRange("B2")
  const lowCellHa = wsLiveData.getRange("C2");
  locations.forEach(l => {
    let apiURL = `https://api.openweathermap.org/data/2.5/weather?q=${l}&appid=${key}&units=${units}`;
    let resText = UrlFetchApp.fetch(apiURL).getContentText()
    let resJSON = JSON.parse(resText)
    let tempMin = resJSON["main"]["temp_min"]
    let tempMax = resJSON["main"]["temp_max"]
    highCell.setValue(tempMax);
    lowCell.setValue(tempMin);
    wsHistory.appendRow([new Date(), tempMax, tempMin]);
  });
}

There may need to be other data in locations but since you have not provided your data structure there is no way to know that