DOCS: Document Google Sheets update process with enhanced metadata

[jt14den]

Summary

We need documentation and tooling to safely update the canonical Google Sheets inventory with enhanced metadata generated by our automation scripts, without losing any formatting, colors, or conditional formatting.

Context

We have several automation scripts that generate enhanced metadata:

• scripts/scrape-metadata.js - Scrapes author, license, dates from lesson URLs
• scripts/standardize-languages.js - Standardizes language codes (IETF)
• scripts/estimate-time-required.js - Estimates lesson duration (ISO 8601)
• scripts/generate-slugs.js - Creates URL-safe identifiers
• scripts/merge-enhanced-metadata.js - Merges all enhanced data into single CSV

The challenge: How do we update the canonical Google Sheets with this enhanced data without losing:

• Cell formatting and colors
• Conditional formatting rules
• Data validation
• Comments and notes
• Formulas in other columns

Solution Created

Two files have been added to address this:

1. scripts/google-apps-script-updater.gs - Google Apps Script that provides a one-click column updater

   • Matches rows by lesson name
   • Updates only specified enhanced columns
   • Preserves ALL formatting
   • Shows update summary

2. scripts/UPDATING_GOOGLE_SHEETS.md - Comprehensive documentation with:

   • Method 1: Google Apps Script (recommended)
   • Method 2: Manual VLOOKUP approach
   • Step-by-step instructions
   • Troubleshooting guide
   • Data format explanations

Tasks

• Review the documentation for clarity and completeness
• Test the Google Apps Script updater with the actual spreadsheet
• Verify column mappings match current sheet structure
• Document any issues or edge cases discovered
• Update documentation based on testing feedback
• Create a video walkthrough (optional but helpful for future team members)

Columns Updated

The script updates these columns:

• author, license, contributor
• dateCreated, dateModified, datePublished
• inLanguage (IETF codes)
• timeRequired (ISO 8601 duration)
• identifier (slugs)
• abstract, about, accessibilitySummary
• mentions, recordedAt, version

Files

• scripts/google-apps-script-updater.gs
• scripts/UPDATING_GOOGLE_SHEETS.md
• Generated CSV: scripts/output/MERGED-enhanced-metadata-YYYY-MM-DD.csv

Priority

Medium - This is important for maintaining data quality but not blocking other work. The scripts already generate the enhanced data; this just makes it easier to apply updates to the canonical source.

Labels

documentation, student-ready, phase-2-enhancement, good first issue

[P-Ved]

Hi! I’d like to take ownership of this issue.

My plan is to review the documentation for clarity, test the Google Apps Script updater against the live sheet, verify column mappings, and document any edge cases or fixes needed. I’ll also update the docs based on findings and, if time allows, create a short walkthrough for future contributors.

Please assign this issue to me — I’ll start testing and report back with results and any improvements.

[P-Ved]

i'd like to take a leadership of this issue, please assign me this issue