Replace Fiddler with native GAS + RichText for hyperlinks

[TobyHFerguson]

Motivation

The current architecture uses Fiddler (bmPreFiddler) for spreadsheet I/O and stores hyperlinks as HYPERLINK formulas. This creates significant complexity:

• HyperlinkUtils.js (~100+ lines) for formula parsing
• Formula storage in PropertiesService after each save (~50 lines)
• Formula overlay logic on data load
• String manipulation for =HYPERLINK("url", "text") construction

Key Insight: RichText objects provide native hyperlink support in GAS, making formula complexity unnecessary. However, Fiddler doesn't support RichText, so replacing formulas with RichText requires removing Fiddler.

Net Benefit: Remove ~100-150 lines of code + external dependency

Current Implementation

With Fiddler + Formulas:

// Reading (requires formula overlay from PropertiesService)
const formula = row.Route; // "=HYPERLINK(\"url\", \"text\")"
const { url, text } = HyperlinkUtils.parseHyperlinkFormula(formula);

// Writing (requires formula construction + storage)
row.Route = `=HYPERLINK("${url}", "${text}")`;
// Later: _storeFormulas() saves to PropertiesService

Proposed Implementation

With Native GAS + RichText:

// Reading
const richText = range.getRichTextValue();
const url = richText.getLinkUrl();
const text = richText.getText();

// Writing
const richText = SpreadsheetApp.newRichTextValue()
    .setText(text)
    .setLinkUrl(url)
    .build();
range.setRichTextValue(richText);

Implementation Plan

Phase 1: Add Native GAS Conversion Logic

File: src/ScheduleAdapter.js

Replace Fiddler calls with native GAS:

_loadData() {
    const sheet = this._getSheet();
    const lastRow = sheet.getLastRow();
    const lastCol = sheet.getLastColumn();
    
    // Get headers
    const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
    
    // Get values
    const values = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues();
    
    // Get RichText for Route and Ride columns
    const routeColIndex = headers.indexOf('Route');
    const rideColIndex = headers.indexOf('Ride');
    const richTextValues = sheet.getRange(2, 1, lastRow - 1, lastCol).getRichTextValues();
    
    // Convert to objects
    this._data = values.map((row, i) => {
        const obj = { _rowNum: i + 2 };
        headers.forEach((header, j) => {
            if (j === routeColIndex || j === rideColIndex) {
                // Extract URL from RichText
                const richText = richTextValues[i][j];
                obj[header] = richText ? richText.getLinkUrl() || row[j] : row[j];
            } else {
                obj[header] = row[j];
            }
        });
        return obj;
    });
}

Phase 2: Update Save Logic for RichText

File: src/ScheduleAdapter.js

Replace formula preservation with RichText writes:

save() {
    const sheet = this._getSheet();
    const dirtyRows = this._data.filter(row => row._dirtyFields?.size > 0);
    
    dirtyRows.forEach(row => {
        row._dirtyFields.forEach(fieldName => {
            const col = this._headers.indexOf(fieldName) + 1;
            const cell = sheet.getRange(row._rowNum, col);
            
            // Special handling for Route/Ride columns
            if (fieldName === 'Route' || fieldName === 'Ride') {
                const { text, url } = this._parseRouteOrRide(row[fieldName]);
                const richText = SpreadsheetApp.newRichTextValue()
                    .setText(text)
                    .setLinkUrl(url)
                    .build();
                cell.setRichTextValue(richText);
            } else if (typeof row[fieldName] === 'string' && row[fieldName].startsWith('=')) {
                cell.setFormula(row[fieldName]);
            } else {
                cell.setValue(row[fieldName]);
            }
        });
        row._dirtyFields.clear();
    });
    
    SpreadsheetApp.flush();
}

Phase 3: Remove Formula Complexity

Files to Delete/Modify:

• ✅ Delete: src/HyperlinkUtils.js (entire file, ~100+ lines)
• ✅ Delete: test/__tests__/HyperlinkUtils.test.js
• ✅ Remove from src/ScheduleAdapter.js:
  • _storeFormulas() method
  • _overlayFormulas() method
  • PropertiesService formula storage logic
• ✅ Remove from src/Exports.js: HyperlinkUtils export
• ✅ Remove from src/gas-globals.d.ts: HyperlinkUtils declaration
• ✅ Update package.json: Remove bmPreFiddler dependency

Phase 4: Update Row Class

File: src/RowCore.js

Simplify Route/Ride accessors (no formula parsing needed):

get RouteURL() {
    // Before: return HyperlinkUtils.parseHyperlinkFormula(this._data.Route).url;
    // After: return this._data.Route; // Already the URL from RichText
}

setRouteLink(text, url) {
    // Before: this._data.Route = `=HYPERLINK("${url}", "${text}")`;
    // After: Store both for ScheduleAdapter to create RichText
    this._data.Route = { text, url };
    this._markDirty('Route');
}

Testing Requirements

Unit Tests

• ✅ Native GAS array ↔ object conversion
• ✅ RichText URL extraction
• ✅ RichText creation and writing
• ✅ Row.RouteURL getter returns correct URL
• ✅ Row.setRouteLink marks field dirty

Integration Tests

Create test/__tests__/ScheduleAdapter.richtext.test.js:

• Load data with RichText hyperlinks
• Extract URLs correctly
• Save RichText hyperlinks
• Verify spreadsheet contains correct RichText

Manual Testing in GAS

