QexEdit - 18F/charlie GitHub Wiki

Updating the Q Expander

image

Introduction

The Q-Expander is a bot that will explain what each letter of a GSA organization code is. By iterating over each letter and looking up what it represents, it can tell you what department and office any employee of GSA is located in.

(Why the Q? Codes within TTS all start with Q, and the bot started with that. Eventually a list was found with every organization within the GSA and it was added.)

About the Org Code List

The list of org codes that GSA has is published on GSA's D2D, a "a data analytics portal that is used to share business insights, including visualizations, reports and datasets." Every month they upload a new copy of the GSA org codes as an Excel spreadsheet on the same page. Presently there seems to be no way to easily automate taking data from this page into Charlie.

(@geekygirlsarah has reached out to the person that publishes it but has not heard back yet.)

Process to Update the Bot

  1. Head to https://d2d.gsa.gov/dataset/gsa-organization
  2. If you haven't made an account, do that. Otherwise, login.
  3. Download the spreadsheet at the bottom of the page.
  4. Upload the spreadsheet to your Google Drive (or open Excel if you happen to have it).
  5. Don't forget to branch off of main to start your work!
  6. Highlight the two relevant columns of the spreadsheet, which should be labeled like Department ID and Department Description. Press Cmd+C/Ctrl+C, and paste into Visual Studio Code.
  7. In VS Code, press Cmd+F/Ctrl+F to bring up the find box. Click on the .* icon to enable RegEx search mode, and click the arrow that's pointing right to expand the box to include the Replace box.
  8. Search for \t and replace with ,", then click the Replace All button. (This replaces all tabs with a comma and quote.)
  9. Search for $ and replace with ", then click the Replace All button. (This finds the end of every line and adds in a quote.)
  10. Remove the header line (if you pasted it in).
  11. Go into /config/q-expand.csv and remove all lines that exist (aside from the header on line 1) and paste your modified org code lines.
  12. A git diff should show you any changes you've made.
  13. This is where some self-descretion comes in. GSA tends to label a lot of units numerically (i.e. "Unit 1", "Unit 2", etc.) where TTS might label groups with specific names, products, cohorts, etc. If the code starts with a Q, you may wish to update it to reflect our internal descriptions of the group.
  14. Save, commit, push, PR!