Import Live Weather Data to Google Sheets - Spreadsheet & API Tutorial
HTML-код
- Опубликовано: 24 ноя 2020
- Learn how to import live weather data to Google Sheets. For a spreadsheet we'll use Google Sheets & to get weather data we'll use openweathermap.org/ API.
#weather #spreadsheet
Awesome, I've been waiting for it for almost 6 years, thx a lot
Every time i see new tutorial of you in my notifications i get excited!
I have been looking for this specific video in your channel for awhile now, and subscribed with the bell for awhile now, THANK YOU!
прекрасный урок по работе с api в google sheets! огромное спасибо от вашего фаната из России :-)
This is an incredible tutorial, and exactly what I was looking for. Thank you!
Glad it was helpful!
Nice work Sirji. I learned something new today. I really appreciate your work. Keep working like this.
Awesome! This is really great!!! Thank you!!!
Thank you for this. A really useful solution and very well explained
This is very useful script, thanks for sharing...
THANK YOU !!
this was for me the first WOW result about what I could do with my own hands in coding, and I owe it all to you! What about getting the forecasts? I saw that there are a lot of nested objects
i have no code background at all, However i was able to follow along with your explanation and even fetched the "Feels like" and "Humidity" too. Honestly, Thank you
Excellent!
Great video, learned a ton from this. Does anyone know how to pull hourly forecast data using this method? Seems a bit more complicated
Thank You!
:)
Very impressive, but not that easy to understand.
Probably one could do implent it by copy/paste.
Wish you will continue with with your great google sheets series.
Greetings from Switzerland
Terence
Awesome. Is there a way to get data from multiple locations on the spread sheet
most power ..Google sheet
Wonderfull, I want to make the same thing with Currency Converter, any idea where to start and where to get Data / API?
Hello. Thank you very much for your tutorials. Is it possible to scrape data from a website similar to amazon (using importXML, i already managed to import part of it thanks to your videos) but then saving it to another tab in that same spreadsheet? in order to track the price evolution of an item during a period of time? I also know that there are tools like camelcamel or other pice trackers available, but happens that the website i'm scraping is not precisely amazon and is not tracked by those trackers
No.
I ran into this issue and had to build my own web scraper using a Javascript library called Puppeteer then posting the data to a Google sheet, this works great and i use it all the time. Import xml is too limited to scrape websites which don't want to be scraped
I want to but haylerlink in data validation..so I can click in my Data validation cell and choose this haylerlink move me to this sheet.. thank
Is it possible to get a premade code to copy and paste into our script? Then add our API code?
Way over my abilities but I learn much by watching...Thanks. Note: I notice that now in my Google Sheets in Tools their is now AppSheet. Would you be doing a tutorial on creating a simple App? I've watched and read up on this but you have a very detailed way of teaching and I'm curious and do need to make a simple phone timesheet app for employees and volunteers where I help at. Again, Great teaching and learning much. Thank You!
So far didn't find AppSheet useful. Maybe if I spend more time with it and they add more features, it will change my mind.
This is all new and Google did away with their AppScript site and rebuilt it and this part works with Google Sheets. Not much info on how to use. I see it worthwhile for mobile phones. I'm always curious about technology, not that I know what I'm doing but I learn from my mistakes. www.appsheet.com/SampleApps
TypeError: Cannot read property 'getSheetByName' of null (line 5, file "Code")
What would you do in this scenario
I want to import simple temperature from website to google sheets single cell
Thank you much, but pls how can we add Rainfall also?
hello! how do we add more than one location ie a different city?
How do I define the code to use multiple cities at once?
This is great, I was also able to modify it to upload other info from the forecast API. But how can I get the forecasted weather info that would be 4 hours ahead?
You'll need to find an API service that supports this.
@@ExcelGoogleSheets I too am trying to do a 5 Day forecast using OpenWeather. I have replaced the API URL, but it's not working. Says: TypeError: Cannot read property 'temp' of undefined, for: const temp = (resJSON["main"]["temp"]).
It's exactly the same code as the current weather, even the url when pasted into a browser is the same. What am I doing wrong?
How to use XML as opposed to JSON?
seems like this works for others, I'm getting several errors though like code not being valid... I dunno what's going wrong on my end :-(
Hi, many thanks for this video. It is possible to fetch a csv file on web using google script and insert it into google sheets?
Use IMPORTDATA function support.google.com/docs/answer/3093335?hl=en
@@ExcelGoogleSheets unfortunately that functions goes in timeout! After 20 calls it doesn’t work anymore! That’s why I’m looking for a custom script to fetch it.
Hi, My script has worked until last month , but from June 2021 I did not temp array in JSON it return [object] only , the api works well via browser , I think openweathermap has blocked google sheets for temp arrays . Is there any solution ?
I have the same problem
Thanks for this, got it working, adding in Temp/Temp Max/Temp Min/Feels Like/Sunrise/Sunset/Wind Speed. However, I am unable to add in the Description or Main that refers to the clouds etc! This: "weather":[{"id":803,"main":"Clouds","description":"broken clouds","icon":"04d"}] It's that square bracket which is not on the other types that seems to be the issue. Appreciate help with this please. Thanks
Hi, did you find a solution? I'm stuck with the same issue. Thanks!
Having the exact same issue , did anyone have any luck on working that out?
Hope he can post a solution on how to retrieve objects from the "weather" array :(
Super late but you just need resJSON ["weather"][0]["main"].
Please make a video on on following use case.
Suppose we are using Google sheet as a database and using alasql as query languag(as explained by you). Suppose I there are multiple sheet which ate used as main database. Now if a value is changed in the a sheet ,the same value used in other sheet must also change just like a database.
More elaboration. Suppose employee ID is used as a key between two sheet. Now if Employee ID is changed in one sheet it should also be changed in the other sheet also automatically..
Please share this code. I'm facing issue.
Thanks in Advance.
The most interesting info (the real weather) is inside an array. How do I extract that info, like "clody" or "sunny"? :(
Hi, how do I add multiple cities? For example if I wanted to get the high's and low's for 25 cities each time I ran it
yeah me too i need this
define n const for each n city
I'm struggling a bit with many functions and differences between ', ", ´ etc. Is this different with other language settings etc? I'm also getting this error a lot: TypeError: wsHistory.appendrow is not a function, even if I don't even have any variables inside, like this: wsHistory.appendrow()
Silly me, wsHistory.Appendrow is case sensitive!
Yes, there are differences in JavaScript between these text qualifiers. ` allows you to use ${variable} syntax. The other 2 would not.
@@ExcelGoogleSheets Ok! But do these change with language settings too? For instance, I have to use ; instead of , as a delimiter with all functions in Sheets
I don't think they do.
Google sheets have changed in 2023, scripts are under extensions in the tool bar now.
still runs tho' :)
Please I want create drop-down menu in google forms I mean I want serch The name I want if I have 500 names
Hey the JavaScript move you made in the apiURL string ${variable} doesn’t work for me. Says invalid argument. Any thoughts??
I'm not sure. Share your code.
I also got the same response. I tried hard coding the key and location, instead of using $ {} , and it works. Not the best solution. Perhaps someone with better knowledge of coding can come up with a solution.
The location I used was copied from the sheet and the key was copied from line 3, so they were identical.
Do you use any other keyboard then "EN"?!!
You must use ` for this to work. Not " or '
Also must be under V8 runtime.
Hi, great video. I really need some help:
This is a code that will enable me to select a range from 1 workbook, copy it then paste it in the last row of another workbook. The only downside I have is pasting only values. I've already spent a few hours trying to solve this but I am stuck. Someone, please help modify the code so that it only paste values.
Sub copy_paste_Cell2()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim LastRow As Long
'Set variables for copy and destination sheets
Set selectedRange = Application.Selection
Set wsCopy = Workbooks("New-Data.xlsx").Worksheets("Export 2")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
'1. Select Range to copy
Set selectedRange = Application.InputBox("Select the range that you want to copy: ", "CopyOnlyValues", selectedRange.Address, Type:=8)
'2. Find first blank row in the destination range
With Workbooks("Reports.xlsm")
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
End With
'3. Copy & Paste Data
selectedRange.Copy wsDest.Range("A" & Rows.Count).End(3)(2)
'wsDest.Activate
End Sub
instead of
selectedRange.Copy wsDest.Range("A" & Rows.Count).End(3)(2)
Try this
selectedRange.Copy
wsDest.Range("A" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues