LegiScan Education Finance Bill Tracker

An automated R pipeline for tracking legislation across U.S. states. This tool receives legislative bill data from Google Drive (updated weekly from LegiScan's API), applies filtering, and produces an Excel workbook for human review and decision tracking.

📁 Note: Scripts 00-04 (LegiScan API data acquisition) have been archived. Start by configuring config/filter_settings.R, then run Script 01 to download pre-processed data from Google Drive.

See archived_scripts/README.md for complete documentation on archived scripts, API setup, and the full data pipeline.

Quick Start {#quick-start}

1. Install required packages

source("config/pkg_dependencies.R")

2. Run the pipeline

source("R/01_download_and_filter.R")          # Download from Google Drive & filter
source("R/02a_create_tracked_workbook.R")     # Generate Excel workbook

3. Track bills

Open Master_Pull_List.xlsx
Review the Needs_Review sheet
Set Track = TRUE or FALSE for each bill
Save and run source("02b_sync_decisions.R")

Workflow Overview {#workflow-overview}

┌─────────────────────────────────────────────────────────────────────────┐
│                    DATA ACQUISITION (Run by Biko weekly)                 │
├─────────────────────────────────────────────────────────────────────────┤
│  Scripts 00-04 (archived) → all_states_combined.csv → Google Drive      │
└─────────────────────────────────────────────────────────────────────────┘
                                    ↓
┌─────────────────────────────────────────────────────────────────────────┐
│                  ★ START HERE: config/filter_settings.R                 │
├─────────────────────────────────────────────────────────────────────────┤
│  Determine and set your variables of interest                           │
│    • TARGET_STATES — which states to include                            │
│    • KEYWORDS — terms to match in title, description, committee         │
│    • STUCK_THRESHOLD_DAYS — days of inactivity before flagging          │
│    • DEAD_KEYWORDS — regex for detecting dead bills                     │
└─────────────────────────────────────────────────────────────────────────┘
                                    ↓
┌─────────────────────────────────────────────────────────────────────────┐
│                         DOWNLOAD & FILTER                               │
├─────────────────────────────────────────────────────────────────────────┤
│  01_download_and_filter.R                                               │
│    • Download gdrive_all_states_combined.csv from Google Drive          │
│    • Apply keyword and state filters from settings above                │
│    • Output: filtered_bills.csv                                         │
└─────────────────────────────────────────────────────────────────────────┘
                                    ↓
┌─────────────────────────────────────────────────────────────────────────┐
│                         EXCEL WORKBOOK CREATION                         │
├─────────────────────────────────────────────────────────────────────────┤
│  02a_create_tracked_workbook.R   →  Master_Pull_List.xlsx               │
│    • Needs_Review sheet (new/changed bills)                             │
│    • Tracked sheet (bills you're tracking)                              │
│    • Not_Tracked sheet (bills you've ignored)                           │
│    • Archive sheet (dead/stuck bills)                                   │
└─────────────────────────────────────────────────────────────────────────┘
                                    ↓
┌─────────────────────────────────────────────────────────────────────────┐
│                         USER DECISION LOOP                               │
├─────────────────────────────────────────────────────────────────────────┤
│  User reviews Excel → Sets Track = TRUE/FALSE → Saves file              │
│  02b_sync_decisions.R  →  tracking_decisions.csv                        │
│  Re-run 02a to refresh workbook                                         │
└─────────────────────────────────────────────────────────────────────────┘

Script Summary

Script	Purpose	Output
`01`	Download from Google Drive & filter according to keywords	`filtered_bills.csv`
`02a`	Create Excel workbook for bill tracking	`Master_Pull_List.xlsx`
`02b`	Sync user decisions on which bills to track	`tracking_decisions.csv`
`03`	Analyze dead/stuck bills (optional)	`filtered_bills_with_status.csv`

Detailed Script Documentation {#detailed-script-documentation}

Script 01: `R/01_download_and_filter.R`

Purpose: Download combined data from Google Drive and filter to relevant bills.

What it does:

Downloads gdrive_all_states_combined.csv from Google Drive (updated weekly by colleague)
Applies two-level filtering:

Keyword Filter (matches in title, description, or committee): - "education" - "teacher" - "school" - "property tax"

State Filter (17 target states): - AL, AZ, AR, CA, CO, DE, GA, IN, MD, MI, MS, NM, NY, NC, PA, TN, VA

Input: Google Drive file (ID set via GDRIVE_FILE_ID in config/filter_settings.R)

Output: filtered_bills.csv (~5 MB)

Libraries: googledrive, tidyverse

Script 02a: `R/02a_create_tracked_workbook.R`

Purpose: Generate a multi-sheet Excel workbook for bill tracking with change detection.

What it does: - Loads filtered_bills.csv and tracking_decisions.csv - Detects changes since last run: - is_new - Bills not in previous tracking data - status_changed - Status date has changed - action_changed - Bill action has changed - needs_review - Any of the above are true - Identifies dead/stuck bills: - is_dead - Contains keywords like "died in committee", "vetoed", etc. - is_stuck - No action in 45+ days (and not dead) - Creates formatted Excel workbook with conditional formatting

Excel Sheets Created:

Sheet	Contents	Styling
`Needs_Review`	Bills requiring user decision	Blue = new, Yellow = changed
`Tracked`	Bills marked Track=TRUE	Standard
`Not_Tracked`	Bills marked Track=FALSE	Standard
`Archive`	Dead or stuck bills	Gray = dead, Orange = stuck

Features: - Frozen header rows - TRUE/FALSE data validation for Track column - Color-coded highlighting for new/changed bills - Days since last action calculated

Configuration: STUCK_THRESHOLD_DAYS and DEAD_KEYWORDS are set in config/filter_settings.R. CURRENT_DATE is set locally via Sys.Date().

Output: - Master_Pull_List.xlsx (4-sheet workbook) - Updated tracking_decisions.csv

Libraries: tidyverse, openxlsx2

Script 02b: `R/02b_sync_decisions.R`

Purpose: Sync user's Track decisions from Excel back to the tracking CSV.

What it does: - Reads Master_Pull_List.xlsx (specifically Needs_Review sheet) - Extracts user's Track column values (TRUE/FALSE) - Updates tracking_decisions.csv with: - Track decision - Decision date - Previous status/action for change detection

Migration Mode (Optional): For importing from legacy Excel format with separate sheets: - Imports from Tracked_Bills → Track = TRUE - Imports from Do_Not_Track → Track = FALSE

Output: Updated tracking_decisions.csv

Libraries: tidyverse, openxlsx2

Script 03: `R/03_analyze_bills.R`

Purpose: Analyze and categorize bills by legislative status.

What it does: - Reads filtered_bills.csv - Calculates days since last action - Categorizes each bill: - Dead - Contains keywords: "died in committee", "failed", "postponed", "killed", "vetoed" - Stuck - No action > 45 days and not dead - Active - All other bills

Note: This logic is now integrated into Script 02a, so this script is optional/supplementary.

Output: filtered_bills_with_status.csv

Libraries: tidyverse

Data Files Generated {#data-files-generated}

Your workflow (Scripts 01-03) generates these files:

File	Created By	Description
`gdrive_all_states_combined.csv`	Script 01	Downloaded from Google Drive (updated weekly by colleague)
`filtered_bills.csv`	Script 01	Filtered to target states + keywords
`tracking_decisions.csv`	Scripts 02a/02b	Your Track decisions
`Master_Pull_List.xlsx`	Script 02a	Excel workbook for tracking
`filtered_bills_with_status.csv`	Script 03	(Optional) Bills categorized by status

Note: For information about files generated by archived Scripts 00-04, see archived_scripts/README.md.

Repository Structure

legiscan_share/
├── config/
│   ├── pkg_dependencies.R        # Install/load required packages
│   └── filter_settings.R         # Target states, keywords, thresholds
├── R/
│   ├── 01_download_and_filter.R
│   ├── 02a_create_tracked_workbook.R
│   ├── 02b_sync_decisions.R
│   └── 03_analyze_bills.R
├── archived_scripts/
│   ├── 00_simplified_datasetlist_grab.R
│   ├── 01_simplified_get_LegDatasets.R
│   ├── 02_create_bills_folder.R
│   ├── 03_minimal_json_to_csv.R
│   ├── 04_combine_all_states.R
│   └── README.md
├── CLAUDE.md                      # Developer preferences and project context
└── README.md

Note:

Scripts 00-04 are archived (run weekly by colleague, not by you)
Active scripts (01-03) are in the R/ directory
Data files (CSVs, Excel files) are generated when you run Scripts 01-02a

User Workflow: Tracking Bills {#user-workflow-tracking-bills}

Initial Setup (First Time)

Install required packages: source("config/pkg_dependencies.R")
Set filters: Change keywords, target states, and stuck threshold in config/filter_settings.R
Run Script 01 to download and filter data: source("01_download_and_filter.R")
Run Script 02a to create workbook: source("02a_create_tracked_workbook.R")
Open Master_Pull_List.xlsx

Regular Workflow

1. REFRESH DATA
   └── Run Script 01: source("01_download_and_filter.R")
   └── Run Script 02a: source("02a_create_tracked_workbook.R")

2. REVIEW BILLS
   └── Open Master_Pull_List.xlsx
   └── Go to "Needs_Review" sheet
   └── Look for highlighted rows:
       • Blue = New bill
       • Yellow = Status or action changed

3. MAKE DECISIONS
   └── Set Track column to TRUE (want to track) or FALSE (ignore)
   └── Save the Excel file

4. SYNC DECISIONS
   └── Run Script 02b to save your decisions to tracking_decisions.csv

5. REFRESH WORKBOOK
   └── Run Script 02a again
   └── Your tracked bills appear in "Tracked" sheet
   └── Ignored bills appear in "Not_Tracked" sheet

6. REPEAT
   └── Run this workflow weekly after Biko updates Google Drive

Customization {#customization}

Default settings are in config/filter_settings.R:

GDRIVE_FILE_ID <- "1K1MJ7uB5aXvZLYcq4N8VwjSOFMDkisvd"  # Google Drive source file
TARGET_STATES <- c("CA", "NY", "TX")             # States to include
KEYWORDS <- c("keyword1", "keyword2")            # Keywords to match in title/description/committee
STUCK_THRESHOLD_DAYS <- 45                       # Days of inactivity before flagging as "Stuck"
DEAD_KEYWORDS <- "died in committee|fail|..."    # Regex pattern for dead bill detection
MIGRATION_MODE <- FALSE                          # Set TRUE to import from old Excel structure

Project-Specific Settings

To apply this tool to a specific project without modifying the defaults above, create config/project_settings.R and override any variables you need. This file is automatically sourced by filter_settings.R if it exists.

Recommended workflow:

Keep main as a clean, reusable template with generic defaults
Create a project branch (e.g., project/my-project) off main
Add config/project_settings.R on that branch with your project-specific states, keywords, etc.
Work on the project branch; when you improve the core pipeline on main, merge it in:

git checkout project/my-project
git merge main

Requirements {#requirements}

R Packages

install.packages(c(
  "googledrive", # Google Drive access
  "tidyverse",   # Data manipulation
  "openxlsx2"    # Excel file creation
))

Note: Packages httr2, jsonlite, and base64enc are only needed if running archived scripts (00-04).

System Requirements

R version 4.0 or higher
Google account with access to shared Google Drive file
~50 MB disk space for filtered data

License

This project is for educational and research purposes. LegiScan data is subject to their terms of service.

Acknowledgments

LegiScan for providing legislative data API
Built for tracking legislation across U.S. states

Navigation Menu

Repository navigation

About

Repository files navigation

LegiScan Education Finance Bill Tracker

Table of Contents

Quick Start {#quick-start}

1. Install required packages

2. Run the pipeline

3. Track bills

Workflow Overview {#workflow-overview}

Script Summary

Detailed Script Documentation {#detailed-script-documentation}

Script 01: `R/01_download_and_filter.R`

Script 02a: `R/02a_create_tracked_workbook.R`

Script 02b: `R/02b_sync_decisions.R`

Script 03: `R/03_analyze_bills.R`

Data Files Generated {#data-files-generated}

Repository Structure

User Workflow: Tracking Bills {#user-workflow-tracking-bills}

Initial Setup (First Time)

Regular Workflow

Customization {#customization}

Project-Specific Settings

Requirements {#requirements}

R Packages

System Requirements

License

Acknowledgments

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
R		R
archived_scripts		archived_scripts
config		config
.DS_Store		.DS_Store
CLAUDE.md		CLAUDE.md
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation

LegiScan Education Finance Bill Tracker

Table of Contents

Quick Start {#quick-start}

1. Install required packages

2. Run the pipeline

3. Track bills

Workflow Overview {#workflow-overview}

Script Summary

Detailed Script Documentation {#detailed-script-documentation}

Script 01: R/01_download_and_filter.R

Script 02a: R/02a_create_tracked_workbook.R

Script 02b: R/02b_sync_decisions.R

Script 03: R/03_analyze_bills.R

Data Files Generated {#data-files-generated}

Repository Structure

User Workflow: Tracking Bills {#user-workflow-tracking-bills}

Initial Setup (First Time)

Regular Workflow

Customization {#customization}

Project-Specific Settings

Requirements {#requirements}

R Packages

System Requirements

License

Acknowledgments

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

Script 01: `R/01_download_and_filter.R`

Script 02a: `R/02a_create_tracked_workbook.R`

Script 02b: `R/02b_sync_decisions.R`

Script 03: `R/03_analyze_bills.R`