Collect Data from Anywhere with Google Sheet's ImportXML Function - Excel Tips and Tricks

Collect data from anywhere with Google Sheet's ImportXML function, which allows you to scrape data from websites and import it directly into your sheet. By specifying an XPath query, you can easily target specific elements and import data without having to manually copy and paste information. This feature is ideal for research, tracking market trends, and analyzing website performance. The ImportXML function is a versatile and efficient tool for collecting and analyzing data from various sources.

Extract City From Address
=TRIM(MID(B2,FIND(",",B2)+2,FIN D(",",B2,FIND(",",B2)+1)-FIND(",",B2)-2))

Extract Population From Webpage (USA) /html/body/article/section[1]/div[1]/p[4]/span
=IMPORTXML(("https://www.citypopulation.de/en/usa/metro"&lower(TRIM(SUBSTITUTE(C2," ","")))),"/html/body/article/section[1]/div[1]/p[4]/span")

Extract Population From Webpage (Canada) /html/body/article/section[1]/table/tbody[2]/tr/td[8]
=IMPORTXML(("https://www.citypopulation.de/en/canada/metro"&lower(TRIM(SUBSTITUTE(C22," ","")))),"/html/body/article/section[1]/table/tbody[2]/tr/td[8]")

Tier Calculation
=IF(D2>=10000000,"Tier 1",IF(D2>=5000000,"Tier 2",IF(D2>=1000000,"Tier 3","Tier 4")))


Source for population
USA
https://www.citypopulation.de/en/usa/metronewyork/
Canada
https://www.citypopulation.de/en/canada/metrocalgary/

ImportXML, google sheet, google, xpath query,
importxml google sheets,import data from password protected website to google sheets,pull data from google spreadsheet to website,importxml locale,how to use importxml,import table from website to google sheets,google sheets import from web,importxml examples,