10/30 Week 3, Day 5: More APIs, Script Lab

[ajlee12]

Morning Exercise Review (GitHub API)

This is the URL we're working with:

https://api.github.com/search/users?per_page=100&page=1&q=type:user+language:ruby+created:<=2015-10-30+location:miami

We can get different data by changing a few values in the URL:

• e.g. What if we want data about JavaScript developers instead?
  • Replace ruby with javascript

Let's make the call to the API by interpolating our parameters into the URL:

• Let's assume you stored your token in an .env file already.

  
  const baseUrl = 'https://api.github.com/search/users';

async function getMatchingCandidates(location, primaryLanguage, beenCodingSince) { const params = ?per_page=100&page=1&q=type:user+language:${javascript}+created:<=${beenCodingSince}+location:${location}; const options = { headers: { Accept: token ${process.env.GITHUB_TOKEN}, }, };

const response = await fetch(baseUrl + params, options);

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

}

The limitation with the fetch above is that it only gets us the first page of results.

**What if we want all the available results?**

Let's use a loop to continue to call for more results.
- We can modify the `per_page` and `page` values in the URL to get more than just page 1.
```js
const baseUrl = 'https://api.github.com/search/users';

async function getMatchingCandidates(location, primaryLanguage, beenCodingSince) {
  const maxResults = 1000;
  const resultsPerPage = 100;
  const maxPages = Math.ceil(maxResults / resultsPerPage);

  const allCandidates = [];

  const options = {
  headers: {
    Accept: `token ${process.env.GITHUB_TOKEN}`,
  },

  for (let page = 1; page <= maxPages; page += 1) {
    const params = `?per_page=${resultsPerPage}&page=${page}&q=type:user+language:${primaryLanguage}+created:<=${beenCodingSince}+location:${location}`;

    const response = await fetch(baseUrl + params, options);

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

    // Once we get a page # with no data, stop the loop.
    // e.g. If on page 4 we got no data, we won't make more API calls to get pages 5 - 10.
    if (someCandidates.length === 0) break;

    allCandidates.push(...someCandidates);
  }

  /* The while-loop alternative to for-loop:
  let page = 1;

  while (true) {
    const params = `?per_page=100&page=${page}&q=type:user+language:${primaryLanguage}+created:<=${beenCodingSince}+location:${location}`;

    const response = await fetch(baseUrl + params, options);

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

    // This line ensures that the loop ENDS when we hit the end of the available data from the API.
    if (someCandidates.length === 0) break;

    allCandidates.push(...someCandidates);
    page += 1;
  }
  */

  return allCandidates;
}

Let's invoke that async function above and find devs in Toronto who works with JS for at least 3 years:

async function getTorontoJsDevsWithThreeYearsExp() {
  const devs = await getMatchingCandidates('Toronto', 'Javascript', '2017-10-30');

  return devs;
}

getTorontoJsDevsWithThreeYearsExp();

---

Let's use the same API to get candidate's info on GitHub.

• We're interested in username, name, company, location, email, whether they're hireable, bio, twitter...

The endpoint for getting user info is:

• GET /users/:username

async function getCandidateInfo(username) {
  const url = `https://api.github.com/users/${username}`;
  const options = {
    headers: {
      Authorization: `token ${process.env.GITHUB_TOKEN}`,
    },
  };

  const response = await fetch(url, options);
  // 'data' is a big object containig user's info.
  const data = await response.json();

  const yearsExp = new Date().getFullYear() - Number(data.created_at.split('-')[0]);

  const userInfo = {
    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: yearsExp,
    followers: data.followers,
  };

  return userInfo;
}

async function getAndyInfo() {
  const andyInfo = await getCandidateInfo('andyweiss1982');
  return andyInfo;
}

Then you'll see this:

---

Working with Dates

Get the current date:

const date = new Date();
// date -> Fri Oct 30 2020 08:12:41 GMT-0700 (Pacific Daylight Time)

What if we want 3 years from now?

date.setFullYear(date.getFullYear() - 3);
// date --> 1509376371495

Turn it into something more readable:

date.toISOString();
// date -> "2017-10-30T15:12:51.495Z"

Format the date string to YYYY-MM-DD:

date.toISOString().split('T')[0];
// date -> "2017-10-30"

---

Bringing getMatchingCandidates() and getCandidateInfo() together

Within getMatchingCandidates(), instead of pushing all info of all candidates into allCandidates array, we can push only the usernames.

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

So the results from getMatchingCandidates() is now an array of GitHub usernames.

Let's write another function:

async function getAllCandidateInfo(location, primaryLanguage, yearsExperience) {
  const candidateLogins = await getMatchingCandidates(location, primaryLanguage, yearsExperience);

  const allCandidateInfo = [];

  // Don't use .forEach here!
  // .forEach doesn't play nicely with async-await.
  for (let i = 0; i < candidateLogins.length; i += 1) {
    const username = candidateLogins[i];

    const candidateInfo = await getCandidateInfo(username);

    allCandidateInfo.push(candidateInfo);
  }

  return allCandidateInfo;
}

async function logTorontoJsDevsWithThreeYearsExp() {
  const devs = await getAllCandidateInfo('Toronto', 'Javascript', 3);
  return devs;
}

logTorontoJsDevsWithThreeYearsExp();

Script Lab & JavaScript API

We can use JavaScript in Excel!

We can also make and style HTML pages.

An example code populating cells:

await Excel.run(async (context) => {
  const sheet = context-workbook.worksheets.getActiveWorksheet();

  const range = sheet.getRange('A1');

  range.values = [[]];
  range.format.autofitColumnes();

  await context.sync();
});

Making an API call in Script:

async function getRandomDadJoke() {
  const response = await fetch(api_url);

  const data = await response.json();

  return data.joke;
}

• It's the same as the usual JS code we write!

You can export and import "public gists" on GitHub.

• Try importing this snippet Andy wrote in the demo.

  Useful Excel table propperties

This is a good reference for working with tables using JS in Excel: https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-tables

Some useful properties and methods:

table.name

table.getHeaderRowRange() - reference

table.rows.add - reference

table.getDataBodyRange() - refereence

---

Excel naming rules

Read more about it here: https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/#excel-name-rules