OSINT At Home #15 – 3 Simple Steps to Pull, Sort and Map Data

12
Published on August 1, 2022 by

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.

TOOLS
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:
=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_satellite_map_images_with_missing_or_unclear_data“,”table”,0)

MORE RESOURCES
TowardsDataScience: https://towardsdatascience.com/import-html-tables-into-google-sheets-effortlessly-f471eae58ac9
Octoparse: https://www.octoparse.com/blog/scraping-data-from-website-to-excel
Ben Collins: https://www.benlcollins.com/spreadsheets/google-sheet-web-scraper/
Spreadsheetpoint: https://spreadsheetpoint.com/scrape-websites-using-google-sheets-formulas/
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/
Source: http://incompetech.com/music/royalty-free/index.html?isrc=USUAN1400003
Artist: http://incompetech.com/

Category

Add your comment

10 Comments

  • Pacificl L 1 week ago

    Thank you for your videos !

  • marina petrova 1 week ago

    ❤Only for fans over 18 year⤵️ Alles sehr schön. Aber zuerst zusammen die Nummern 10 und 1. Eine verwohn.online Brünette und eine andere Blondine. Es wäre unfair, wennh ich 4 wählen würde

  • Belikov 1 week ago

    Thanks dude

  • Elizabeth King 1 week ago

    ❤Only for fans over 18 year⤵️ Alles sehr schön. Aber zuerst zusammen die Nummern 10 und 1. Eine empru.ONLINE Brünette und eine andere Blondine. Es wäre unfair, wennh ich 4 wählen würde

  • 2xKTfc 1 week ago

    Outstanding, very well communicated example! And this kind of workflow easily adapts to enabling all sorts of other tasks. Oftentimes, powerful methods are not one specific and difficult or secret skill, but rather an elegant and readily available combination of fairly basic skills. Plus knowing which tools are available to start with. In that regard, I think I've strongly underestimated Google Sheets and had no idea it has so many powerful and, better yet, almost foolproof functions! I last evaluated Google Sheets to see if it was worthwhile for me to spend more time getting to know it in detail years ago. Back then it was not exactly spectacular. Clearly, I've missed a bunch of its development since then. My usual way to import tables is to cook up a short R script with one of its web/HTML libraries but this is even more straightforward for many intents and purposes. (Edited this comment a lot, I might have eaten an English syntax clown this morning. Apologies)

  • silent willy 1 week ago

    Is there a comparable approach with OSM? Google maps is horrible for hiking/biking trails, it would be nice to see the relevant trails when mapping facilities.

  • Maksim 1 week ago

    Alles sehr schön. Aber zuerst zusammen die Nummern 10 und 1. Eine empru.ONLINE Brünette und eine anderea Blondine. Es wäre unfair, wenn ich 4 wählen würde

  • snyde21 1 week ago

    Ben, Thank you for taking the time to share! Hope you can help with this – when I do the 'Split text to columns', the info in the first 3 columns disappears and does not come back. In your demo, the same thing happened, but the info came back after the #REF! error (approx 5:20 in the video). Any idea what I'm doing wrong? Anyone else having this issue? Thanks!

  • SalveTheRunner 1 week ago

    Nice video

  • Colin Bradford 1 week ago

    'Map my sheet'…for real? Great video by the way.