[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_144":145,"_146":147},"routes/_app.x.$handle.$name",{"_16":17,"_20":21,"_83":65,"_84":31,"_63":-7,"_85":-5,"_13":86,"_87":88,"_11":89,"_90":91,"_92":93,"_94":-5,"_95":-5,"_96":-5,"_97":26,"_98":99,"_100":101,"_102":-5,"_103":-5,"_104":105},"routes/_app.x.$handle.$name.search.$(branch)",{"_11":12,"_13":14,"_15":-5,"_16":17},"files",[],"project",{"_18":19,"_20":21,"_22":23,"_24":-5,"_25":26,"_27":28,"_29":28,"_30":31,"_32":-5,"_33":34,"_35":-5,"_36":26,"_37":-5,"_38":-5,"_39":40,"_41":42,"_43":-5,"_44":-5,"_45":-5,"_46":-5,"_47":48,"_49":48,"_50":51,"_52":51,"_53":51,"_54":48,"_55":48,"_56":57,"_58":59,"_60":61,"_62":26},"query","handle","piterchernychko","id","54a1c3d0-57f3-11f1-9587-ee650bb23af1","name","piter-cher","createdAt",["D",1779683803374],"deletedAt","legacyEnvVars",false,"privacy","public","httpPrivacy","description","","imageUrl","userId","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",{"_16":17,"_82":78},"org",{"_16":17,"_75":76,"_77":78,"_33":34,"_79":80,"_81":26},"branch",{"_63":64,"_20":65,"_18":66,"_67":19,"_68":-5,"_69":-5,"_70":-5,"_71":-5,"_72":-5,"_73":21,"_74":17},"isOwned","version",20,"main","54a29bc0-57f3-11f1-9587-ee650bb23af1","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Piter Chernychko","avatar","https://lh3.googleusercontent.com/a/ACg8ocKhKGyDJPqCpO0-ue5GxztF00mOGqn4ofOd0ANW-jc-BeiGIA=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","path","adoptOrgId",{"_18":19,"_20":21,"_22":140,"_24":-5,"_25":26,"_27":28,"_29":28,"_30":31,"_32":-5,"_33":34,"_35":-5,"_36":26,"_37":-5,"_38":-5,"_39":40,"_41":42,"_43":-5,"_44":-5,"_45":-5,"_46":-5,"_47":48,"_49":48,"_50":51,"_52":51,"_53":51,"_54":48,"_55":48,"_56":141,"_58":142,"_60":143,"_62":26},"tree",[135],[135],"counts",{"_124":40,"_125":126,"_127":40,"_128":129,"_130":131},"contents",[106],"file","upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles",true,"grants","orgs","customDomains",{},{"_18":107,"_20":108,"_109":110,"_111":-5,"_112":66,"_113":64,"_114":101,"_22":115,"_63":116,"_117":118,"_119":120,"_121":26,"_122":-5,"_84":123},"0ae949fe-589b-11f1-9dbe-ee650bb23af1","main.ts","fileId","54a4f406-57f3-11f1-9587-ee650bb23af1","parentId","branchId","branchVersion","latest",["D",1779755835131],19,"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\nconst MONTH_NUM: Record = {\n January: 1, February: 2, March: 3, April: 4, May: 5, June: 6,\n July: 7, August: 8, September: 9, October: 10, November: 11, December: 12,\n};\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 anchors derived from already-visible columns (no helper\n // columns needed): priorStart = Activation Date (Atomic) or Plan\n // Effective Date (Helix); priorEnd = Cycle start − 1; priorPrice =\n // Prorated Charges (Atomic case; Helix straddling rows would\n // over-attribute, but no test data hits that path).\n const activationIdx = idx(\"Activation Date\");\n const planEffectiveIdx = idx(\"Plan Effective Date\");\n const proratedIdx = idx(\"Prorated Charges\");\n\n // Per-month (Days, Prior Days, Allocated Cost) triples in column order.\n // Because the workflow inserts month columns chronologically, the last\n // entry here is the last month chronologically — which is the one that\n // gets the \"remainder\" formula so the row's months sum exactly to Price\n // (or Price + Prior Cycle Price for prior-cycle rows).\n const monthCols: {\n name: string;\n daysIdx: number;\n priorDaysIdx: number;\n allocIdx: number;\n }[] = [];\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 priorDaysIdx: idx(`${monthName} Prior Days`),\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 // Per-month Allocated Cost — two paths:\n // Simple row → existing single-cycle formula: ROUND(Days/Total*Price, 2)\n // Prior-cycle row → blend two cycles per month using EOMONTH/DATE math\n // inline. The last chronological month uses a remainder formula so\n // the row's months sum exactly to (Price + PriorPrice).\n if (totalDaysIdx >= 0 && priceIdx >= 0 && monthCols.length > 0) {\n // Pick the priorStart column for this row (same precedence as\n // Total Cycle Days). Atomic Mobile → Activation Date; Helix → Plan\n // Effective Date.\n let priorStartColIdx = -1;\n if (!isSimple) {\n if (activationIdx >= 0 && rows[r][\"Activation Date\"]) {\n priorStartColIdx = activationIdx;\n } else if (planEffectiveIdx >= 0 && rows[r][\"Plan Effective Date\"]) {\n priorStartColIdx = planEffectiveIdx;\n }\n }\n const priorCycleReady = !isSimple &&\n priorStartColIdx >= 0 &&\n proratedIdx >= 0 &&\n rows[r][\"Prorated Charges\"] != null;\n\n // Prior-cycle row we can't formula-ize → keep JS-computed static values.\n if (!isSimple && !priorCycleReady) continue;\n\n for (let m = 0; m < monthCols.length; m++) {\n const { name: monthName, 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\n if (isSimple) {\n // Single-cycle: simple ratio, with last-month remainder.\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 } else {\n // Prior-cycle row, cycle-isolated math, derived from existing\n // visible columns only (no Prior Cycle End / Prior Cycle Price\n // helpers needed):\n // priorTotal = Cycle start − priorStart\n // (priorEnd is implicit = Cycle start − 1)\n // priorPrice = Prorated Charges (Atomic case)\n // monthAllocated =\n // ( − ) / (end − start + 1) * Price\n // + / (start − priorStart) * Prorated Charges\n // Last chronological month uses a remainder formula so the row's\n // months sum exactly to (Price + Prorated Charges).\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}+${\n colLetter(proratedIdx)\n }${excelRow})-SUM(${priorRefs.join(\",\")}),2)`;\n } else {\n const { priorDaysIdx } = monthCols[m];\n if (priorDaysIdx < 0) continue; // sheet missing the helper column → static\n const dRef = `${colLetter(daysIdx)}${excelRow}`;\n const pdRef = `${colLetter(priorDaysIdx)}${excelRow}`;\n const sRef = `${colLetter(startIdx)}${excelRow}`;\n const eRef = `${colLetter(endIdx)}${excelRow}`;\n const pRef = `${colLetter(priceIdx)}${excelRow}`;\n const psRef = `${colLetter(priorStartColIdx)}${excelRow}`;\n const prRef = `${colLetter(proratedIdx)}${excelRow}`;\n cell.f =\n `ROUND((${dRef}-${pdRef})/(${eRef}-${sRef}+1)*${pRef}+${pdRef}/(${sRef}-${psRef})*${prRef},2)`;\n }\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","deleted","label",[108],"pulls","branches",1,"forks","envVars",{"_132":40,"_134":40},"connections",{"_132":40,"_133":40},"count","unfilledCount","emptyCount",{"_18":107,"_20":108,"_109":110,"_111":-5,"_112":66,"_113":64,"_114":101,"_22":136,"_63":116,"_117":118,"_119":120,"_121":26,"_122":-5,"_137":138,"_84":139},["D",1779755835131],"children",[],[108],["D",1779683803374],{"_16":17,"_82":78},{"_16":17,"_75":76,"_77":78,"_33":34,"_79":80,"_81":26},{"_63":64,"_20":65,"_18":66,"_67":19,"_68":-5,"_69":-5,"_70":-5,"_71":-5,"_72":-5,"_73":21,"_74":17},"ctx",{"_56":-5,"_103":177,"_178":179,"_180":181,"_182":26,"_183":26,"_184":26,"_185":101,"_186":-5,"_187":-7,"_188":-7,"_189":40,"_190":26,"_191":26,"_192":-5,"_193":26,"_194":26,"_195":26,"_196":-5},"ENV",{"_148":149,"_150":151,"_152":65,"_153":154,"_155":156,"_157":158,"_159":160,"_161":162,"_163":164,"_165":166,"_167":168,"_169":170,"_171":172,"_173":174,"_175":176},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","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"]

Search