1. Deploy to dev environment
2. Open spreadsheet with existing HYPERLINK formulas
3. Run menu command that reads Route/Ride URLs
4. Verify URLs extracted correctly
5. Run menu command that writes Route/Ride links
6. Verify spreadsheet shows RichText hyperlinks (not formulas)
7. Click links to verify they work

Benefits

✅ Remove ~100-150 lines of code
✅ Remove external dependency (Fiddler/bmPreFiddler)
✅ Simpler mental model (RichText vs formula strings)
✅ Native GAS feature (better long-term support)
✅ No PropertiesService storage (one less state management concern)
✅ Better performance (no formula parsing overhead)

Migration Notes

Backward Compatibility:

• Existing spreadsheets with HYPERLINK formulas will need one-time conversion
• Create migration script: scripts/migrate-formulas-to-richtext.js
• Migration reads formulas, converts to RichText, writes back

Rollout Strategy:

1. Deploy code with both formula + RichText support
2. Run migration script on production spreadsheet
3. Verify all links work
4. Remove formula support code

Related Issues

• Addresses complexity mentioned in ScheduleAdapter architecture discussions
• Enables further simplification of Row abstraction
• Reduces technical debt from formula preservation logic

[TobyHFerguson]

Migration Utility Required

Important: We need a one-shot migration utility to convert existing HYPERLINK formulas to RichText BEFORE deploying the new code. This avoids the complexity of dual support (handling both formulas and RichText).

Migration Utility: scripts/migrate-formulas-to-richtext.js

Create a standalone GAS function that:

/**
 * One-time migration utility: Convert all HYPERLINK formulas to RichText
 * Run this ONCE before deploying the new ScheduleAdapter code
 * 
 * Usage: 
 * 1. Deploy this script to GAS
 * 2. Run migrateHyperlinksToRichText() from Script Editor
 * 3. Verify all Route/Ride links work in spreadsheet
 * 4. Deploy new ScheduleAdapter code
 */
function migrateHyperlinksToRichText() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Consolidated Rides');
    const lastRow = sheet.getLastRow();
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // Find Route and Ride columns
    const routeColIndex = headers.indexOf('Route') + 1;
    const rideColIndex = headers.indexOf('Ride') + 1;
    
    if (routeColIndex === 0 || rideColIndex === 0) {
        throw new Error('Could not find Route or Ride columns');
    }
    
    // Process Route column
    console.log('Converting Route column formulas to RichText...');
    convertColumnToRichText(sheet, routeColIndex, 2, lastRow);
    
    // Process Ride column
    console.log('Converting Ride column formulas to RichText...');
    convertColumnToRichText(sheet, rideColIndex, 2, lastRow);
    
    console.log('Migration complete! Verify links work, then deploy new code.');
}

function convertColumnToRichText(sheet, colIndex, startRow, endRow) {
    const numRows = endRow - startRow + 1;
    const formulas = sheet.getRange(startRow, colIndex, numRows, 1).getFormulas();
    const values = sheet.getRange(startRow, colIndex, numRows, 1).getValues();
    
    let converted = 0;
    
    formulas.forEach((formulaRow, i) => {
        const formula = formulaRow[0];
        const rowNum = startRow + i;
        
        // Skip empty cells
        if (!formula && !values[i][0]) {
            return;
        }
        
        // Check if it's a HYPERLINK formula
        const match = formula.match(/^=HYPERLINK\("([^"]+)",\s*"([^"]+)"\)/);
        
        if (match) {
            const url = match[1];
            const text = match[2];
            
            // Create RichText and write to cell
            const richText = SpreadsheetApp.newRichTextValue()
                .setText(text)
                .setLinkUrl(url)
                .build();
            
            sheet.getRange(rowNum, colIndex).setRichTextValue(richText);
            converted++;
        } else if (formula.startsWith('=HYPERLINK')) {
            console.warn(`Row ${rowNum}: Could not parse HYPERLINK formula: ${formula}`);
        }
    });
    
    console.log(`Converted ${converted} formulas in column ${colIndex}`);
}

Revised Rollout Strategy

1. Before Code Changes:

   • Create migrateHyperlinksToRichText.js utility
   • Test on copy of production spreadsheet
   • Verify all links work after conversion

2. Migration Day:

   • Run migrateHyperlinksToRichText() on production spreadsheet
   • Verify all Route/Ride links work
   • Check a few rows manually by clicking links

3. Deploy New Code:

   • Deploy ScheduleAdapter with RichText-only support (no formula handling)
   • Test read/write operations
   • Verify dirty tracking works with RichText

4. Cleanup:

   • Remove HyperlinkUtils.js and related code
   • Remove Fiddler dependency
   • Delete migration utility (no longer needed)

Benefits of One-Shot Migration

✅ Simpler code: No dual support for formulas + RichText
✅ Faster deployment: Convert once, deploy clean code
✅ Easier testing: Only one code path to test
✅ Clear cutover: Migration is a discrete event, not gradual

Rollback Plan

If migration fails:

1. Spreadsheet still has formulas (migration didn't run or failed)
2. Don't deploy new code
3. Fix migration script and retry

If new code has issues:

1. Revert to previous GAS deployment
2. Spreadsheet already has RichText (that's fine, formulas can be recreated if needed)
3. Formula-based code can read RichText display values (degraded but functional)

[TobyHFerguson]

Lets extend this to the remaining sheets - allowing for text values, or RichText values.