chapter-organizer/tools/GoogleSheetsScheduleImport/GoogleSheetGridReader.cs

using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Color = Google.Apis.Sheets.v4.Data.Color;

namespace GoogleSheetsScheduleImport;

/// <summary>
/// Fetches raw grid data via Sheets API (public API key).
/// </summary>
public sealed class GoogleSheetGridReader
{
    private readonly SheetsService _service;

    public GoogleSheetGridReader(string apiKey)
    {
        if (string.IsNullOrWhiteSpace(apiKey))
            throw new ArgumentException("API key is required for Google Sheets access.", nameof(apiKey));
        _service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer
        {
            ApiKey = apiKey,
            ApplicationName = "TSA GoogleSheetsScheduleImport"
        });
    }

    public Spreadsheet FetchSpreadsheet(string spreadsheetId)
    {
        var req = _service.Spreadsheets.Get(spreadsheetId);
        req.IncludeGridData = true;
        return req.Execute();
    }

    public static GridSheetModel BuildModel(Sheet sheet)
    {
        var title = sheet.Properties?.Title ?? "(untitled)";
        var grid = sheet.Data?.FirstOrDefault();
        if (grid?.RowData == null || grid.RowData.Count == 0)
        {
            return new GridSheetModel
            {
                SheetTitle = title,
                Values = Array.Empty<string?[]>(),
                BackgroundKeys = Array.Empty<string?[]>()
            };
        }

        var rowCount = grid.RowData.Count;
        var colCount = grid.RowData.Max(r => r.Values?.Count ?? 0);

        var values = new string?[rowCount][];
        var bg = new string?[rowCount][];

        for (var r = 0; r < rowCount; r++)
        {
            values[r] = new string?[colCount];
            bg[r] = new string?[colCount];
            var row = grid.RowData[r];
            for (var c = 0; c < colCount; c++)
            {
                string? text = null;
                string? hex = null;
                if (row.Values != null && c < row.Values.Count)
                {
                    var cell = row.Values[c];
                    text = string.IsNullOrWhiteSpace(cell.FormattedValue)
                        ? cell.EffectiveValue?.StringValue
                        : cell.FormattedValue;
                    if (!string.IsNullOrEmpty(text))
                        text = TextNormalization.ForSheetCell(text);
                    var color = cell.UserEnteredFormat?.BackgroundColor
                                ?? cell.EffectiveFormat?.BackgroundColor;
                    hex = ColorToHexKey(color);
                }

                values[r][c] = string.IsNullOrWhiteSpace(text) ? null : text;
                bg[r][c] = hex;
            }
        }

        ApplyMerges(sheet.Merges, values, bg);
        NormalizeAllValueCells(values);

        return new GridSheetModel
        {
            SheetTitle = title,
            Values = values,
            BackgroundKeys = bg
        };
    }

    private static void ApplyMerges(IList<GridRange>? merges, string?[][] values, string?[][] bg)
    {
        if (merges == null || merges.Count == 0)
            return;

        foreach (var range in merges)
        {
            var r0 = range.StartRowIndex ?? 0;
            var r1 = range.EndRowIndex ?? r0;
            var c0 = range.StartColumnIndex ?? 0;
            var c1 = range.EndColumnIndex ?? c0;
            if (r1 <= r0 || c1 <= c0)
                continue;

            var anchorText = values[r0][c0];
            var anchorBg = bg[r0][c0];
            for (var r = r0; r < r1; r++)
            {
                for (var c = c0; c < c1; c++)
                {
                    if (values[r][c] == null && anchorText != null)
                        values[r][c] = anchorText;
                    if (bg[r][c] == null && anchorBg != null)
                        bg[r][c] = anchorBg;
                }
            }
        }
    }

    /// <summary>Re-run after merges so copied anchor text is also single-line.</summary>
    private static void NormalizeAllValueCells(string?[][] values)
    {
        for (var r = 0; r < values.Length; r++)
        for (var c = 0; c < values[r].Length; c++)
        {
            if (values[r][c] is { } v && !string.IsNullOrWhiteSpace(v))
                values[r][c] = TextNormalization.ForEmitLine(v);
        }
    }

    private static string? ColorToHexKey(Color? color)
    {
        if (color == null)
            return null;
        var r = color.Red ?? 1f;
        var g = color.Green ?? 1f;
        var b = color.Blue ?? 1f;
        // Treat near-white as no color key for grouping
        if (r >= 0.99f && g >= 0.99f && b >= 0.99f)
            return null;
        static byte F(float x) => (byte)(Math.Clamp(x, 0f, 1f) * 255f);
        return $"#{F(r):X2}{F(g):X2}{F(b):X2}";
    }
}