function BUILDPATH(id, slug, parentId, root) {
  if (!id || !slug) return "";

  try {
    const sheet = SpreadsheetApp.getActiveSheet();
    const range = SpreadsheetApp.getActiveRange();
    const formula = range.getFormula();

    const matches = formula.match(/([A-Z]+)\d+/g);
    if (!matches) return "/" + (root ? root + "/" : "") + slug;

    const [idCol, slugCol, parentCol] = matches.map(match => match.replace(/\d+/, ''));

    // Get the full data once
    const lastRow = sheet.getLastRow();
    const data = sheet.getRange(1, 1, lastRow, sheet.getLastColumn()).getValues();

    // Get column indexes
    const idColIndex = sheet.getRange(idCol + "1").getColumn() - 1;
    const slugColIndex = sheet.getRange(slugCol + "1").getColumn() - 1;
    const parentColIndex = sheet.getRange(parentCol + "1").getColumn() - 1;

    // Create a map of all rows by ID for faster lookup
    const rowsById = {};
    for (let i = 0; i < data.length; i++) {
      const rowId = data[i][idColIndex];
      if (rowId) {
        rowsById[rowId.toString()] = {
          id: rowId,
          slug: data[i][slugColIndex],
          parentId: data[i][parentColIndex] ? data[i][parentColIndex].toString() : null
        };
      }
    }

    // Build path parts array
    let pathParts = [];

    // Start with the current item's slug
    pathParts.push(slug);

    // Traverse up the parent chain
    let currentParentId = parentId ? parentId.toString() : null;
    let maxIterations = 20; // Prevent infinite loops

    while (currentParentId && maxIterations > 0) {
      const parentRow = rowsById[currentParentId];
      if (!parentRow) break;

      pathParts.unshift(parentRow.slug);
      currentParentId = parentRow.parentId;
      maxIterations--;
    }

    // Add root at the beginning if provided
    if (root) pathParts.unshift(root);

    return '/' + pathParts.join('/');

  } catch (error) {
    return "#ERROR! " + error.toString();
  }
}