Search for developers across markets and languages

Github API.EXCEL.yaml

name: Github API
description: Search for developers across markets and languages
host: EXCEL
api_set: {}
script:
  content: >
    const corsProxy = "https://rbi-tech-bootcamp-cors-proxy.herokuapp.com/";


    const form = document.querySelector("form");

    const button = document.querySelector("button");


    form.addEventListener("submit", handleFormSubmit);


    async function handleFormSubmit(event) {
      event.preventDefault();
      const candidates = await getAllCandidateInfo(
        form.location.value,
        form.language.value,
        Number(form.experience.value),
        form.apiKey.value
      );
      generateWorksheet(candidates);
    }


    async function getMatchingCandidates(location, primaryLanguage,
    yearsExperience, apiKey) {
      const status = document.querySelector("#status");
      // Get today's date
      const date = new Date();
      // Subtract yearsExperience from the year
      date.setFullYear(date.getFullYear() - yearsExperience);
      // format that as a string
      const beenCodingSince = date.toISOString().split("T")[0];

      const allCandidateLogins = [];

      let page = 1;

      while (true) {
        status.textContent = `Fetching ${page * 100} ${primaryLanguage} candidates in ${location}...`;

        const baseUrl = `https://api.github.com/search/users`;
        const params = `?per_page=100&page=${page}&q=type:user+language:${primaryLanguage}+created:<=${beenCodingSince}+location:${location}`;
        const fullUrl = corsProxy + baseUrl + params;

        const response = await fetch(fullUrl, {
          headers: {
            Authorization: `token ${apiKey}`
          }
        });

        const data = await response.json();
        const someCandidates = data.items || [];

        someCandidates.forEach((candidate) => allCandidateLogins.push(candidate.login));

        page += 1;

        if (someCandidates.length === 0) {
          break;
        }
      }

      return allCandidateLogins;
    }


    async function getCandidateInfo(username, apiKey) {
      const url = `https://api.github.com/users/${username}`;

      const response = await fetch(corsProxy + url, {
        headers: {
          Authorization: `token ${apiKey}`
        }
      });
      const data = await response.json();

      const formattedData = {
        username: data.login,
        name: data.name,
        company: data.company,
        location: data.location,
        email: data.email,
        hireable: data.hireable,
        bio: data.bio,
        twitter: data.twitter_username,
        yearsExperience: new Date().getFullYear() - Number(data.created_at.split("-")[0]),
        followers: data.followers
      };

      return formattedData;
    }


    async function getAllCandidateInfo(location, primaryLanguage,
    yearsExperience, apiKey) {
      const status = document.querySelector("#status");
      button.disabled = true;
      const candidateLogins = await getMatchingCandidates(location, primaryLanguage, yearsExperience, apiKey);

      const allCandidateInfo = [];

      for (let i = 0; i < candidateLogins.length; i++) {
        const username = candidateLogins[i];
        status.textContent = `Fetching data ${i + 1} of ${
          candidateLogins.length
        } ${primaryLanguage} candidates in ${capitalize(location)}...`;
        const candidateInfo = await getCandidateInfo(username, apiKey);
        allCandidateInfo.push(candidateInfo);
      }
      button.disabled = false;
      status.textContent = "Done!";
      return allCandidateInfo;
    }


    function capitalize(txt) {
      return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
    }


    async function generateWorksheet(candidates) {
      const status = document.querySelector("#status");
      status.textContent = "Compiling...";
      await Excel.run(async (context) => {
        // generate a new sheet
        const sheetName = form.outputSheet.value.replace(/\\\\\/\*\?:\[\],/g, "");
        const tableName = `Candidates${sheetName}`.replace(/[^a-zA-Z0-9]/g, "");
        const sheets = context.workbook.worksheets;
        const targetSheet = sheets.add(sheetName);
        await context.sync();
        // put a table on the sheet
        const table = targetSheet.tables.add("A1:F1", true);
        table.name = tableName;
        await context.sync();
        // set the table headers
        table.getHeaderRowRange().values = [["NAME", "EMAIL", "TWITTER", "COMPANY", "EXPERIENCE", "FOLLOWERS"]];
        // add a row to the table for each candidate
        const tableRows = candidates.map(candidate => {
          return(
            [
              candidate.name,
              candidate.email,
              candidate.twitter,
              candidate.company,
              candidate.yearsExperience,
              candidate.followers
            ]
          )
        })

        table.rows.add(null /*add rows to the end of the table*/, tableRows);
        await context.sync();
        // sort the table by years of experience
        const sortRange = table.getDataBodyRange();
        sortRange.sort.apply([
          {
            key: 4,
            ascending: false,
          },
        ]);
        await context.sync();
        targetSheet.getUsedRange().format.autofitColumns();
        await context.sync();
        targetSheet.activate()
        await context.sync();
      });
    }
  language: typescript
template:
  content: "<h1>GitHub Candidate Finder</h1>\n\n<h2>Status: <span id=\"status\">Not Running</span></h2>\n\n<form>\n\t<div>\n\t\t<label for=\"language\">Language</label>\n\t\t<input id=\"language\" name=\"language\" type=\"text\" autocomplete=\"off\" required></input>\n\t</div>\n\t<div>\n\t\t<label for=\"location\">Location</label>\n\t\t<input id=\"location\" name=\"location\" type=\"text\" autocomplete=\"off\" required></input>\n\t</div>\n\t<div>\n\t\t<label for=\"experience\">Years Experience</label>\n\t\t<input id=\"experience\" name=\"experience\" type=\"number\" min=\"0\" autocomplete=\"off\" required></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\" autocomplete=\"off\" required></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\" autocomplete=\"off\" required></input>\n\t</div>\n\n\t<div>\n\t\t<button type=\"submit\">Submit</button>\n\t</div>\n</form>"
  language: html
style:
  content: |-
    * {
      font-family: monospace;
      box-sizing: border-box;
    }

    input, button {
      width: 100%;
      padding: 0.5rem;
      font-size: 1.5rem;
    }

    div {
      margin: 1rem auto;
    }

    button {
      background: 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