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

    // Build path from bottom up
    let pathParts = [];
    if (root) pathParts.push(root);

    // Add current slug
    pathParts.push(slug);

    // Traverse up the parent chain
    let currentParentId = parentId;
    while (currentParentId) {
      const parentRow = rowsById[currentParentId.toString()];
      if (!parentRow) break;

      // Add parent slug to path (after root if exists)
      pathParts.splice(root ? 1 : 0, 0, parentRow.slug);

      // Move to next parent
      currentParentId = parentRow.parentId;
    }

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

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