Created
November 2, 2020 16:46
-
-
Save andyweiss1982/a876c0edf4a95eabdeb73f6c14a57a0b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: Yelp Brand Distances SOLUTION | |
description: '' | |
host: EXCEL | |
api_set: {} | |
script: | |
content: "const statusText = document.querySelector(\"#status\");\nconst form = document.querySelector(\"form\");\n\nform.addEventListener(\"submit\", handleFormSubmit);\n\nasync function handleFormSubmit(event) {\n event.preventDefault();\n\n const baseBrandRestaurants = await getRestaurants(form.baseBrand.value, form.location.value, form.apiKey.value);\n const competitorBrandRestaurants = await getRestaurants(\n form.competitorBrand.value,\n form.location.value,\n form.apiKey.value\n );\n\n statusText.textContent = `Calculating distance for ${baseBrandRestaurants.length} restaurants...`;\n baseBrandRestaurants.forEach((baseRestaurant) => {\n competitorBrandRestaurants.forEach((competitorRestaurant) => {\n // calculate the distance using lat / long and check if this one is the closest\n const distance = milesBetween(\n baseRestaurant.latitude,\n baseRestaurant.longitude,\n competitorRestaurant.latitude,\n competitorRestaurant.longitude\n );\n // if this is the very first restaurant we are comparing, \n // or, if the distance is less than the previous closest competitor\n // set this competitor restaurant as the closestCompetitor\n if (\n baseRestaurant.distanceToClosestCompetitor === undefined ||\n distance < baseRestaurant.distanceToClosestCompetitor\n ) {\n baseRestaurant.distanceToClosestCompetitor = distance;\n baseRestaurant.closestCompetitor = competitorRestaurant;\n }\n });\n });\n statusText.textContent = `Done!`;\n generateWorksheet(baseBrandRestaurants)\n}\n\nasync function getRestaurants(brand, location, apiKey) {\n const corsProxy = \"https://rbi-tech-bootcamp-cors-proxy.herokuapp.com/\";\n const limit = 50;\n const maxRestaurants = 1000;\n const allRestaurants = [];\n\n for (let offset = 0; offset < maxRestaurants; offset += limit) {\n statusText.textContent = `Searching through ${offset +\n limit} businesses in ${location} looking for ${brand} restaurants...`;\n const url = `https://api.yelp.com/v3/businesses/search?term=${brand}&location=${location}&limit=${limit}&offset=${offset}`;\n\n const response = await fetch(corsProxy + url, {\n headers: {\n Authorization: `Bearer ${apiKey}`\n }\n });\n\n if (response.ok) {\n const data = await response.json();\n const restaurants = data.businesses;\n const formattedRestaurants = restaurants.map((restaurant) => {\n return {\n id: restaurant.id,\n name: restaurant.name,\n rating: restaurant.rating,\n latitude: restaurant.coordinates.latitude,\n longitude: restaurant.coordinates.longitude,\n address: restaurant.location.display_address.join(\", \")\n };\n });\n formattedRestaurants.forEach((restaurant) => {\n if (restaurant.name.toLowerCase() === brand.toLowerCase()) {\n allRestaurants.push(restaurant);\n }\n });\n } else {\n statusText.textContent = `There was an error \U0001F915`;\n return allRestaurants;\n }\n }\n\n statusText.textContent = `Done!`;\n return allRestaurants;\n}\n\nfunction milesBetween(lat1, lon1, lat2, lon2) {\n if (lat1 == lat2 && lon1 == lon2) {\n return 0;\n } else {\n const radlat1 = (Math.PI * lat1) / 180;\n const radlat2 = (Math.PI * lat2) / 180;\n const theta = lon1 - lon2;\n const radtheta = (Math.PI * theta) / 180;\n let dist = Math.sin(radlat1) * Math.sin(radlat2) + Math.cos(radlat1) * Math.cos(radlat2) * Math.cos(radtheta);\n if (dist > 1) {\n dist = 1;\n }\n dist = Math.acos(dist);\n dist = (dist * 180) / Math.PI;\n dist = dist * 60 * 1.1515;\n return dist;\n }\n}\n\nasync function generateWorksheet(restaurants) {\n await Excel.run(async (context) => {\n // generate a new sheet\n const sheetName = form.outputSheet.value.replace(/\\\\\\\\\\/\\*\\?:\\[\\],/g, \"\");\n const tableName = `Distances${sheetName}`.replace(/[^a-zA-Z0-9]/g, \"\");\n const sheets = context.workbook.worksheets;\n const targetSheet = sheets.add(sheetName);\n await context.sync();\n // put a table on the sheet\n const table = targetSheet.tables.add(\"A1:G1\", true /* table has headers */);\n table.name = tableName;\n await context.sync();\n // set the table headers\n table.getHeaderRowRange().values = [[\n `${form.baseBrand.value} ID`,\n `${form.baseBrand.value} Address`,\n `${form.baseBrand.value} Rating`,\n `${form.competitorBrand.value} ID`,\n `${form.competitorBrand.value} Address`,\n `${form.competitorBrand.value} Rating`,\n 'Miles' \n ]];\n // set text columns as TEXT format\n table.columns.getItemAt(0).getRange().numberFormat = [[\"@\"]];\n table.columns.getItemAt(1).getRange().numberFormat = [[\"@\"]];\n table.columns.getItemAt(3).getRange().numberFormat = [[\"@\"]];\n table.columns.getItemAt(4).getRange().numberFormat = [[\"@\"]];\n // add a row to the table for each restaurant\n const tableRows = restaurants.map(restaurant => {\n return (\n [\n restaurant.id,\n restaurant.address,\n restaurant.rating,\n restaurant.closestCompetitor.id,\n restaurant.closestCompetitor.address,\n restaurant.closestCompetitor.rating,\n restaurant.distanceToClosestCompetitor\n ]\n )\n })\n table.rows.add(null /*add rows to the end of the table*/, tableRows);\n await context.sync();\n // sort the table by years of experience\n const sortRange = table.getDataBodyRange();\n sortRange.sort.apply([\n {\n key: 6,\n ascending: true,\n },\n ]);\n await context.sync();\n targetSheet.getUsedRange().format.autofitColumns();\n await context.sync();\n targetSheet.activate()\n await context.sync();\n });\n}" | |
language: typescript | |
template: | |
content: "<h1>Brand Closest Competitor Analysis</h1>\n<h2>Status: <span id=\"status\">Not Running</span></h2>\n<form>\n\t<div>\n\t\t<label for=\"base-brand\">Base Brand</label>\n\t\t<input id=\"base-brand\" name=\"baseBrand\" type=\"text\" required autocomplete=\"off\"></input>\n\t</div>\n\t<div>\n\t\t<label for=\"competitor-brand\">Competitor Brand</label>\n\t\t<input id=\"competitor-brand\" name=\"competitorBrand\" type=\"text\" required autocomplete=\"off\"></input>\n\t</div>\n\t<div>\n\t\t<label for=\"locaton\">Location</label>\n\t\t<input id=\"locaton\" name=\"location\" type=\"text\" required autocomplete=\"off\"></input>\n\t</div>\n\t<div>\n\t\t<label for=\"output-sheet\">Output Sheet</label>\n\t\t<input id=\"output-sheet\" name=\"outputSheet\" type=\"text\" required autocomplete=\"off\" maxlength=\"31\"></input>\n\t</div>\n\t<div>\n\t\t<label for=\"api-key\">API Key</label>\n\t\t<input id=\"api-key\" name=\"apiKey\" type=\"password\" required autocomplete=\"off\"></input>\n\t</div>\n\t<div>\n\t\t<button type=\"submit\">Submit</button>\n\t</div>\n</form>" | |
language: html | |
style: | |
content: |- | |
* { | |
box-sizing: border-box; | |
font-family: monospace; | |
} | |
h2 { | |
font-size: 1rem; | |
} | |
input, button { | |
width: 100%; | |
padding: 0.5rem; | |
font-size: 1.5rem; | |
} | |
div { | |
margin: 1rem 0; | |
} | |
button { | |
background-color: dodgerblue; | |
color: white; | |
border: 0; | |
cursor: pointer; | |
} | |
language: css | |
libraries: | | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
@types/office-js | |
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
core-js@2.4.1/client/core.min.js | |
@types/core-js | |
jquery@3.1.1 | |
@types/jquery@3.3.1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment