Besoin d'un GENIE en TYPESCRIPT / JAVASCRIPT

Bonjour a tous les Kheys altruistes qui pourrait m'aider

Si vous avez la flemme, allez a la fin du post

Je suis actuellement étudiant (pas en france), par contre je ne suis pas en étude d'ingé ou de dev ou de programmation cepandant j'ai quand meme des matieres a propos d'excel

Maintenant le probleme n'est pas vraiment Excel mais TypeScript

J'ai un exercice surprenament dure, surtout que on ne peux utilise QUE TYPESCRIPT, et pas VBA par exemple

alors deja, voici le tableau de donnés: (qui s'appelle "data")

Bref voici l'exercice (oui c'est en anglais):

Le PDF dit ca pour les exercices:

Voici les 3 questions
1) [20%] Calculate the proportion of shipments which arrived on time by mode of shipment and warehouse block. Plot these in a column / bar chart.

et Voici la quatrieme (le probleme donc)
4) [50%] Write an ExcelScript which generates the report directly from the excel file given to you from the accounts department. The accounts department have committed to always providing you with an excel workbook with (only) one table which will always have the same headings (but there is no guarantee these headings will be in the same columns)

TLDR: 3 exercices d'excel relativement commun que je dois automatiser via un script en utilisant uniquement ExcelScript (et pas VBA)

Voici mon prototype de script que j'ai bricoler avec saint chatgpt, et puis avec un pote

```jsx
async function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const headerRange = sheet.getRange("1:1");
  const headers = headerRange.getTexts()[0];
  const columns = getColumns(headers);

  validateColumns(columns);

  await calculateAndVisualizeOnTimeShipments(workbook, columns);
  await calculateProfitAndSummarizeByDay(workbook, columns);
  await createTimeSeriesChartWithMovingAverage(workbook);
  await analyzeExpressAirFreightOption(workbook, columns);
  await generateSummaryReport(workbook);

  console.log("All analyses have been successfully completed and the report is generated.");
}

function getColumns(headers: string[]): { [key: string]: number } {
  const requiredColumns = ["ID", "Order Date", "Warehouse", "Mode", "Price", "Cost", "Discount", "Shipping Cost", "Weight", "Distance", "On time"];
  let columns = {};
  headers.forEach((header, index) => {
    if (requiredColumns.includes(header)) {
      columns[header] = index;
    }
  });
  return columns;
}

function validateColumns(columns: { [key: string]: number }): void {
  const requiredColumns = ["ID", "Order Date", "Warehouse", "Mode", "Price", "Cost", "Discount", "Shipping Cost", "Weight", "Distance", "On time"];
  requiredColumns.forEach(column => {
    if (columns[column] === undefined) {
      throw new Error(`Column '${column}' not found.`);
    }
  });
}

async function calculateAndVisualizeOnTimeShipments(workbook: ExcelScript.Workbook, columns: { [key: string]: number }) {
  const sheet = workbook.getActiveWorksheet();
  const pivotSheet = workbook.addWorksheet("OnTimeShipmentsReport");
  const usedRange = sheet.getUsedRange();
  const pivotTable = pivotSheet.addPivotTable("PivotTableOnTimeShipments", usedRange, "A1");

  pivotTable.addHierarchy(pivotTable.getHierarchies().getItem("Warehouse"), pivotTable.getRowHierarchies());
  pivotTable.addHierarchy(pivotTable.getHierarchies().getItem("Mode"), pivotTable.getColumnHierarchies());

  const onTimeField = pivotTable.addValues(pivotTable.getHierarchies().getItem("On time"), ExcelScript.AggregationFunction.count, "Count of On time");
  onTimeField.setNumberFormat("0");

  const chart = pivotSheet.addChart(ExcelScript.ChartType.columnClustered, pivotTable.getDataBodyRange(), ExcelScript.ChartSeriesBy.columns);
  chart.setPosition("D1");
  chart.getAxes().getItem(ExcelScript.ChartAxisType.category).getTitle().setText("Proportion of On-Time Shipments");
}

async function calculateProfitAndSummarizeByDay(workbook: ExcelScript.Workbook, columns: { [key: string]: number }) {
}

async function createTimeSeriesChartWithMovingAverage(workbook: ExcelScript.Workbook) {
}

async function analyzeExpressAirFreightOption(workbook: ExcelScript.Workbook, columns: { [key: string]: number }) {
}

async function generateSummaryReport(workbook: ExcelScript.Workbook) {
}

main();

```