paste script.osts

{"version":"0.2.0","body":"function main(workbook: ExcelScript.Workbook, input: string): void {\n  try {\n    // Parse the input as JSON to get the copiedData\n    let inputData: unknown[][] = JSON.parse(input);\n\n    let targetWorksheet: ExcelScript.Worksheet = workbook.getWorksheet(\"Sample_data\");\n\n    // Loop through each row of the filtered data\n    for (let i: number = 0; i < inputData.length; i++) {\n      // Check if the key columns exist in the data sheet\n      let keyExists: boolean = checkKeyExists(targetWorksheet, inputData[i]);\n\n      // If key columns exist, print a message\n      if (keyExists) {\n        console.log(`Match found for row ${i + 1} in the data sheet.`);\n      } else {\n        // If key columns don't exist, paste the data starting from A2\n        pasteData(targetWorksheet, inputData[i], i + 2);\n        console.log(`Data pasted for row ${i + 1}.`);\n      }\n    }\n\n    // Group and sum data in \"Sample_data\" sheet and paste into \"Week_data\" sheet\n    groupAndSumData(workbook);\n\n    // Sum data in \"Week_data\" sheet columns D and E and paste into column F\n    sumAndPasteDataInWeek(workbook);\n\n    console.log(\"Data processing complete.\");\n  } catch (error) {\n    console.log(\"Error while processing data:\", error);\n  }\n}\n\n// Function to check if key columns exist in the data sheet\nfunction checkKeyExists(worksheet: ExcelScript.Worksheet, rowData: unknown[]): boolean {\n  let name: string = String(rowData[0]);\n  let id: number = Number(rowData[1]);\n  let st_id: number = Number(rowData[2]);\n  let supervisor: string = String(rowData[3]);\n  let school: string = String(rowData[4]);\n  let department: string = String(rowData[5]);\n  let code: string = String(rowData[6]);\n  let convenor: string = String(rowData[7]);\n  let period: string = String(rowData[8]);\n  let activity: string = String(rowData[9]);\n  let first: string = String(rowData[10]);\n  let paycode: string = String(rowData[11]);\n\n  let usedRangeValues: unknown[][] = worksheet.getUsedRange().getValues();\n\n  let keyExists: boolean = usedRangeValues.some((row) => {\n    return (\n      row[0] === name &&\n      row[1] === id &&\n      row[2] === st_id &&\n      row[3] === supervisor &&\n      row[4] === school &&\n      row[5] === department &&\n      row[6] === code &&\n      row[7] === convenor &&\n      row[8] === period &&\n      row[9] === activity &&\n      row[10] === first &&\n      row[11] === paycode\n    );\n  });\n\n  return keyExists;\n}\n\n// Function to paste data starting from a specific row\nfunction pasteData(worksheet: ExcelScript.Worksheet, rowData: unknown[], startRow: number): void {\n  let targetRange: ExcelScript.Range = worksheet.getRange(`A${startRow}:Q${startRow}`);\n\n  for (let j: number = 0; j < rowData.length; j++) {\n    targetRange.getCell(0, j).setValue(rowData[j]);\n  }\n}\n\n// Function to group and sum data in \"Sample_data\" sheet and paste into \"Week_data\" sheet\nfunction groupAndSumData(workbook: ExcelScript.Workbook): void {\n  let sampleDataSheet: ExcelScript.Worksheet = workbook.getWorksheet(\"Sample_data\");\n  let usedRange: ExcelScript.Range = sampleDataSheet.getUsedRange();\n  let dataValues: unknown[][] = usedRange.getValues();\n  let groupedData: { [key: string]: { sum: number, qValues: number[] } } = {};\n\n  for (let i: number = 1; i < dataValues.length; i++) {\n    let key: string = `${dataValues[i][1]}_${dataValues[i][6]}_${dataValues[i][8]}`;\n    if (!groupedData[key]) {\n      groupedData[key] = { sum: 0, qValues: [] };\n    }\n    groupedData[key].sum += Number(dataValues[i][16]); // Assuming the sum is for column P (adjust as needed)\n    groupedData[key].qValues.push(Number(dataValues[i][15])); // Store values from column Q\n  }\n\n  let weekDataSheet: ExcelScript.Worksheet = workbook.getWorksheet(\"Week_data\");\n  let targetRange: ExcelScript.Range = weekDataSheet.getRange(`A2:E${Object.keys(groupedData).length + 1}`);\n  let outputData: unknown[][] = [];\n\n  for (let key in groupedData) {\n    let keys: string[] = key.split('_');\n    // Display any one value from column Q along with others\n    let qValue: number = groupedData[key].qValues.length > 0 ? groupedData[key].qValues[0] : 0;\n    outputData.push([keys[0], keys[1], keys[2], groupedData[key].sum, qValue]);\n  }\n\n  targetRange.setValues(outputData);\n}\n\n// Function to sum data in \"Week_data\" sheet columns D and E and paste into column F\nfunction sumAndPasteDataInWeek(workbook: ExcelScript.Workbook): void {\n  let weekDataSheet: ExcelScript.Worksheet = workbook.getWorksheet(\"Week_data\");\n  let usedRange: ExcelScript.Range = weekDataSheet.getUsedRange();\n  let dataValues: unknown[][] = usedRange.getValues();\n\n  for (let i: number = 1; i < dataValues.length; i++) {\n    let sumValue: number = Number(dataValues[i][3]) + Number(dataValues[i][4]);\n    weekDataSheet.getCell(i, 5).setValue(sumValue); // Assuming column F is at index 5 (adjust as needed)\n\n    // Set background color based on conditions\n    let color: string = sumValue > 8 ? \"red\" : (sumValue < 8 ? \"yellow\" : \"green\");\n    weekDataSheet.getRange(`F${i + 1}`).getFormat().getFill().setColor(color);\n  }\n}\n","description":"","parameterInfo":"{\"version\":1,\"originalParameterOrder\":[{\"name\":\"input\",\"index\":0}],\"parameterSchema\":{\"type\":\"object\",\"required\":[\"input\"],\"properties\":{\"input\":{\"type\":\"string\"}}},\"returnSchema\":{\"type\":\"object\",\"properties\":{}},\"signature\":{\"comment\":\"\",\"parameters\":[{\"name\":\"workbook\",\"comment\":\"\"},{\"name\":\"input\",\"comment\":\"\"}]}}","apiInfo":"{\"variant\":\"synchronous\",\"variantVersion\":2}"}