Import Weather into Google Sheets

user_10459267 0
Viewed 175

I am trying to import the extended 14-day forecast into Google Sheets. Some of the research I have been looking for are outdated and I would love some help for my knowledge gaps.

I tried =importXML and =importHTML and get an Error that says Cannot Fetch URL.
In cell A2 is the URL: "https://www.timeanddate.com/weather/mexico/mexico-city/ext"
In cell B2 is this formula with xpath: =IMPORTXML(A2,"//*[@class='zebra tb-wt fw va-m tb-hover']")
I also tried in B2: =IMPORTHTML(A2, "table", 2)

I also read weather sites may use scripting so I'd have to try importJSON.
I added to Google Sheets App Script from: https://gist.github.com/allenyllee/c764c86ed722417948fc256b7a5077c4#file-import_json_appsscript-js
I then tried: =IMPORTJSON("https://www.timeanddate.com/weather/usa/atlanta/ext?$format=json",table,2)

All gave me errors like Error Fetching URL.

Any instructions/clarifications would be appreciated. Thank you!

Can also try with Google Weather or Weather.com or Accuweather if easier.

weather google-sheets-formula import html json
1 Answers

You have to extract the <script type="text/javascript"> from web source and parse var data

function getWeatherForecast() {
  var url = 'https://www.timeanddate.com/weather/mexico/mexico-city/ext'
  var fetchedUrl = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (fetchedUrl) {
    var html = fetchedUrl.getContentText().replace(/(\r\n|\n|\r|\t|  )/gm, "")
    var data = html.match(/var data=([^<]+)<\/script>/g)
    var jsn = JSON.parse(data[0].slice(9,-10))
    const header = Object.keys(jsn.detail[0]);
    var result = [header, ...jsn.detail.map(o => header.map(h => Array.isArray(o[h]) ? o[h].join(",") : o[h]))];
    var sh = SpreadsheetApp.getActiveSheet()
    sh.getRange(1,1,result.length,result[0].length).setValues(result)
  }
}

enter image description here

However, it seems that after a few tests, the site has blocked the google ip address on my side

IP: 107.178.203.254You have been blocked because we have registered an unusual amount of traffic from your IP address.Please contact webmaster@timeanddate.com if you need more information or believe that this is a mistake.

user_150193800
Related