Yelp Brand Distances SOLUTION.EXCEL.yaml

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