Navigate to Sheet Range

Programmatically navigate and scroll to specific cell ranges

The useNavigateToSheetRange hook provides a programmatic way to navigate to specific cell ranges, change sheets, and scroll to particular locations in your spreadsheet. This is useful for implementing search functionality, jumping to errors, following links, and creating guided tours.

Overview

Navigate to Sheet Range enables:

• Sheet switching: Change the active sheet programmatically

• Cell navigation: Jump to specific cells or ranges

• Auto-scrolling: Automatically scroll cells into view

• Selection updates: Update selections when navigating

• Cross-sheet navigation: Navigate across different sheets

Basic Usage

import { SpreadsheetProvider } from "@rowsncolumns/spreadsheet";
import { useNavigateToSheetRange } from "@rowsncolumns/spreadsheet";

function MySpreadsheet() {
  const navigateToSheetRange = useNavigateToSheetRange();

  const handleNavigate = () => {
    navigateToSheetRange?.({
      sheetId: 2,
      startRowIndex: 10,
      startColumnIndex: 5,
      endRowIndex: 15,
      endColumnIndex: 10,
    });
  };

  return (
    <button onClick={handleNavigate}>
      Go to Sheet 2, Range F11:K16
    </button>
  );
}

Hook Usage

The hook must be used within a SpreadsheetProvider:

import { SpreadsheetProvider } from "@rowsncolumns/spreadsheet";
import { useNavigateToSheetRange } from "@rowsncolumns/spreadsheet";

function NavigationComponent() {
  const navigateToSheetRange = useNavigateToSheetRange();

  // navigateToSheetRange is available for use
  return <YourComponent navigate={navigateToSheetRange} />;
}

function App() {
  return (
    <SpreadsheetProvider>
      <NavigationComponent />
    </SpreadsheetProvider>
  );
}

Function Signature

type SheetRange = {
  sheetId: number;
  startRowIndex: number;
  startColumnIndex: number;
  endRowIndex: number;
  endColumnIndex: number;
};

type NavigateToSheetRange = (range: SheetRange) => void;

Navigation Examples

Navigate to Specific Cell

const navigateToCell = (sheetId: number, rowIndex: number, columnIndex: number) => {
  navigateToSheetRange?.({
    sheetId,
    startRowIndex: rowIndex,
    startColumnIndex: columnIndex,
    endRowIndex: rowIndex,
    endColumnIndex: columnIndex,
  });
};

// Navigate to cell B5 on sheet 1
navigateToCell(1, 5, 2);

Navigate to Range

const navigateToRange = () => {
  // Navigate to range A1:E10 on sheet 3
  navigateToSheetRange?.({
    sheetId: 3,
    startRowIndex: 1,
    startColumnIndex: 1,
    endRowIndex: 10,
    endColumnIndex: 5,
  });
};

Navigate to Named Range

function navigateToNamedRange(namedRangeName: string, namedRanges: NamedRange[]) {
  const namedRange = namedRanges.find((nr) => nr.name === namedRangeName);

  if (!namedRange || !namedRange.range) {
    console.error(`Named range "${namedRangeName}" not found`);
    return;
  }

  navigateToSheetRange?.(namedRange.range);
}

// Navigate to named range "SalesData"
navigateToNamedRange("SalesData", namedRanges);

Complete Example

import React, { useState } from "react";
import {
  SpreadsheetProvider,
  CanvasGrid,
  Sheet,
  useNavigateToSheetRange,
} from "@rowsncolumns/spreadsheet";
import {
  useSpreadsheetState,
  SheetData,
  CellData,
} from "@rowsncolumns/spreadsheet-state";

function NavigationControls() {
  const navigateToSheetRange = useNavigateToSheetRange();

  const quickJumps = [
    { label: "Go to A1", sheetId: 1, row: 1, col: 1 },
    { label: "Go to Z100", sheetId: 1, row: 100, col: 26 },
    { label: "Sheet 2 - F11:K16", sheetId: 2, row: 11, col: 6, endRow: 16, endCol: 11 },
  ];

  const handleQuickJump = (jump: typeof quickJumps[0]) => {
    navigateToSheetRange?.({
      sheetId: jump.sheetId,
      startRowIndex: jump.row,
      startColumnIndex: jump.col,
      endRowIndex: jump.endRow || jump.row,
      endColumnIndex: jump.endCol || jump.col,
    });
  };

  return (
    <div className="flex gap-2 p-2">
      {quickJumps.map((jump, index) => (
        <button
          key={index}
          onClick={() => handleQuickJump(jump)}
          className="px-3 py-1 bg-blue-500 text-white rounded"
        >
          {jump.label}
        </button>
      ))}
    </div>
  );
}

function SpreadsheetWithNavigation() {
  const [sheets, setSheets] = useState<Sheet[]>([
    { sheetId: 1, rowCount: 200, columnCount: 26, title: "Sheet 1" },
    { sheetId: 2, rowCount: 200, columnCount: 26, title: "Sheet 2" },
  ]);
  const [sheetData, setSheetData] = useState<SheetData<CellData>>({});

  const {
    activeCell,
    activeSheetId,
    selections,
    getCellData,
    onChangeActiveCell,
    onChangeSelections,
    onChangeActiveSheet,
  } = useSpreadsheetState({
    sheets,
    sheetData,
    onChangeSheets: setSheets,
    onChangeSheetData: setSheetData,
  });

  return (
    <SpreadsheetProvider>
      <div className="flex flex-col h-screen">
        <NavigationControls />

        <div className="flex-1">
          <CanvasGrid
            sheetId={activeSheetId}
            activeCell={activeCell}
            selections={selections}
            getCellData={getCellData}
            onChangeActiveCell={onChangeActiveCell}
            onChangeSelections={onChangeSelections}
            onChangeActiveSheet={onChangeActiveSheet}
          />
        </div>
      </div>
    </SpreadsheetProvider>
  );
}

export default SpreadsheetWithNavigation;

Use Cases

Search Results Navigation

Navigate to cells matching search criteria:

function SearchAndNavigate() {
  const navigateToSheetRange = useNavigateToSheetRange();
  const [searchResults, setSearchResults] = useState<SheetRange[]>([]);
  const [currentIndex, setCurrentIndex] = useState(0);

  const handleNextResult = () => {
    if (searchResults.length === 0) return;

    const nextIndex = (currentIndex + 1) % searchResults.length;
    setCurrentIndex(nextIndex);
    navigateToSheetRange?.(searchResults[nextIndex]);
  };

  const handlePreviousResult = () => {
    if (searchResults.length === 0) return;

    const prevIndex = currentIndex === 0 ? searchResults.length - 1 : currentIndex - 1;
    setCurrentIndex(prevIndex);
    navigateToSheetRange?.(searchResults[prevIndex]);
  };

  return (
    <div>
      <button onClick={handlePreviousResult}>Previous</button>
      <span>{currentIndex + 1} / {searchResults.length}</span>
      <button onClick={handleNextResult}>Next</button>
    </div>
  );
}

Error Navigation

Jump to cells with formula errors:

function navigateToErrors(
  sheets: Sheet[],
  sheetData: SheetData<CellData>,
  navigateToSheetRange: NavigateToSheetRange
) {
  const errors: SheetRange[] = [];

  sheets.forEach((sheet) => {
    const data = sheetData[sheet.sheetId];
    if (!data) return;

    data.forEach((row, rowIndex) => {
      if (!row?.values) return;

      row.values.forEach((cell, colIndex) => {
        if (cell?.effectiveValue?.errorValue) {
          errors.push({
            sheetId: sheet.sheetId,
            startRowIndex: rowIndex,
            startColumnIndex: colIndex,
            endRowIndex: rowIndex,
            endColumnIndex: colIndex,
          });
        }
      });
    });
  });

  // Navigate to first error
  if (errors.length > 0) {
    navigateToSheetRange(errors[0]);
  }

  return errors;
}

Table Navigation

Navigate to different sections of a table:

function navigateToTable(table: TableView, section: "header" | "data" | "total") {
  const range = { ...table.range };

  switch (section) {
    case "header":
      range.endRowIndex = range.startRowIndex;
      break;
    case "data":
      range.startRowIndex = range.startRowIndex + 1;
      if (table.totalsRow) {
        range.endRowIndex = range.endRowIndex - 1;
      }
      break;
    case "total":
      if (table.totalsRow) {
        range.startRowIndex = range.endRowIndex;
      }
      break;
  }

  navigateToSheetRange?.(range);
}

Hyperlink Navigation

Navigate when clicking on cell references:

function handleCellReferenceClick(cellReference: string) {
  // Parse cell reference like "Sheet2!A1" or "B5"
  const match = cellReference.match(/^(?:(.+)!)?([A-Z]+)(\d+)$/);

  if (!match) return;

  const [, sheetName, column, row] = match;
  const sheetId = sheetName ? getSheetIdByName(sheetName) : activeSheetId;
  const columnIndex = columnToIndex(column);
  const rowIndex = parseInt(row, 10);

  navigateToSheetRange?.({
    sheetId,
    startRowIndex: rowIndex,
    startColumnIndex: columnIndex,
    endRowIndex: rowIndex,
    endColumnIndex: columnIndex,
  });
}

Guided Tour

Create a step-by-step tour of your spreadsheet:

