Urgent TypeScript Codebase Enhancement

// Define interfaces for API response objects
interface Contract {
CustomerID: number;
CustomerName: string;
ContractType: string;
BlockHoursContract?: {
HoursIncluded: number;
};
}

interface Ticket {
TicketID: number;
TicketResolvedDate: string;
CustomerID: number;
}

interface WorkHours {
TotalDurationHours: number;
}

// Function to fetch contracts with "Block Hours" type only
async function fetchContracts(proxyUrl: string, headers: HeadersInit): Promise<Contract[]> {
const response: Response = await fetch(`${proxyUrl}/contracts?itemsInPage=50`, { method: 'GET', headers: headers });
const data: { items: Contract[] } = await response.json();
console.log('All Contracts:', data.items); // Log all contracts

return data.items.filter((contract: Contract) => contract.ContractType === "Block Hours" && contract.BlockHoursContract);
}

// Function to fetch tickets for a specified month
async function fetchTickets(proxyUrl: string, headers: HeadersInit, month: string): Promise<Ticket[]> {
const response: Response = await fetch(`${proxyUrl}/tickets?itemsInPage=50`, { method: 'GET', headers: headers });
const data: { items: Ticket[] } = await response.json();
console.log('Tickets:', data.items); // Debugging
return data.items.filter((ticket: Ticket) => ticket.TicketResolvedDate && ticket.TicketResolvedDate.startsWith(month));
}

// Function to fetch work hours for a ticket
async function fetchWorkHoursForTicket(proxyUrl: string, headers: HeadersInit, ticketId: number): Promise<WorkHours> {
const response: Response = await fetch(`${proxyUrl}/tickets/${ticketId}/workhours`, { method: 'GET', headers: headers });
const workHours: WorkHours = await response.json();
console.log('Work Hours for Ticket ID', ticketId, ':', workHours); // Debugging
return workHours;
}

// Function to calculate used hours
async function calculateUsedHours(proxyUrl: string, headers: HeadersInit, month: string): Promise<Map<number, number>> {
const usedHoursMap: Map<number, number> = new Map<number, number>();
const tickets: Ticket[] = await fetchTickets(proxyUrl, headers, month);

for (const ticket of tickets) {
const currentHours: number = usedHoursMap.get(ticket.CustomerID) ?? 0;
const workHours: WorkHours = await fetchWorkHoursForTicket(proxyUrl, headers, ticket.TicketID);
const totalHours: number = currentHours + workHours.TotalDurationHours;
usedHoursMap.set(ticket.CustomerID, totalHours);
}

console.log('Used Hours Map:', usedHoursMap); // Debugging
return usedHoursMap;
}

// Function to determine the previous month
function getPreviousMonth(): string {
const today: Date = new Date();
today.setMonth(today.getMonth() - 1);
return today.toISOString().split('-').slice(0, 2).join('-');
}

