0

Access to HEBCAL data from within google sheets

David Kra 9 months ago updated by anonymous 8 months ago 1

This is a tip, not a question nor complaint.

It is possible to access Hebcal data from within google sheets through the XML API.

For example,

=IMPORTXML("https://www.hebcal.com/converter/?cfg=xml&hy=5780&hm=Adar&hd=25&h2g=1","//hebcal/gregorian/@month") 
=IMPORTXML("https://www.hebcal.com/converter/?cfg=xml&hy=5780&hm=Adar&hd=25&h2g=1","//hebcal/gregorian/@day") 

The result becomes the value of the cell.

You can get multiple result values in one query. The the first result value goes into the cell with the formula, and others go below it, into cells which must be empty, with neither a formula nor a value already in them. This makes it unfeasible for one query to fill in multiple columns in a table.

=IMPORTXML("https://www.hebcal.com/converter/?cfg=xml&hy=5780&hm=Adar&hd=25&h2g=1","//hebcal/gregorian/@month|//hebcal/gregorian/@day") 

Do not overburden hebcal.com with a blast of hundreds of queries when the sheet loads or recalculates. Throttle usage by putting the query into an IF so that it only fires if some condition is TRUE.

For example:

=IF($AN8,IMPORTXML("https://www.hebcal.com/converter/?cfg=xml&h2g=1&hy="&AO8&"&hm="&AP8&"&hd="&AQ8,"//hebcal/gregorian/@day")," ")

where

AN8 is TRUE or FALSE

AO8 is the Jewish year

AP8 is the Jewish month

AQ8 is the Jewish day of the month


Thank you to the people at Hebcal. Mo'adim L'simchah.