In Every Google Apps Script project on Github visualized I introduced Vizzy  which uses GitHub data as its source. That same data package is available publicly in case you want to build something on top of it. There’s also an Apps Script library to avoid needing to know the details of how its compressed and organized.  This article will show you how to build something in Apps Script from that data.

Motivation

I have a shared spreadsheet that is automatically updated by Gasgit with all my shared libraries, but since we have details on all publicly accessed libraries now on Vizzy, it’s straightforward to automatically maintain a spreadsheet using the same data.  Unfortunately we don’t have the repo and owner for every library user in use as

  • The name of the library might not be the same as the github repo
  • The library might not be on github
  • The github API might not want to return the repo because it considers it to be not-relevant (too old for example), and is therefore missing from Vizzy (as discussed in Searching and cataloging Apps Script projects on Github)

However, we do have the id of every library accessed by every repo we know, as per this small sample below

id versions label userSymbols repo repoLink owner
1XQhNeKBi0ONR7_0iVM94DlQzi1Hwh8wvB_zCJxrnyiphMY_3AjudC6Qi 11 Ai Ai
1dLXVLsswT5OXeq7-hnVNq4P6oPveFXKyxfgsw4n1n6EgQhj89G30rQy7 1 alertbotoriginv3 alertbotoriginv3
1r9wNWbta3ebuYL4ENAdIp4UYKmyNiWf1AqsXYzfXduRHhTZEeTxS9MhZ 23 ArrayLib ArrayLib
1R-rxUHuQU0ez95FlZz9fxqifbYFszHnukqzv_3Uk5zM3eG4eRBrh_E1M 18,22 Assert Assert Assert https://github.com/andrewroberts/Assert Andrew Roberts
1KaXybVDE2XE8tnenIv-_bHbl6zRX70gSI3RXVpYpPTSV-9Y_rXt-RYLd 8 assertionManager
assertionManager
1wGm-zBaLhLEEX51y2JcPSEur9UfSkAbHTtb8qDKOaSAKUOBzW0vRs_sG 10 badgeManager badgeManager
1CrnW_mVjjZRVjQuC1HTx9gc1HQI8Rqn-qCt17jYel3j6qXIpfysrGFV0 3 badges badges
1AyoU6-xyXP4tCSr3ABYzW9IIlWduKnVCVwj1Sd8T-tqDC3LtKbDyYsWD 3 badgesFromSheet
badgesFromSheet
1HLv6tWz0oXFOJHerBTP8HsNmhpRqssijJatC92bv9Ym6HSN69_UuzcDk 4 BatchRequest BatchRequest
1kzzYZhgODZluRmjsjh7t3O-ftmFAF9h1jaRHU0rOdf9irF0VUiJW33Kt 13,19 BBLog BBLog BBLog https://github.com/andrewroberts/BBLog Andrew Roberts
1DSyxam1ceq72bMHsE6aOVeOl94X78WCwiYPytKi7chlg4x5GqiNXSw0l 27 BetterLog BetterLog
1hMJszJGSUVZDB3vmsWrUZfRhY1UWbhS0SQ6Lzl06gm1zhBF3ioTM7mpJ 81,63,69,72,62 BkperApp BkperApp
11TvZM3OIzemIFWu1rvO7xacg60q6FwSsdjh7cg0p-G78oQxOSteQi-uI 7,8 blister blister
1QPQom9mT_xqHz7Ge-scZlHVN5blvqqkKZdwoF3cUqagK6B-yesym-PKM 10 blisterAPI blisterAPI
1gDyDuW88-Y8xc0SFRO3fzVYm5RCaYghEnzlsIAnVUA9mmof9-0Q6bY_z 1 blisterMaintain blisterMaintain
1F0W2N-FOBzm3xhbDJi-XPeki1DX8PDPuh3MlBs-rpet95aWfB264-7Ve 3 blisterTest blisterTest
1uPqFKHCtWmI0OK7JCzewoG3tsozrkyme8FIJzZv77sSycnGOaPcnpei- 1 blogDetails blogDetails
13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU 4,3 bmFiddler bmFiddler bmFiddler https://github.com/brucemcpherson/bmFiddler
bruce mcpherson
13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR 5 bmPreFiddler bmPreFiddler bmPreFiddler https://github.com/brucemcpherson/bmPreFiddler
bruce mcpherson
1J_Oci2WG7wQ4KffCg8XOs-HID9gaIjEcNFlidE_7gwiJnrgQw24ozKct 2 bmQottle bmQottle bmQottle https://github.com/brucemcpherson/bmQottle
bruce mcpherson
1dajqLysdKo8IoqddtEaGhtUUlSbtSQ1Agi2K5cXSUm0DxXfLYouSO9yD 10 bmRottler bmRottler bmRottler https://github.com/brucemcpherson/bmRottler
bruce mcpherson
17B4lbAwYGooIpK7i06ukNYZ1AO82cykhMkxTXpKoa3eLLYQuuMvd1_q3 1 bucket bucket
1U6j9t_3ONTbhTCvhjwANMcEXeHXr4shgzTG0ZrRnDYLcFl3_IH2b2eAY 15,19,18,17,16,13 cCacheHandler cCacheHandler cCacheHandler https://github.com/brucemcpherson/cCacheHandler
bruce mcpherson

