0

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.

BigBen
  • 46,229
  • 7
  • 24
  • 40
user18308583
  • 81
  • 1
  • 9

1 Answers1

0

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.
NightEye
  • 10,634
  • 2
  • 5
  • 24
  • Thank you so much for this @ASyntuBU. I learnt how to write the syntax with your answer. But, I have few issues. 1 = My excel has column with timezone in IANA format like Asia/Jordan. How do I change that part to match with URL of website. 2]Can you please help with writing a script. I don't have coding background. Please. I will be thankful to you for this. 3] Also, any other website from where I can get just the UTC difference of each timezone. I tried a lot but to no avail. – user18308583 Feb 25 '22 at 16:10
  • I even tried website - time.is. My code is, Example: For Asia/Jordan, IMPORTXML("https://time.is/jordan,"/html/body/div[2]/div[2]/div[2]/div") This link gives me time but is not current time (pre-DST time), that is 1 hour behind. Can you please help me solve this? Please. – user18308583 Feb 25 '22 at 17:26
  • I have modified my answer to include custom function @user18308583, see updated answer above. – NightEye Feb 26 '22 at 08:59
  • Thank you so much for this. I owe you a lot. It worked but 1 error and 2 challenges. Error - 1 - It gives output in the cell but when i run script, it says - "Exception: Invalid argument: timeZone. Should be of type: StringDetails" . Also, when I hover the formula (It says unknown function). Now, Challenges - 1) How to make time change every minute. The trigger does not work. In logs, it gives error like - Exception: Invalid argument: timeZone. Should be of type: String 2)How i get output in the cell in date and format. Isdate(cell value) in sheet gives false as output. Please please help. – user18308583 Feb 26 '22 at 15:05
  • Of course it wont execute when run in editor since you are not passing anything when running the actual function. Also, this is not meant to be used for frequent update/refresh like every minute. If you want, post another question as this already answered your original question. We are going away from the original issue. – NightEye Feb 27 '22 at 04:14
  • Oh, thank you very much for this. I will post another question. Thanks again a lot for this. – user18308583 Feb 27 '22 at 05:59
  • I have posted a new question - https://stackoverflow.com/q/71282631/18308583 Please please help. – user18308583 Feb 27 '22 at 06:35