This tutorial is part 15 of the OSINT At Home series. The tutorial is a simple and easy-to-follow guide on how to pull data from websites that have data seen in a table. The automatic pull allows you to scrape a website’s content directly into Google Sheets. Then this tutorial shows how to split columns of text in Google Sheets to clean the data, and then how to make a map from Google Sheets.
Being able to scrape data in Google Sheets, sort it and quickly display it in a map is a crucial asset for anyone working in research with data, trends, analysing movements or activity throughout the world.
Being able to pull data directly into Google Sheets saves a lot of time which is always important, whether you’re a researcher, hobbyist or journalist. In this specific case, I wanted to see if there was a map of blurred locations on Google Maps. I was looking to see if there were any spots near me where this was happening, so I scraped the table from Wikipedia directly into Google Sheets, then sorted the columns and mapped that data. It was a very simple process and saved me time. Otherwise, I would have had to manually copy and paste that data.
The tools covered in this tutorial include Google Sheets and a Google Sheets add-on called Map My Sheet.
The OSINT At Home series is useful for those looking to find digital breadcrumbs and pick up some methods of open source intelligence (OSINT), digital investigations and good old plain research. No matter who you are, or where you are in the world, you can follow these tutorials from home with publicly available information to answer questions such as who, what, where and when.
Google Sheets https://www.google.co.uk/sheets/about/
Map My Sheet: https://workspace.google.com/marketplace/app/map_my_sheet/165796308680
Google Sheets function IMPORTHTML:
Ben Collins: https://www.benlcollins.com/spreadsheets/google-sheet-web-scraper/
Alternative more powerful method from Google Earth: https://www.google.com/earth/outreach/learn/mapping-from-a-google-spreadsheet/
CREDITS FOR THIS TUTORIAL
Music Intro: World’s Fair – God Mode
Music Title and End: Dhaka by Kevin MacLeod is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/