How to build this sample

We’ll use 2 libraries

  • bmPreFiddler – to handle spreadsheet interaction – for details see Handly helper for fiddler
  • bmVizzyCache – to access the GitHub catalogued data

Creating the sheet

For simplicity we’ll use fiddler to get and/or create a target sheet

  // use this to interact with the sheet
  const fiddler = bmPreFiddler.PreFiddler().getFiddler({
    id: '1DlKpVVYCrCPNfRbGsz6N_K3oPTgdC9gQIKi0aNb42uI',
    sheetName: 'vizzyLibraries',
    createIfMissing: true
  })
create sheet

Getting catalog data

Note that to avoid the library needing to have any Apps script dependencies itself, you need to pass over how to fetch data. for Apps Script, it is of course UrlFetchApp.fetch.

For details of the structure of what comes back from the catalogued data, see the classes in https://github.com/brucemcpherson/gitvizzy

  // get the data
  const { mf, gd } = bmVizzyCache.VizzyCache.fetch(UrlFetchApp.fetch)
  const repos = gd.items('repos');
  const owners = gd.items('owners');
get catalog data from cache

Organize and dump the library sheet

  // dump the libraries
  const data = Array.from(mf.maps.libraries.values()).sort(compare)
    
    // turn the versions in use into a list
    .map(f => {
      const values = Array.from(f.versions.values());

      // remove the dups from all used userSymbols (it's possible a custom user symbol is in use by some)
      const userSymbols = new Set(values.map(g=>g.userSymbol))

      return {
        ...f,
        versions: values.map(v=>v.version).join(","),
        userSymbols: Array.from(userSymbols)
      }

    })

    // try to find if we know this library on github by one of its userSymbols
    // pretty poor match here probably either because the repo is not called the same thing as the usersymbol, or it's just not found on github
    .map(f=> {
   
      const repo = repos.find(g=>f.userSymbols.indexOf(g.fields.name) !== -1)
      const owner = repo && owners.find(g=>g.fields.id===repo.fields.ownerId)
     
      return {
        ...f,
        repo: repo && repo.fields.name,
        repoLink: repo && repo.fields.html_url,
        owner: owner && owner.fields.name
      }
    })


  fiddler.setData(data).dumpValues()
extracting the library info

sort by library name

and finally a small helper to sort the data

  // ignore case compare
  const compare = (a,b) => {
    const alab = a.label.toLowerCase();
    const blab = b.label.toLowerCase();
    return alab === blab ? 0 : (alab > blab ? 1 : -1)
  }
compare for sorting

Links and references

  • gitvizzy on github, the writeup, and live
  • scrgit on github, the writeup – this maintains the cache
  • code for this example on github, and on script.google.com
  • bmVizzyCache library on github,library id: 1SohIo0pSlXSt2SKNf3iX2ZcbzE_N0F4BocnKpGuaXgyxx1X6s1nDhjZK
  • bmPreFiddler library on github, library id: 13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR

More

If you want to build something, contact me if you need any help in consuming the catalog data.