Filling in weather from different towns

[Cedarleaf]

I made an Excel document in Google Sheets. I have it set up to automatically pull the weather from the internet using =IMPORTXML. We work in 2 different towns and I would like the weather to pull for the town we are in at the moment. So basically if I choose “Sitka” it populates the Sitka weather, if I choose “Ketchikan” I get Ketchikan weather. Is this possible?

[LeventK]

There are a bunch of free weather REST APIs on the internet that you can use. I may advise to construct a time triggered script which may run in hourly intervals and update weather data in a gSheet. You can do that for multiple cities and use table grouping to present that data in the app.
This is the one that I’m using mostly.

weatherstack.com

API Documentation - weatherstack

[LeventK]

@Cedarleaf

---

GOOGLE SPREADSHEET SETUP

---

Sheet could be like this:

Column Name	Data Type	Key?	Initial Value	AppFormula
ID	Text		-	-
TIMESTAMP	Datetime	-	-	-
CITY	Text	-	-	-
ICON	Image	-	-	-
DESC	Text	-	-	-
TEMP	Number	-	-	-
FEELS_LIKE	Number	-	-	-
WIND_SPEED	Number	-	-	-
WIND_DEGREE	Number	-	-	-
WIND_DIR	Text	-	-	-
PRESSURE	Number (thousands off)	-	-	-
PRECIPITATION	Number	-	-	-
HUMIDITY	Number	-	-	-
UV_INDEX	Number	-	-	-
VISIBILITY	Number	-	-	-

---

GOOGLE APPS SCRIPT

---

const WEATHER_API_KEY = "YOUR_API_KEY_HERE";
const SHEETID = "YOUR_SPREADSHEET_ID_HERE";
const SHEETNAME = "YOUR_SHEET_NAME_HERE";
const myCities = ["Sitka" , "Ketchikan"]; //You can add more cities in this array if needed

function getWeatherData(queryCity) {
  var baseURL = "http://api.weatherstack.com/current?access_key="+WEATHER_API_KEY+"&query="+encodeURIComponent(queryCity);
  var data = UrlFetchApp.fetch(baseURL);
  
  if (data.getResponseCode() === 200) {
    var response = JSON.parse(data.getContentText())
    var weatherArray = [UUID(),response.location.localtime,response.location.name,response.current.weather_icons[0],response.current.weather_descriptions[0],response.current.temperature,
                        response.current.feelslike,response.current.wind_speed,response.current.wind_degree,response.current.wind_dir,response.current.pressure,response.current.precip,
                        response.current.humidity,response.current.uv_index,response.current.visibility];
    SpreadsheetApp.openById(SHEETID).getSheetByName(SHEETNAME).appendRow(weatherArray)
  }
}

function UUID(){
   var chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
   var uuid = [], rnd = Math.random, r;
   uuid[8] = uuid[13] = uuid[18] = uuid[23] = '-';
   uuid[14] = '4'; // version 4

   for (var i = 0; i < 36; i++) {
      if (!uuid[i]) {
         r = 0 | rnd()*16;
         uuid[i] = chars[(i == 19) ? (r & 0x3) | 0x8 : r & 0xf];
      }
   }
  return uuid.join('').split("-")[0];
}

function getMyWeather(){
  myCities.forEach(function(city){
    getWeatherData(city)
  })
}

You can then set an hourly (or any desired interval) time trigger for the getMyWeather() function. On how to set a script trigger, you can check below post.

How to Integrate Google Apps Script & Trigger with an AppSheet App Tips & Tricks 💡

5 - GOOGLE APPS SCRIPT TRIGGERING On the last step, we will set the installable trigger, so that the code will be automatically executed when the data in the spreadsheet changes. The code will be executed if the conditional criteria inside the code is met. To set the trigger, goto Edit > Current project’s triggers menu item on the script editor [image] A new browser window will be opened, showing all the triggers set for that spreadsheet container so far. If you haven’t set any, the windo…

---

DEMO APP

---

[GW375]

Thanks @LeventK ! This solution is excellent! Works great! This will certainly save lots of Zaps on my Zapier account!

[LeventK]

You’re welcome.

[GW375]

I need some help utilizing this new weather table within my app. My weather table is built exactly as @LeventK describes in this great post. I set the App Script trigger to run hourly rather than on a spreadsheet update/edit. This is because there are many changes and updates in my app which would result in many similar unnecessary weather rows. I have added the table (‘weather’) to my app and set the column types appropriately. My question is, how do you set up references to tables that are updated outside of Appsheet input?

In the fishing app I’m working on I have a table called ‘Trips’ This table includes a column TripNumber which is a unique primary key, The table also has StartDate, StartTime, StopDate, StopTime and Status. Status is a virtual column that changes from “Fishing” to “Complete” once the trip ends. I also concatenated the StartDate/Time and StopDate/Time in virtual columns. I would like to set up a 1 to many relationship between ‘Trips’ and the ‘Weather’ table. I can’t figure out how to create a reference between these tables. I would like to pass the TripNumber to the weather table when the LocalTime field within the ‘weather’ table is between the StartDate/Time and StopDate/Time and the trip status is “Fishing”. Since I am new to appsheet and app script I was hoping to do this within appsheet where I have slightly more knowledge. Any help would be great. Thank you!

[LeventK]

@GW375
As your TripNumber is the key column to your table, you cannot convert that column to a REF type. However, you can create a List type VC and name it i.e. TripWeather and construct a FILTER() expression like:

FILTER(
	"WeatherTableName",
	(AND(
		[TIMESTAMP] >= [_THISROW].[START DATETIME], //StartDate/Time VC
		[TIMESTAMP] <= [_THISROW].[STOP DATETIME] //StopDate/Time VC
		[CITY] = [_THISROW].[LOCATION] // Location Column
	))
)

And you can set a SHOW_IF expression for this VC:

“Fishing” = [STATUS]

Check below update in my demo app. I have also set an Editable_If for the Start/End Datetime columns (so that they can’t be edited if the [Status] is Complete) and assign a Quick Edit mode on them in the Detail View, so that the Virtual Weather Data list became dynamic directly from the detail view. If you change the dates (as per mock data), the list will re-arrange itself.

---

REMINDER

---

The WeatherStack API is free only for 1000 API calls per month. With a timely trigger it’s 24 calls per day and 741 calls per month. However even bad API calls or null returns are also counted as a legit call, I may advise following your API usage from their dashboard > https://weatherstack.com/usage

---

[GW375]

Thanks @LeventK!! Your solution worked perfectly and your example app was extremely useful! I also understand the filter expression much better now!

Thanks for the reminder about the 1000 API calls with WeatherStack. So far I have been very impressed with the reliability. I haven’t had any null returns yet.

[LeventK]

You’re welcome @GW375, my pleasure to be helped of. Happy New Year!

[Cedarleaf]

@LeventK

How can I make the temperature record in Fahrenheit degrees?

Thanks

[JoaquinBalaguer]

Hello! Great contribution LeventK! I will apply it in an app! Today and for this specific case of general or non-specific api queries, you can add the extension or plugin to the google spreadsheet, called API Connector. There configure the api queries and schedule them. The most important thing is that you don't need to write a code! Greetings 🙂