Created
October 30, 2020 20:22
-
-
Save andyweiss1982/b930bcc56b189138f801512a24ab7fb2 to your computer and use it in GitHub Desktop.
Search for developers across markets and languages
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: 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment