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;

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

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>
  );
}

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;
}

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);
}

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);
}

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

Validate Ranges: Ensure row and column indices are within bounds
Handle Edge Cases: Check for undefined or null navigateToSheetRange
User Feedback: Provide visual indicators during navigation
Accessibility: Support keyboard shortcuts for navigation
Performance: Avoid excessive navigation calls in loops

Troubleshooting

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

PreviousText to Columns NextComponents

Last updated 1 day ago

Was this helpful?