Create Label Report in Google Spreadsheet
- Dominant language
- JavaScript
- Stars
- 363
- Forks
- 872
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 18
Description
### Dependency
- #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](https://github.com/hackforla/live-dashboard-automation) 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](https://console.cloud.google.com/cloud-resource-manager?authuser=4) 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"](https://docs.google.com/spreadsheets/d/1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM/edit#gid=1433865054) 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](https://github.com/hackforla/live-dashboard-automation) (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 #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](https://github.com/hackforla/live-dashboard-automation/blob/main/Project%20Board%20Dashboard%20Script%20Editable.ipynb) that handles cleaning and sending of data to Resource 5.I's ["Unreviewed Newly Added or Deleted Labels"](https://docs.google.com/spreadsheets/d/1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM/edit#gid=1433865054) sheet (tab) (for testing)
1. [Template for .env file](https://github.com/hackforla/live-dashboard-automation/blob/main/.env) 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)
1. Using Google API to Send and Read Data to Google Sheets: [Update Google Sheets Using A Service Account With Google Sheets API In Python](https://www.youtube.com/watch?v=sVURhxyc6jE&t=2s), [How to Analyze Data in Google Sheets With Python: A Step-By-Step Guide](https://www.datacamp.com/tutorial/how-to-analyze-data-in-google-sheets-with-python-a-step-by-step-guide)
1. [Repository controlling automation of script run to update Looker dashboard](https://github.com/hackforla/live-dashboard-automation)
1. [Google Drive folder: Dashboard](https://drive.google.com/drive/folders/1OoCzO58nhcGtd4My9lvbPCAVNf_fK5hR) to put any additional resources created
1. [GitHub Project Board Issues - Data Analysis](https://docs.google.com/spreadsheets/d/1aJ0yHkXYMWTtMz6eEeolTLmAQOBc2DyptmR5SAmUrjM/edit?usp=sharing) spreadsheet
1. [Slides with instructions on how to create GitHub token](https://docs.google.com/presentation/d/1hZCNMZewl4L_so1ceGMOuP0mTbaBkUuwzAbBPgcGYfU/edit?usp=sharing) (see Appendix)
1. [Looker dashboard being worked on](https://lookerstudio.google.com/reporting/6410c484-f38f-44ba-b4ed-c29a6b2a081b)
1. Refer to issue #4921 for information on Looker dashboard involved
1. How to use .env file: Just add the .env file to the same folder where your Jupyter Notebook is to use it.
Contributor guide
Assessment
This issue has not been assessed yet.