Create Label Report in Google Spreadsheet

[kimberlytanyh]

Dependency

• Epic: Refactoring GHA - JavaScript files to use common key: value JSON #6454

Overview

We need to create a data set with newly added and deleted labels, so that we can add them to a dashboard.

Detail

In a recent meeting, we noticed that there were instances when volunteers would add their own labels or delete official labels from the website team repository (accidentally or without permission). We want to catch these early to prevent any resulting problems.

When this issue is completed, there will be a spreadsheet (as the source of truth) that will show labels that have not yet been reviewed and it will release the dependency on creating the dashboard for a report that product will use to fix labels.

Preparing to work on this issue

You will need to be added to the following resources:

• Hack for LA website team drive
• Access to Hack for LA's 1Password vaults
• Access to live-dashboard-automation repository to merge changes

Action Items

Part 1: Creating a new dataset with labels

• Use GitHub API to retrieve all existing labels in the website repository in a Jupyter Notebook
• Format these labels into a dataset
• Create an empty spreadsheet in the HackforLA.org website Google Drive (see Resource 5 for the link)
• Log into webadmin@hackforla.org (use Hack for LA's 1Password account)
  • Create a new project in the Google Cloud Console under the webadmin@hackforla.org account
    • Create a Service Account credential for the project (Refer to Resource 3)
    • Get json key (Refer to Resource 3)
    • Connect the service account email to the blank Google spreadsheet by adding it under Share (Refer to Resource 3)
• Send the labels to the blank Google sheet you created to act as a "source of truth" on all official labels currently in use

Part 2: Using the API to compare data sets, each with labels

We'll now repurpose the API call above to be used to detect new labels added or labels deleted from the website repository in the future (your code, once perfected, is to be added to the .py script currently in use for the dashboard in the live-dashboard-automation repository)

• Read in all official labels in use from the Google Sheet you created in Part 1 above using Google API
• Perform API call to retrieve all labels from the website repository (just copy the code from Part 1)
• Compare the labels pulled in by the API call in Part 2 to the list of labels from the Google spreadsheet from Part 1
• Create dataset with labels that are not in or missing from the Part 1 Google sheet's list of official labels
• Send the dataset to "Unreviewed Newly Added or Deleted Labels" sheet (tab) in the "GitHub Project Board Issues - Data Analysis" spreadsheet (Refer to Resource 5.I and Resource 1/3/4)
• Once you are sure everything is working well, add your code for Part 2 to the .py script in live-dashboard-automation repository (Test using the .ipynb file -Project Board Dashboard Script Editable.ipynb before adding code, pushing, and merging changes in the .py file to the main repository)
• Release the dependency on Create Landing Page for Anomaly Report in Looker Dashboard #5812
  • add links to whatever assets you created, into the resources section of the issue
  • move it into the new issue approval column
  • add the ready for prioritization label

Resources/Instructions

1. Editable version of Python script that handles cleaning and sending of data to Resource 5.I's "Unreviewed Newly Added or Deleted Labels" sheet (tab) (for testing)

2. Template for .env file to go with Resource 1 (You need to get your own GitHub token - See Resource 6 - and add it to a .env file along with other credentials to run Resource 1)(Note: You can directly add the credentials in Resource 1 but it would be inconvenient when you want to share your work later without revealing your confidential information)

3. Using Google API to Send and Read Data to Google Sheets: Update Google Sheets Using A Service Account With Google Sheets API In Python, How to Analyze Data in Google Sheets With Python: A Step-By-Step Guide

4. Repository controlling automation of script run to update Looker dashboard

5. Google Drive folder: Dashboard to put any additional resources created

   1. GitHub Project Board Issues - Data Analysis spreadsheet

6. Slides with instructions on how to create GitHub token (see Appendix)

7. Looker dashboard being worked on

8. Refer to issue Create Project Board Looker Dashboard for Different Roles  #4921 for information on Looker dashboard involved

9. How to use .env file: Just add the .env file to the same folder where your Jupyter Notebook is to use it.

[ExperimentsInHonesty]

@kimberlytanyh This information should either be available in a repository, or in 1password, or a combination of both.

Contact me (kimberly.tan.yh@gmail.com) for Jupyter Notebook with script and the key required to access the "GitHub Project Board Issues - Data Analysis" spreadsheet

[ExperimentsInHonesty]

We will discuss with Sophia the best way to make Jupiter Notebook available without revealing secrets.

[kimberlytanyh]

Hello @ExperimentsInHonesty, I just took a look at the issue again and made some edits. Both editable Jupyter notebooks and .env files have been added to the live-dashboard-automation repository and changes have been made accordingly to this issue. My contact information has been removed.

[ExperimentsInHonesty]

• Part 1 will not need to be created, instead this issue will get rewritten to use the file that is created in Epic: Refactoring GHA - JavaScript files to use common key: value JSON #6454 and it will get compared with this sheet Labels for Weekly label check, Official GitHub Labels tab and then produce the list of labels that are not on the offical list but are in the repo. It will then also flag any of those labels with being used by GHAs

[Samhitha444]

• All the links in this issue will need to be checked to see if we have access
• Epic: Refactoring GHA - JavaScript files to use common key: value JSON #6454
  • Have access, it's located in ____
  • public
• https://github.com/hackforla/live-dashboard-automation
  • Have access, it's located in ____
  • public
• https://console.cloud.google.com/cloud-resource-manager?authuser=4
  • Have access, it's located in ____
  • public
• https://docs.google.com/spreadsheets/d/1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM/edit#gid=1433865054
  • Need access
  • public
• https://github.com/hackforla/live-dashboard-automation
  • Need access
  • public
• Create Landing Page for Anomaly Report in Looker Dashboard #5812
  • Have access, it's located in ____
  • public
• https://github.com/hackforla/live-dashboard-automation/blob/main/Project%20Board%20Dashboard%20Script%20Editable.ipynb
  • Have access, it's located in ____
  • public
• https://docs.google.com/spreadsheets/d/1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM/edit#gid=1433865054
  • Need access
  • public
• https://github.com/hackforla/live-dashboard-automation/blob/main/.env
  • Have access, it's located in ____
  • public
• https://www.youtube.com/watch?v=sVURhxyc6jE&t=2s
  • Have access, it's located in ____
  • public
• https://www.datacamp.com/tutorial/how-to-analyze-data-in-google-sheets-with-python-a-step-by-step-guide
  • Have access, it's located in ____
  • public
• https://github.com/hackforla/live-dashboard-automation
  • Have access, it's located in ____
  • public
• https://drive.google.com/drive/folders/1OoCzO58nhcGtd4My9lvbPCAVNf_fK5hR
  • Need access
  • public
• https://docs.google.com/spreadsheets/d/1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM/edit?usp=sharing
  • Need access
  • public
• https://docs.google.com/presentation/d/1hZCNMZewl4L_so1ceGMOuP0mTbaBkUuwzAbBPgcGYfU/edit?usp=sharing
  • Have access, it's located in ____
  • public
• https://lookerstudio.google.com/reporting/6410c484-f38f-44ba-b4ed-c29a6b2a081b
  • Have access, it's located in ____
  • public
• Create Project Board Looker Dashboard for Different Roles  #4921
  • Have access, it's located in ____
  • public
• Epic: Refactoring GHA - JavaScript files to use common key: value JSON #6454
  • Have access, it's located in ____
  • public
• https://docs.google.com/spreadsheets/d/1-ltg0qMeZSgOnqrCU0nKUDQd1JOXTMWrNTK63VZjXdk/edit#gid=0
  • Need access
  • public