// Main function with corrected type declarations and error logging
async function main(workbook: ExcelScript.Workbook): Promise<void> {
const proxyUrl: string = "https://hello-world-shy-hat-061e.michael9397.workers.dev/";
const headers: HeadersInit = { 'Content-Type': 'application/json', 'x-api-key': '20ae4fd00e8d4af4b1eb14ed19b51d66' };
const previousMonth: string = getPreviousMonth();
const contracts: Contract[] = await fetchContracts(proxyUrl, headers);
console.log('Filtered Contracts:', contracts); // Debugging - Check filtered contracts data

const usedHoursMap: Map<number, number> = await calculateUsedHours(proxyUrl, headers, previousMonth);

const sheet: ExcelScript.Worksheet | undefined = workbook.getWorksheet("2023");
if (!sheet) {
console.log("Error: Worksheet '2023' not found");
return;
}

const usedRange: ExcelScript.Range | undefined = sheet.getUsedRange();
if (!usedRange) {
console.log("Error: No used range found in worksheet '2023'");
return;
}

const rangeValues: (string | number)[][] = usedRange.getColumn(0).getValues() as (string | number)[][];
console.log('Range Values:', rangeValues); // Debugging - Check Excel range values

for (const contract of contracts) {
const customerID: number = contract.CustomerID;
const usedHours: number = usedHoursMap.get(customerID) ?? 0;
const customerName: string = contract.CustomerName;
console.log(`Processing contract for customer: ${customerName}`);

let rowIndex: number = rangeValues.findIndex((rowValue: (string | number)[]) => rowValue[0] === customerName);
console.log(`Row Index for '${customerName}': ${rowIndex}`);

if (rowIndex >= 0) {
rowIndex += 2; // Adjusting for Excel's 1-based indexing and header row
const rowRange: ExcelScript.Range = sheet.getRange(`C${rowIndex}`);
rowRange.setValue(usedHours);
console.log(`Updated hours for '${customerName}' at row ${rowIndex}`);
} else {
const newRow: number = usedRange.getRowCount() + 2; // Adjusting for header row
const newRange: ExcelScript.Range = sheet.getRange(`A${newRow}:C${newRow}`);
newRange.setValues([[customerName, contract.BlockHoursContract?.HoursIncluded ?? 0, usedHours]]);
console.log(`Added new row for '${customerName}' at row ${newRow}`);
}
}
}

// Entry point for the script execution
async function run(context: ExcelScript.Workbook): Promise<void> {
try {
await main(context);
} catch (error) {
console.log('Error running script:', error); // Logging error details
}
}
11:42 AM
here is the cors proxy
11:42 AM
below
11:42 AM
addEventListener('fetch', event => {
event.respondWith(handleRequest(event.request));
});

async function handleRequest(request) {
if (request.method === 'OPTIONS') {
return handleOptions(request);
}

const url = new URL(request.url);
const path = url.pathname.substring(1);
const query = url.search; // Includes query parameters like '?itemsInPage=50'

// Ensure no double slashes in the endpoint URL
const baseUrl = 'https://app.atera.com/api/v3';
const endpoint = `${baseUrl}/${path}${query}`;

const headers = new Headers();
headers.set('Content-Type', 'application/json');
headers.set('x-api-key', '20ae4fd00e8d4af4b1eb14ed19b51d66');

const response = await fetch(endpoint, {
method: request.method,
headers: headers
});

const responseHeaders = new Headers(response.headers);
responseHeaders.set('Access-Control-Allow-Origin', '*');

return new Response(response.body, {
status: response.status,
headers: responseHeaders
});
}

function handleOptions(request) {
const responseHeaders = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Methods': 'GET, OPTIONS',
'Access-Control-Allow-Headers': request.headers.get('Access-Control-Request-Headers'),
'Access-Control-Max-Age': '86400' // 1 day
};
return new Response(null, { status: 204, headers: responseHeaders });
}

there is no error but the script is not working as i intended

i can give you access to the excel sheet if you need

https://networkofficesc.sharepoint.com/:x:/s/TechSupport/EThgCKWs_gpNqGZR_f-xPvIBatPAHJV61pmMGBVOpoDaFQ?e=CGYkUG

when you go to scripts you may need to click on "show most up to date"

i want the script to fill the customer names column with only block hour contract customers. they should not duplicate. The hours included column should fill with the respecting customer block hour contract ammount. eg. the customer spire law has 8 hours included per month. after that..........
the monthly columns will calculate how many hours the client has used by pulling the tickets for the month and adding the hors u and displaying them. On the 16th of each month the script will run automatically and pull the last months hours. eg. FEB will show hours between JAN 16 AND FEB 16.

There are 3 endpoints in Atera which need to be accessed to achieve what i need

i can currently communicate with the endpoints but the data is not going onto the excel sheet
the cors proxy is needed due to some issues in atera
pagination is also used in atera so that may be a challenge

the script should add new customers if they are present on the 16th of each month
need to ensure customers appear in alphabetical order

script needs to be able to create and move to anew yearly sheet. ego 2024 would happen soon
the script is called "script" in excel. You can ignore the MAIN script