[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_154":155,"_156":157},"routes/_app.x.$handle.$name",{"_95":94,"_32":45,"_104":85,"_27":53,"_13":14,"_105":-5,"_11":106,"_107":108,"_109":110,"_111":112,"_113":114,"_20":-5,"_115":-5,"_116":-5,"_117":25,"_118":119,"_120":121,"_122":-5,"_123":-5,"_124":125},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":78,"_79":80,"_81":82,"_83":25},"version",9,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","main.ts","latestPath","fileId","54a4f406-57f3-11f1-9587-ee650bb23af1","name","fileType","http","content","// XLSX Builder for n8n — paste this into a new HTTP val on val.town.\n//\n// Receives the GAAP allocation rows from n8n's \"GAAP Allocation Code\" node\n// and returns a true .xlsx binary with LIVE EXCEL FORMULAS baked into the\n// computed cells. When someone opens the file in Excel and clicks one of\n// these cells, they see the actual formula, not just the number.\n//\n// Cells that become formulas:\n// • Total Cycle Days = - + 1\n// • Allocated Cost = ROUND( Days / Total Cycle Days * Price, 2)\n// • Allocated Cost (last) = ROUND(Price - SUM(prior months' Allocated Cost), 2)\n//\n// Every other column (original supporting-file columns, divider, per-month\n// \"Days\" counts, dates, etc.) is written as a static value exactly as n8n\n// sends it.\n\nimport * as XLSX from \"npm:xlsx\";\n\nconst MONTH_DAYS_RE =\n /^(January|February|March|April|May|June|July|August|September|October|November|December) Days$/;\n\n// Parse an ISO YYYY-MM-DD string (or a Date) to a UTC Date. Returns null otherwise.\nconst parseIsoDate = (v: any): Date | null => {\n if (!v) return null;\n if (v instanceof Date) return isNaN(v.getTime()) ? null : v;\n if (typeof v === \"string\") {\n const m = v.match(/^(\\d{4})-(\\d{2})-(\\d{2})/);\n if (m) {\n const d = new Date(Date.UTC(+m[1], +m[2] - 1, +m[3]));\n return isNaN(d.getTime()) ? null : d;\n }\n }\n return null;\n};\n\n// A \"simple\" row is one whose JS-computed Total Cycle Days equals\n// (Cycle end date − Cycle start date + 1) — i.e. the row spans a SINGLE\n// cycle. Prior-cycle rows (Atomic Mobile mid-cycle activation / Helix\n// straddling prorated windows) accumulate prior-cycle days into\n// Total Cycle Days; their monthly buckets also blend prior + current\n// dollars in a way that can't be expressed as a single per-month\n// =Days/Total*Price formula. For those rows we keep the JS-computed\n// static values exactly as the workflow sent them.\nconst isSimpleSingleCycleRow = (row: Record): boolean => {\n const start = parseIsoDate(row[\"Cycle start date\"]);\n const end = parseIsoDate(row[\"Cycle end date\"]);\n const total = Number(row[\"Total Cycle Days\"]);\n if (!start || !end || !Number.isFinite(total)) return false;\n const currentDays =\n Math.round((end.getTime() - start.getTime()) / 86400000) + 1;\n return Math.abs(currentDays - total) < 0.5;\n};\n\nconst colLetter = (idx0: number): string => {\n let s = \"\";\n let n = idx0 + 1;\n while (n > 0) {\n const rem = (n - 1) % 26;\n s = String.fromCharCode(65 + rem) + s;\n n = Math.floor((n - 1) / 26);\n }\n return s;\n};\n\nexport default async function (req: Request): Promise {\n if (req.method !== \"POST\") {\n return new Response(\"Use POST\", { status: 405 });\n }\n\n let body: any;\n try {\n body = await req.json();\n } catch {\n return new Response(\"Invalid JSON body\", { status: 400 });\n }\n\n const rows: Record[] = Array.isArray(body?.rows)\n ? body.rows\n : [];\n const sheetName = String(body?.sheetName || \"GAAP Allocation\").slice(0, 31);\n const fileName = String(body?.fileName || \"gaap-allocation.xlsx\");\n if (rows.length === 0) return new Response(\"No rows\", { status: 400 });\n\n // Build the union of all column headers across rows, preserving insertion\n // order (which the workflow constructs deliberately: original-file columns\n // first, then divider, then normalized columns, then monthly buckets).\n const headers: string[] = [];\n const seen = new Set();\n for (const row of rows) {\n for (const k of Object.keys(row)) {\n if (!seen.has(k)) {\n seen.add(k);\n headers.push(k);\n }\n }\n }\n\n // Start with everything as static values.\n const ws = XLSX.utils.json_to_sheet(rows, { header: headers });\n\n // Anchor lookups by header name.\n const idx = (name: string) => headers.indexOf(name);\n const startIdx = idx(\"Cycle start date\");\n const endIdx = idx(\"Cycle end date\");\n const priceIdx = idx(\"Price\");\n const totalDaysIdx = idx(\"Total Cycle Days\");\n // Prior-cycle helpers (present when the workflow surfaced Atomic Mobile /\n // Helix prior-cycle metadata). Activation Date reuses the existing column\n // since for Atomic Mobile the activation date equals the prior cycle start.\n const activationIdx = idx(\"Activation Date\");\n const priorEndIdx = idx(\"Prior Cycle End\");\n\n // Per-month (Days, Allocated Cost) pairs in column order. Because the\n // workflow inserts month columns chronologically, the last entry here is\n // the last month chronologically — which is the one that gets the\n // \"remainder\" formula so the row's months sum exactly to Price.\n const monthCols: { name: string; daysIdx: number; allocIdx: number }[] = [];\n for (const h of headers) {\n const m = h.match(MONTH_DAYS_RE);\n if (m) {\n const monthName = m[1];\n monthCols.push({\n name: monthName,\n daysIdx: idx(h),\n allocIdx: idx(`${monthName} Allocated Cost`),\n });\n }\n }\n\n for (let r = 0; r < rows.length; r++) {\n const excelRow = r + 2; // header is row 1; data starts at row 2\n\n // Prior-cycle rows (e.g. Atomic Mobile mid-cycle activation) carry a\n // Total Cycle Days that already includes prior-cycle days, and their\n // monthly buckets blend two cycles' dollars. A simple per-month\n // =Days/Total*Price formula can't reproduce that math, so we leave\n // those rows with their JS-computed static values untouched.\n if (!isSimpleSingleCycleRow(rows[r])) continue;\n\n // Total Cycle Days = end - start + 1\n if (totalDaysIdx >= 0 && startIdx >= 0 && endIdx >= 0) {\n const ref = colLetter(totalDaysIdx) + excelRow;\n const cell = (ws as any)[ref];\n if (cell) {\n cell.f = `${colLetter(endIdx)}${excelRow}-${\n colLetter(startIdx)\n }${excelRow}+1`;\n delete cell.v;\n cell.t = \"n\";\n }\n }\n\n // Per-month Allocated Cost\n if (totalDaysIdx >= 0 && priceIdx >= 0 && monthCols.length > 0) {\n for (let m = 0; m < monthCols.length; m++) {\n const { daysIdx, allocIdx } = monthCols[m];\n if (allocIdx < 0 || daysIdx < 0) continue;\n const ref = colLetter(allocIdx) + excelRow;\n const cell = (ws as any)[ref];\n if (!cell) continue;\n\n const isLast = m === monthCols.length - 1;\n if (isLast && monthCols.length > 1) {\n const priorRefs = monthCols\n .slice(0, m)\n .map((mc) => colLetter(mc.allocIdx) + excelRow);\n cell.f = `ROUND(${colLetter(priceIdx)}${excelRow}-SUM(${\n priorRefs.join(\",\")\n }),2)`;\n } else {\n cell.f = `ROUND(${colLetter(daysIdx)}${excelRow}/${\n colLetter(totalDaysIdx)\n }${excelRow}*${colLetter(priceIdx)}${excelRow},2)`;\n }\n delete cell.v;\n cell.t = \"n\";\n }\n }\n }\n\n const wb = XLSX.utils.book_new();\n XLSX.utils.book_append_sheet(wb, ws, sheetName);\n const buf = XLSX.write(wb, { type: \"array\", bookType: \"xlsx\" }) as Uint8Array;\n\n return new Response(buf, {\n headers: {\n \"Content-Type\":\n \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\",\n \"Content-Disposition\": `attachment; filename=\"${\n fileName.replace(/\"/g, \"\")\n }\"`,\n },\n });\n}\n","// XLSX Builder for n8n — paste this into a new HTTP val on val.town.\n//\n// Receives the GAAP allocation rows from n8n's \"GAAP Allocation Code\" node\n// and returns a true .xlsx binary with LIVE EXCEL FORMULAS baked into the\n// computed cells. When someone opens the file in Excel and clicks one of\n// these cells, they see the actual formula, not just the number.\n//\n// Cells that become formulas:\n// • Total Cycle Days = - + 1\n// • Allocated Cost = ROUND( Days / Total Cycle Days * Price, 2)\n// • Allocated Cost (last) = ROUND(Price - SUM(prior months' Allocated Cost), 2)\n//\n// Every other column (original supporting-file columns, divider, per-month\n// \"Days\" counts, dates, etc.) is written as a static value exactly as n8n\n// sends it.\n\nimport * as XLSX from \"npm:xlsx\";\n\nconst MONTH_DAYS_RE =\n /^(January|February|March|April|May|June|July|August|September|October|November|December) Days$/;\n\n// Parse an ISO YYYY-MM-DD string (or a Date) to a UTC Date. Returns null otherwise.\nconst parseIsoDate = (v: any): Date | null => {\n if (!v) return null;\n if (v instanceof Date) return isNaN(v.getTime()) ? null : v;\n if (typeof v === \"string\") {\n const m = v.match(/^(\\d{4})-(\\d{2})-(\\d{2})/);\n if (m) {\n const d = new Date(Date.UTC(+m[1], +m[2] - 1, +m[3]));\n return isNaN(d.getTime()) ? null : d;\n }\n }\n return null;\n};\n\n// A \"simple\" row is one whose JS-computed Total Cycle Days equals\n// (Cycle end date − Cycle start date + 1) — i.e. the row spans a SINGLE\n// cycle. Prior-cycle rows (Atomic Mobile mid-cycle activation / Helix\n// straddling prorated windows) accumulate prior-cycle days into\n// Total Cycle Days; their monthly buckets also blend prior + current\n// dollars in a way that can't be expressed as a single per-month\n// =Days/Total*Price formula. For those rows we keep the JS-computed\n// static values exactly as the workflow sent them.\nconst isSimpleSingleCycleRow = (row: Record): boolean => {\n const start = parseIsoDate(row[\"Cycle start date\"]);\n const end = parseIsoDate(row[\"Cycle end date\"]);\n const total = Number(row[\"Total Cycle Days\"]);\n if (!start || !end || !Number.isFinite(total)) return false;\n const currentDays =\n Math.round((end.getTime() - start.getTime()) / 86400000) + 1;\n return Math.abs(currentDays - total) < 0.5;\n};\n\nconst colLetter = (idx0: number): string => {\n let s = \"\";\n let n = idx0 + 1;\n while (n > 0) {\n const rem = (n - 1) % 26;\n s = String.fromCharCode(65 + rem) + s;\n n = Math.floor((n - 1) / 26);\n }\n return s;\n};\n\nexport default async function (req: Request): Promise {\n if (req.method !== \"POST\") {\n return new Response(\"Use POST\", { status: 405 });\n }\n\n let body: any;\n try {\n body = await req.json();\n } catch {\n return new Response(\"Invalid JSON body\", { status: 400 });\n }\n\n const rows: Record[] = Array.isArray(body?.rows)\n ? body.rows\n : [];\n const sheetName = String(body?.sheetName || \"GAAP Allocation\").slice(0, 31);\n const fileName = String(body?.fileName || \"gaap-allocation.xlsx\");\n if (rows.length === 0) return new Response(\"No rows\", { status: 400 });\n\n // Build the union of all column headers across rows, preserving insertion\n // order (which the workflow constructs deliberately: original-file columns\n // first, then divider, then normalized columns, then monthly buckets).\n const headers: string[] = [];\n const seen = new Set();\n for (const row of rows) {\n for (const k of Object.keys(row)) {\n if (!seen.has(k)) {\n seen.add(k);\n headers.push(k);\n }\n }\n }\n\n // Start with everything as static values.\n const ws = XLSX.utils.json_to_sheet(rows, { header: headers });\n\n // Anchor lookups by header name.\n const idx = (name: string) => headers.indexOf(name);\n const startIdx = idx(\"Cycle start date\");\n const endIdx = idx(\"Cycle end date\");\n const priceIdx = idx(\"Price\");\n const totalDaysIdx = idx(\"Total Cycle Days\");\n // Prior-cycle helpers (present when the workflow surfaced Atomic Mobile /\n // Helix prior-cycle metadata). Activation Date reuses the existing column\n // since for Atomic Mobile the activation date equals the prior cycle start.\n const activationIdx = idx(\"Activation Date\");\n const priorEndIdx = idx(\"Prior Cycle End\");\n\n // Per-month (Days, Allocated Cost) pairs in column order. Because the\n // workflow inserts month columns chronologically, the last entry here is\n // the last month chronologically — which is the one that gets the\n // \"remainder\" formula so the row's months sum exactly to Price.\n const monthCols: { name: string; daysIdx: number; allocIdx: number }[] = [];\n for (const h of headers) {\n const m = h.match(MONTH_DAYS_RE);\n if (m) {\n const monthName = m[1];\n monthCols.push({\n name: monthName,\n daysIdx: idx(h),\n allocIdx: idx(`${monthName} Allocated Cost`),\n });\n }\n }\n\n for (let r = 0; r < rows.length; r++) {\n const excelRow = r + 2; // header is row 1; data starts at row 2\n const isSimple = isSimpleSingleCycleRow(rows[r]);\n\n // Total Cycle Days formula — different shape per row kind:\n // • Simple row: = end - start + 1\n // • Prior-cycle row: = (end - start + 1) + (priorEnd - activation + 1)\n // For prior-cycle rows we require the Activation Date and Prior Cycle\n // End cells to actually carry values; otherwise we leave the JS-\n // computed static value (matches current Helix prior-cycle behavior\n // where Activation Date is blank).\n if (totalDaysIdx >= 0 && startIdx >= 0 && endIdx >= 0) {\n const ref = colLetter(totalDaysIdx) + excelRow;\n const cell = (ws as any)[ref];\n if (cell) {\n if (isSimple) {\n cell.f = `${colLetter(endIdx)}${excelRow}-${\n colLetter(startIdx)\n }${excelRow}+1`;\n delete cell.v;\n cell.t = \"n\";\n } else if (\n activationIdx >= 0 && priorEndIdx >= 0 &&\n rows[r][\"Activation Date\"] && rows[r][\"Prior Cycle End\"]\n ) {\n cell.f = `(${colLetter(endIdx)}${excelRow}-${\n colLetter(startIdx)\n }${excelRow}+1)+(${colLetter(priorEndIdx)}${excelRow}-${\n colLetter(activationIdx)\n }${excelRow}+1)`;\n delete cell.v;\n cell.t = \"n\";\n }\n }\n }\n\n // Per-month Allocated Cost formulas are only applied to simple rows;\n // prior-cycle rows blend two cycles' dollars per month, which a single\n // =Days/Total*Price formula can't reproduce. Those keep their JS-\n // computed static values.\n if (!isSimple) continue;\n\n // Per-month Allocated Cost\n if (totalDaysIdx >= 0 && priceIdx >= 0 && monthCols.length > 0) {\n for (let m = 0; m < monthCols.length; m++) {\n const { daysIdx, allocIdx } = monthCols[m];\n if (allocIdx < 0 || daysIdx < 0) continue;\n const ref = colLetter(allocIdx) + excelRow;\n const cell = (ws as any)[ref];\n if (!cell) continue;\n\n const isLast = m === monthCols.length - 1;\n if (isLast && monthCols.length > 1) {\n const priorRefs = monthCols\n .slice(0, m)\n .map((mc) => colLetter(mc.allocIdx) + excelRow);\n cell.f = `ROUND(${colLetter(priceIdx)}${excelRow}-SUM(${\n priorRefs.join(\",\")\n }),2)`;\n } else {\n cell.f = `ROUND(${colLetter(daysIdx)}${excelRow}/${\n colLetter(totalDaysIdx)\n }${excelRow}*${colLetter(priceIdx)}${excelRow},2)`;\n }\n delete cell.v;\n cell.t = \"n\";\n }\n }\n }\n\n const wb = XLSX.utils.book_new();\n XLSX.utils.book_append_sheet(wb, ws, sheetName);\n const buf = XLSX.write(wb, { type: \"array\", bookType: \"xlsx\" }) as Uint8Array;\n\n return new Response(buf, {\n headers: {\n \"Content-Type\":\n \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\",\n \"Content-Disposition\": `attachment; filename=\"${\n fileName.replace(/\"/g, \"\")\n }\"`,\n },\n });\n}\n","createdAt",["D",1779752059051],"branchVersion","branchId","54a29bc0-57f3-11f1-9587-ee650bb23af1","id","54a1c3d0-57f3-11f1-9587-ee650bb23af1","piter-cher",["D",1779683803374],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","17b54865-9ad6-4808-bacc-de7e7462ab4f","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019e5d68-b691-759e-a311-00d7dc76a9b7","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_95":94,"_103":99},"org",{"_95":94,"_96":97,"_98":99,"_18":55,"_100":101,"_102":25},"branch",{"_13":84,"_32":85,"_43":42,"_86":44,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":45,"_93":94},"isOwned",20,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","piterchernychko","handle","displayName","Piter Chernychko","avatar","https://lh3.googleusercontent.com/a/ACg8ocKhKGyDJPqCpO0-ue5GxztF00mOGqn4ofOd0ANW-jc-BeiGIA=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_43":44,"_32":45,"_38":150,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":-5,"_67":-5,"_68":69,"_70":69,"_71":72,"_73":72,"_74":72,"_75":69,"_76":69,"_77":151,"_79":152,"_81":153,"_83":25},"tree",[145],"files",[145],"counts",{"_134":61,"_135":136,"_137":61,"_138":139,"_140":141},"contents",[126],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_43":127,"_32":28,"_30":31,"_128":-5,"_41":42,"_40":14,"_129":25,"_38":130,"_13":131,"_33":34,"_35":37,"_24":25,"_132":-5,"_27":133},"403106be-5892-11f1-a2ec-ee650bb23af1","parentId","latest",["D",1779752059051],8,"label",[28],"pulls","branches",1,"forks","envVars",{"_142":61,"_144":61},"connections",{"_142":61,"_143":61},"count","unfilledCount","emptyCount",{"_43":127,"_32":28,"_30":31,"_128":-5,"_41":42,"_40":14,"_129":25,"_38":146,"_13":131,"_33":34,"_35":37,"_24":25,"_132":-5,"_147":148,"_27":149},["D",1779752059051],"children",[],[28],["D",1779683803374],{"_95":94,"_103":99},{"_95":94,"_96":97,"_98":99,"_18":55,"_100":101,"_102":25},{"_13":84,"_32":85,"_43":42,"_86":44,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":45,"_93":94},"ctx",{"_77":-5,"_123":187,"_188":189,"_190":191,"_192":25,"_193":25,"_194":25,"_195":121,"_196":-5,"_197":-7,"_198":-7,"_199":61,"_200":25,"_201":25,"_202":-5,"_203":25,"_204":25,"_205":25,"_206":-5},"ENV",{"_158":159,"_160":161,"_162":85,"_163":164,"_165":166,"_167":168,"_169":170,"_171":172,"_173":174,"_175":176,"_177":178,"_179":180,"_181":182,"_183":184,"_185":186},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","811aa22498c9b0b5a47e8d778de579cdb990f332","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v9