Mapping, Counting, Recovering: Humanities through the Digital Lens' Workshop - UCL DH / QMU - March 2024

README.md

Mapping, Counting, Recovering: Humanities through the Digital Lens' Workshop - UCL DH / QMU - March 2024

Introduction

This workshop session will show you how to turn a raw data spreadsheet in excel into ann interactive digital map using some free online tools and some code on this page. Learn how to Geocode some data in a Google Sheet and then export the data onto geojson.io.

Tutorial Video Link

• https://youtu.be/xfdHzVriFe8 - 44 mins

Tools used for this Example

• Google Sheets - https://sheets.new
• Google Drive - https://drive.google.com
• GeoJSON.io - https://geojson.io

Further Reading

• AppScript Documentation - https://developers.google.com/apps-script

address.gs

function geoCodeSpreadsheet(){
    addressToPosition("E2:F");
}

function addressToPosition(range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Women and History of Art in the Making in Post-War Britain 1945-1974");
  var cells = sheet.getRange(range);
  
  var addressColumn = 1;
  var addressRow;
  
  var latlngColumn = addressColumn + 1;
  
  var geocoder = Maps.newGeocoder().setRegion('UK');
  var location;
  
  for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
    var address = cells.getCell(addressRow, addressColumn).getValue();
    var geocoded_value = cells.getCell(addressRow, latlngColumn).getValue();

    if(address != "" && geocoded_value == ""){
      location = geocoder.geocode(address);
   
      if (location.status == 'OK') {
          lat = location["results"][0]["geometry"]["location"]["lat"];
          lng = location["results"][0]["geometry"]["location"]["lng"];
      
          cells.getCell(addressRow, latlngColumn).setValue(lat + "," + lng);
      }
    }
  }
};

geocode.gs

function createGeoJSONFiles(){
  generateGeoJSON(5, "wa_place_of_birth.geojson");
}
function generateGeoJSON(latLngCol, fileName) {
  // Get the spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Women and History of Art in the Making in Post-War Britain 1945-1974");
  var cells = sheet.getRange("A1:V");

  // Define the columns containing coordinates and attributes
  var latLngCol = latLngCol; // Replace with your actual column number for LatLng

  var otherAttributes = ["Surname", "Nee", "First Name", "Date of Birth", "Place of Birth", "place_of_birth_geo","death","place_of_death","place_of_death_geo","trained_at", "trained_at_geo", "graduated",	"degree","subject","thesis_title", "student_of","institutions","active_start","active_end", "role", "expertise",	"archive"]; // Replace with your desired attributes and their column numbers


  // Get the values
  var values = cells.getValues();
  
  // Initialize an empty GeoJSON object
  var geoJSON = {
    "type": "FeatureCollection",
    "features": []
  };
  
  // Loop through each row of data
  for (var i = 1; i < values.length - 1 ; i++) {

    var latLng = values[i][latLngCol];
    
    if(latLng != ""){
      latLng = latLng.split(",");
      var lat = parseFloat(latLng[0]);
      var lng = parseFloat(latLng[1]);

      var feature = {
        "type": "Feature",
        "geometry": {
          "type": "Point",
          "coordinates": [lng, lat] // Change order if needed
        },
        "properties": {}
      };
    
      // Add properties based on other attributes
      for (var j = 0; j < otherAttributes.length; j++) {
        feature.properties[otherAttributes[j]] = values[i][j];
      }
    
      // Add the feature to the collection
      geoJSON.features.push(feature);
    }
  }
  
  // Convert object to JSON string
  var jsonString = JSON.stringify(geoJSON);

  Logger.log(jsonString);

  // Save the JSON string as a file
  DriveApp.createFile(fileName, jsonString, MimeType.PLAIN_TEXT);
  
  // (Optional) Display a message
  Logger.log("GeoJSON file created successfully!");
}