function SpreadsheetTour() {
  const navigateToSheetRange = useNavigateToSheetRange();

  const tourSteps = [
    {
      title: "Welcome",
      range: { sheetId: 1, startRowIndex: 1, startColumnIndex: 1, endRowIndex: 1, endColumnIndex: 1 },
      description: "This is cell A1, the starting point",
    },
    {
      title: "Sales Data",
      range: { sheetId: 1, startRowIndex: 5, startColumnIndex: 2, endRowIndex: 20, endColumnIndex: 5 },
      description: "Here is our sales data table",
    },
    {
      title: "Summary",
      range: { sheetId: 2, startRowIndex: 1, startColumnIndex: 1, endRowIndex: 10, endColumnIndex: 3 },
      description: "The summary is on Sheet 2",
    },
  ];

  const [currentStep, setCurrentStep] = useState(0);

  const goToStep = (stepIndex: number) => {
    if (stepIndex < 0 || stepIndex >= tourSteps.length) return;

    setCurrentStep(stepIndex);
    navigateToSheetRange?.(tourSteps[stepIndex].range);
  };

  return (
    <div className="p-4 border rounded">
      <h3 className="font-bold mb-2">{tourSteps[currentStep].title}</h3>
      <p className="text-sm mb-4">{tourSteps[currentStep].description}</p>

      <div className="flex gap-2">
        <button
          onClick={() => goToStep(currentStep - 1)}
          disabled={currentStep === 0}
          className="px-3 py-1 bg-gray-500 text-white rounded disabled:opacity-50"
        >
          Previous
        </button>
        <button
          onClick={() => goToStep(currentStep + 1)}
          disabled={currentStep === tourSteps.length - 1}
          className="px-3 py-1 bg-blue-500 text-white rounded disabled:opacity-50"
        >
          Next
        </button>
      </div>

      <div className="text-sm text-gray-500 mt-2">
        Step {currentStep + 1} of {tourSteps.length}
      </div>
    </div>
  );
}

Advanced Features

Smooth Scrolling

Implement smooth scrolling with animation:

function smoothNavigate(range: SheetRange, duration: number = 300) {
  // Implementation would involve animating the scroll position
  // This is handled internally by the spreadsheet component
  navigateToSheetRange?.(range);
}

Navigation History

Track navigation history for back/forward buttons:

function useNavigationHistory() {
  const [history, setHistory] = useState<SheetRange[]>([]);
  const [currentIndex, setCurrentIndex] = useState(-1);
  const navigateToSheetRange = useNavigateToSheetRange();

  const navigate = (range: SheetRange) => {
    const newHistory = history.slice(0, currentIndex + 1);
    newHistory.push(range);
    setHistory(newHistory);
    setCurrentIndex(newHistory.length - 1);
    navigateToSheetRange?.(range);
  };

  const goBack = () => {
    if (currentIndex > 0) {
      const newIndex = currentIndex - 1;
      setCurrentIndex(newIndex);
      navigateToSheetRange?.(history[newIndex]);
    }
  };

  const goForward = () => {
    if (currentIndex < history.length - 1) {
      const newIndex = currentIndex + 1;
      setCurrentIndex(newIndex);
      navigateToSheetRange?.(history[newIndex]);
    }
  };

  return {
    navigate,
    goBack,
    goForward,
    canGoBack: currentIndex > 0,
    canGoForward: currentIndex < history.length - 1,
  };
}

Helper Functions

Cell Address to Range

Convert cell address notation to range:

import { cellToAddress } from "@rowsncolumns/utils";

function addressToRange(address: string, sheetId: number): SheetRange | null {
  // Parse address like "A1" or "Sheet2!B5:C10"
  const match = address.match(/^(?:(.+)!)?([A-Z]+)(\d+)(?::([A-Z]+)(\d+))?$/);

  if (!match) return null;

  const [, sheetName, startCol, startRow, endCol, endRow] = match;
  const actualSheetId = sheetName ? getSheetIdByName(sheetName) : sheetId;

  return {
    sheetId: actualSheetId,
    startRowIndex: parseInt(startRow, 10),
    startColumnIndex: columnToIndex(startCol),
    endRowIndex: parseInt(endRow || startRow, 10),
    endColumnIndex: columnToIndex(endCol || startCol),
  };
}

Best Practices

1. Validate Ranges: Ensure row and column indices are within bounds

2. Handle Edge Cases: Check for undefined or null navigateToSheetRange

3. User Feedback: Provide visual indicators during navigation

4. Accessibility: Support keyboard shortcuts for navigation

5. Performance: Avoid excessive navigation calls in loops

Troubleshooting

Navigation Not Working

• Verify you're calling the hook within SpreadsheetProvider

• Check that sheet IDs and indices are valid

• Ensure the component is properly mounted

Sheet Not Switching

• Confirm the target sheet exists in the sheets array

• Verify onChangeActiveSheet is properly connected

• Check that sheet IDs match

Scroll Position Not Updating

• Ensure row and column indices are within the sheet bounds

• Verify the grid is properly rendered

• Check for conflicting scroll handlers