These blog posts are part of research I’m doing for a potential new book, hopefully allowing me to further my love for spreadsheets. Theses posts are hoping to provide a practical cookbook of examples to the reader and allow me to learn what people are interested in and how to approach different subjects working with Google Sheets.
This dashboard could be part of a wider weather forecast app, or similar steps could be used to provide a forecast of statistical information. The steps involved are not too in depth as you will see from the following:
What You Need To Know To Get This Done?
For todays post, to create the weather forecast dashboard, you will need to be able to do the following. We will step you through things further on in the post, but as a general guideline, you will need to be able to:
All formulas used can be referenced at the following page.
The following steps will walk you through how to create the dashboard on your own Google Sheet, starting with accessing the weather forecast API:
1.Access the weather API: In this instance, we are going use the Visual Crossing website, which provides weather forecast data for the location of your choosing. At the moment, Visual Crossing allows up to 1000 weather forecasts a day on the free account. To get access to your weather forecast, follow the steps below:
2.You can now import your data. In my case, I created a new sheet and added the IMPORTDATA function to the B13 cell, to allow me to set up the dashboard on top of the data. If you have not used this function before, all you need to do is add the URL you copied in the previous step, in between the function brackets. For example, your unique URL will looks similar to the incomplete one I have added here:
=IMPORTDATA("https://weather.visualcrossing.com/VisualCrossingWebServices….)
3.We can now start creating our dashboard, using the data we are now importing from Visual Crossing:
In the centre of our dashboard, add in today’s date under the C3 cell using the =TODAY() function
We can now add todays day name. In our instance we are adding this function in the E3 cell. We use a SWITCH function that takes the value of “$C$3” and uses the WEEKDAY function. This will give us a day number of the week. Using the SWITCH function, we then allocate the day based on the number, for example, day 1 is “Sun”. The full formula is listed below:
=switch(WEEKDAY($C$3),1,"Sun",2,"Mon",3,"Tues",4,"Wed",5, "Thur",6,"Fri",7,"Sat")
Now that we have today set up, we can also add in the weather forecast for today as well. From the data we have imported from Visual Crossing, we can simple grab the value for “tempmax” that we have listed in D14. Cell E3, as we have in the image below, you will simply enter the value =D14
In the image below, we can see that row 5 has the rest of the days of the week. We can add them in again by referencing our TODAY value in $C$3, but for each day, we need to add to that number depending on how many days we are from today. For example, the formula for A5 adds one to the weekday value, as we can see below:
=switch(WEEKDAY($C$3 + 1),1,"Sun",2,"Mon",3,"Tues",4,"Wed",5, "Thur",6,"Fri",7,"Sat")
=IF(M14 < 10, $A$15, IF(AND(M14>11,M14<70), $A$16, IF(M14 > 71, $A$17, "" )))I think at this point, you should have a nicely formatted and sharp looking dashboard. We have signed up with Visual Crossing and signed up to their weather API. We have used the IMPORTDATA function to import the data into our sheet. We are then using this data to populate our dashboard and some nice formulas to provide the week day for our dashboard, and an image that gives us insight at a glance on the weather forecast. I hope this was useful and clear for you to use in your own examples. For any issues or questions, please reach out to me on my LinkedIn account at the details below.