Building a Stop and Search App using police data (part 2)

Claude.ai describes the app:

This code allows a user to get stop and search data for a particular location in the UK and visualize it in a spreadsheet.

Here is what it does:

- `onOpen()` creates a custom menu with two options:
— “Get Stop and Search Data” — calls `getStopAndSearchData()`
— “Create Stop and Search Chart” — calls `createStopAndSearchChart()`

- `getStopAndSearchData()` prompts the user to enter a UK postcode, then calls `processPostcode()` to fetch data for that postcode

- `processPostcode()` takes the postcode and uses two APIs:
— postcodes.io API — gets latitude and longitude for the postcode
— police.uk API — gets stop and search data for that location

- The stop and search data is written to the active sheet in columns

- A map image of the location is inserted using Bing Maps API

- `createStopAndSearchChart()` aggregates the data by date and writes to a new sheet

- It counts the number of stops per date and inserts a column chart

- The chart has formatted dates, titles and axis labels

So in summary, it provides a nice interface for fetching, visualizing and analyzing stop and search data for a given location in the UK. The various APIs and charts help bring the data to life in the spreadsheet.

The code:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Get Stop and Search Data’, ‘getStopAndSearchData’)

.addItem(‘Create Stop and Search Chart’, ‘createStopAndSearchChart’)

.addToUi();

}

function getStopAndSearchData() {

// Prompt the user to enter a postcode

var ui = SpreadsheetApp.getUi();

var result = ui.prompt(‘Enter a postcode’);

var postcode = result.getResponseText();

// Call the processPostcode function with the entered postcode

processPostcode(postcode);

}

function processPostcode(postcode) {

// Get the latitude and longitude for the given postcode

var url = “https://api.postcodes.io/postcodes/" + postcode;

var response = UrlFetchApp.fetch(url);

var data = JSON.parse(response.getContentText());

var latitude = data.result.latitude;

var longitude = data.result.longitude;

// Get the stop and search data for the given location

url = “https://data.police.uk/api/stops-street?lat=" + latitude + “&lng=” + longitude;

response = UrlFetchApp.fetch(url);

data = JSON.parse(response.getContentText());

// Write the stop and search data to the active sheet

var sheet = SpreadsheetApp.getActiveSheet();

sheet.clear();

sheet.appendRow([“Postcode”, “Latitude”, “Longitude”]);

sheet.appendRow([postcode, latitude, longitude]);

sheet.appendRow([“Date”, “Gender”, “Age Range”, “Outcome”, “Ethnicity”, “Ethnicity Type”, “Object of Search”]);

for (var i = 0; i < data.length; i++) {

var item = data[i];

var ethnicityType = item.self_defined_ethnicity ? “Self-defined” : “Officer-defined”;

var datetime = item.datetime;

var date = datetime.split(“T”)[0]; // Get date part

sheet.appendRow([date, item.gender, item.age_range, item.outcome, item.self_defined_ethnicity || item.officer_defined_ethnicity, ethnicityType, item.object_of_search]);

}

// Add a map showing the location of the postcode using Bing Maps API

var bingMapsApiKey = “YOU NEED YOUR OWN FREE API KEY FOR BING IN WINDOWS”;

var mapUrl = “https://dev.virtualearth.net/REST/v1/Imagery/Map/Road/" + latitude + “,” + longitude + “/15?mapSize=600,300&pushpin=” + latitude + “,” + longitude + “;1&key=” + bingMapsApiKey;

var imageBlob = UrlFetchApp.fetch(mapUrl).getBlob();

sheet.insertImage(imageBlob, 1, sheet.getLastRow() + 2);

}

function createStopAndSearchChart() {

// Get the data from the sheet

var sheet = SpreadsheetApp.getActiveSheet();

var data = sheet.getDataRange().getValues();

// Process the data to calculate the number of stop and searches for each day

var stopAndSearchCounts = {};

for (var i = 4; i < data.length; i++) {

var row = data[i];

var date = row[0];

if (stopAndSearchCounts[date] === undefined) {

stopAndSearchCounts[date] = 0;

}

stopAndSearchCounts[date]++;

}

// Write the processed data to a new sheet

var chartSheet = SpreadsheetApp.getActive().insertSheet(“Stop and Search Chart”);

chartSheet.appendRow([“Date”, “Stop and Searches”]);

for (var date in stopAndSearchCounts) {

chartSheet.appendRow([date, stopAndSearchCounts[date]]);

}

// Format dates in chart

chartSheet.getRange(“A:A”).setNumberFormat(“yyyy-mm-dd”);

// Create a column chart using the processed data

var chartBuilder = chartSheet.newChart()

.setChartType(Charts.ChartType.COLUMN)

.addRange(chartSheet.getRange(“A1:B” + (Object.keys(stopAndSearchCounts).length +1)))

.setPosition(5,5,0,0);

// Add titles and axis labels to chart

chartBuilder.setOption(“title”, “Stop and Searches by Date”)

.setOption(“vAxis.title”, “Number of Searches”)

.setOption(“hAxis.title”, “Date”);

// Build and insert chart

var chart = chartBuilder.build();

chartSheet.insertChart(chart);

}

This code was generated by Bing Chat ai and improved by asking Claude.ai. It was built with free APIs and tools.

Bard says:

This App Script is about getting and visualizing stop and search data. It allows the user to enter a postcode and then retrieves the stop and search data for that location from the Police UK API. The data is then written to a spreadsheet, and a chart is created showing the number of stop and searches by date.

The script has two functions:

• getStopAndSearchData(): This function prompts the user to enter a postcode and then retrieves the stop and search data for that location. The data is then written to the active sheet in the spreadsheet.
• createStopAndSearchChart(): This function creates a chart showing the number of stop and searches by date. The data for the chart is taken from the spreadsheet.

The script uses the following APIs:

• Postcodes.io API: This API is used to get the latitude and longitude for a given postcode.
• Police UK API: This API is used to get stop and search data for a given location.
• Bing Maps API: This API is used to add a map showing the location of the postcode to the spreadsheet.

The script is a good example of how to use Apps Script to get and visualize data from external APIs. It can be used to track stop and search data for a particular location, or to compare stop and search data between different locations.