lookup country name and return flag image to cell in Google Sheets

Question

I have a country list of 245 countries.

Is there any way I can use a VLOOKUP in Google Sheets to import their respective flags?

I was thinking of potentially using a resource such as Wiki or http://www.theodora.com/flags/ but not sure if I can?

Answer 1

• Sample file * Related article

Step 1. Get links

A1 = http://www.sciencekids.co.nz/pictures/flags.html

B1 = //@src[contains(.,'flags96')]

A3 = =IMPORTXML(A1,B1)

Step2. Use image function

B3 = =IMAGE(substitute(A3,"..","http://www.sciencekids.co.nz"))

Bonus. Country name:

C1 = ([^/.]+)\.jpg$

C3 = =REGEXEXTRACT(A3,C1)

Answer 2

Update:

After writing this and doing a bit more curious Googling, I found the following APIs:

• https://www.countryflags.io/ (for building a country flag url from a country code)
• https://restcountries.eu/ (for getting a country code from a name or partial name)

Which allowed me to create this one-liner formula instead:

=IMAGE(CONCATENATE("https://www.countryflags.io/", REGEXEXTRACT(INDEX(IMPORTDATA(CONCAT("https://restcountries.eu/rest/v2/name/", F3)), 1, 3),"""(\w{2})"""), "/flat/64.png"))

(if anyone knows of a better way to import & parse json in Google Sheets - let me know)

Since these are official APIs rather than "sciencekids.co.nz" it would theoretically provide the following benefits:

• It's a bit more "proper" to use a purpose-built API than some random website
• Maybe slightly more "future proof"
  • Availability: more likely to be available in the future
  • Updated/maintenance: more likely to be updated to include new countries/updated flags

But, big downside: it seems to be limited to 64px-wide images (even the originally posted "sciencekids" solution provided 96px-wide images). So if you want higher-quality images, you can adapt the original formula to:

=IMAGE(SUBSTITUTE(SUBSTITUTE(QUERY(IMPORTXML("http://www.sciencekids.co.nz/pictures/flags.html","//@src[contains(.,'flags96')]"),CONCATENATE("SELECT Col1 WHERE Col1 CONTAINS '/", SUBSTITUTE(SUBSTITUTE(A1, " ", "_"), "&", "and") ,".jpg'")),"..","http://www.sciencekids.co.nz"), "flags96", "flags680"))

which provides 680px-wide images on the "sciencekids.co.nz" site. (If anyone finds an API that provides higher-quality images, please let me know. There's got to be one out there)

Original Post:

To add on to Max's awesome answer, here's the whole thing in a single function:

=IMAGE(SUBSTITUTE(QUERY(IMPORTXML("http://www.sciencekids.co.nz/pictures/flags.html","//@src[contains(.,'flags96')]"),CONCATENATE("SELECT Col1 WHERE Col1 CONTAINS '/", SUBSTITUTE(SUBSTITUTE(A1, " ", "_"), "&", "and") ,".jpg'")),"..","http://www.sciencekids.co.nz"))

(If anyone wants to simplify that a bit, be my guest)

Put this in A2, and put a country name in A1 (eg "Turkey" or "Bosnia & Herzegovina") and it will show a flag for your "search"