Convert list of Google Maps links to exact address

Question

I have a list of addresses in a csv. Eg:

https://www.google.com/maps/place/Cahoots+London/data=!4m2!3m1!1s0x487604d507ab69fb:0xbbfbc270f421a878 https://www.google.com/maps/place/Mr+Fogg's+Gin+Parlour/data=!4m2!3m1!1s0x487604cdb617ba29:0xcef59d8e87aa23df

I want to convert these into the exact address from maps i.e.

13 Kingly Ct, Carnaby, London W1B 5PW 1 New Row, London WC2N 4EA

Is there an easy way to do it in bulk for 500+ URLs?

PS: Haven't tried using Google APIs yet

Answer 1

You may try:

=let(Σ,importxml(A2,"//meta[@property='og:title']/@content"),
 mid(Σ,find("·",Σ)+2,9^9))

• excessive usage of import functions can result in a scenario of import_data loading forever in the sheet. I'd suggest to do few hundreds at a time → then copy-paste the output addresses as values which effectively removes the import formulas for those cells that are done.