Search code examples
google-sheetsgoogle-sheets-formula

How do I use importxml formula in google sheet to get data from timeanddate.com?

I want to get UTC difference of each timezone in google sheet. For that, I am looking to import data from timeanddate.com using importxml. But, it says URL not found.

Example for Asia/Jordan: My formula is =importxml("https://www.timeanddate.com/time/zone/jordan","/html/body/div[6]/main/article/section[1]/div[2]/table/tbody/tr[3]/td")

The timezone is in IANA format like Asia/Jordan.

Any kind of help will be highly appreciated. I really need help. Please help.

Solution

  • The site is javascript generated and is a limitation of the import functions.

    Sites that provide these kind of data where it updates real time might not be available for that method. I suggest some other sites like the one below

    =join(" ", query(importxml("https://www.timetemperature.com/middleeast/jordan_time_zone.shtml", "/html/body/div[2]/div[5]/div[1]/table[1]/tbody/tr[2]/td[1]/text()"), "limit 2 offset 4", 0))

    output

    or better yet, you can try and do it via script instead. Get the timezone date via custom function instead of fetching it from a site.

    function getTime(timeZone) {
  return Utilities.formatDate(new Date(), timeZone, 'MM/dd/YYYY hh:mm a');
}

    usage

    • Only the timezone ids present here is accepted.
    • Like the above example, since Asia/Jordan is not on the list, you use Asia/Amman.
    • You can also use actual timezone itself.