How to show previous values for missing rows when comparing two Excel datasets in JavaScript

I’m working on a script to compare two Excel files (an “old” and a “new” dataset) using JavaScript and the xlsx library. The goal is to highlight changes, additions, and deletions between the datasets. However, I’m encountering a specific issue:

When a row exists in the old dataset but is missing in the new dataset (e.g., its value is now blank), the script doesn’t display the previous value from the old dataset in the output.

For example:

Old dataset:
| ID | NAME | OCCUPATION |
| ——– | ————– | ————– |
| 1 | Walter White | Teacher |
| 2 | Skyler White | Homemaker |
| 3 | Jesse Pinkman | |
| 4 | Saul Gooodman | Lawyer |
| 5 | Hank Schrader | DEA agent |

New dataset:
| ID | NAME | OCCUPATION |
| ——– | ————– | ————– |
| 1 | Walter White | Teacher |
| 2 | Skyler White | Homemaker |
| 3 | Jesse Pinkman | Dealer |
| 4 | Saul Goodman | Lawyer |
| 5 | Hank Schrader | |

Expected output:

ID 5 should show the previous occupation (“DEA agent”) in the output, highlighted in red.

Here’s the relevant portion of my script that handles the comparison:

 document.getElementById('excelDataset1').addEventListener('change', function() {
updateFileList(this.files, 'excelFileList1');
});

 document.getElementById('excelDataset2').addEventListener('change', function() {
updateFileList(this.files, 'excelFileList2');
});

async function readExcel(file) {
return new Promise((resolve, reject) => {
    const reader = new FileReader();
    reader.onload = (e) => {
        const data = new Uint8Array(e.target.result);
        const workbook = XLSX.read(data, { type: 'array' });
        resolve(workbook);
    };
    reader.onerror = () => reject(reader.error);
    reader.readAsArrayBuffer(file);
});
}

async function parseSheet(sheet, pkColumns) {
const json = XLSX.utils.sheet_to_json(sheet, { header: 1 });
const headers = json[0];
const data = {};

for (let i = 1; i < json.length; i++) {
    const row = json[i];
    if (row.length > 0) {
        const id = pkColumns.map(pk => row[headers.indexOf(pk)]).join('_');
        data[id] = row;
    }
}

return { data, headers };
}

async function compareExcelFiles() {
const outputContainer = document.getElementById('outputContainer');
outputContainer.innerHTML = '';

const dataset1Files = document.getElementById('excelDataset1').files;
const dataset2Files = document.getElementById('excelDataset2').files;

if (dataset1Files.length === 0 || dataset2Files.length === 0) {
    outputContainer.textContent = 'Please select files for both datasets.';
    return;
}

const dataset1FileMap = {};
for (let file of dataset1Files) {
    dataset1FileMap[file.name] = file;
}

for (let file2 of dataset2Files) {
    if (dataset1FileMap[file2.name]) {
        const file1 = dataset1FileMap[file2.name];

        const predefined = predefinedFiles[file2.name] || null;
        const workbook1 = await readExcel(file1);
        const workbook2 = await readExcel(file2);

        const sheet1 = workbook1.Sheets[workbook1.SheetNames[0]];
        const sheet2 = workbook2.Sheets[workbook2.SheetNames[0]];

        const headers1 = XLSX.utils.sheet_to_json(sheet1, { header: 1 })[0];
        const headers2 = XLSX.utils.sheet_to_json(sheet2, { header: 1 })[0];

        if (headers1.join('t') !== headers2.join('t')) {
            outputContainer.innerHTML += `<div class="table-title">${file2.name}</div><p>Headers do not match between the files.</p>`;
            continue;
        }

        let pkColumns = predefined || headers1.filter(header => /id/i.test(header));
        if (pkColumns.length === 0) {
            pkColumns = [headers1[0]]; // Default to the first column if no 'id' fields exist
        }

        const { data: dataset1 } = await parseSheet(sheet1, pkColumns);
        const { data: dataset2 } = await parseSheet(sheet2, pkColumns);

        const dataset1Keys = new Set(Object.keys(dataset1));
        const dataset2Keys = new Set(Object.keys(dataset2));

        const commonKeys = [...dataset2Keys].filter(id => dataset1Keys.has(id));
        const addedKeys = [...dataset1Keys].filter(id => !dataset2Keys.has(id));
        const deletedKeys = [...dataset2Keys].filter(id => !dataset1Keys.has(id));

        let resultHtml = '';
        let differencesFound = false;

        commonKeys.forEach(id => {
            const row1 = dataset1[id];
            const row2 = dataset2[id];

            if (row1.join('t') !== row2.join('t')) {
                if (!differencesFound) {
                    resultHtml += `<div class="table-title">${file2.name}</div>`;
                    resultHtml += '<table class="tbl"><thead><tr>';

                    resultHtml += '<th>';
                    headers1.forEach(header => {
                        resultHtml += `<span class="tblhdrClmName">${header}</span> `;
                    });
                    resultHtml += '</th>';

                    resultHtml += '</tr></thead><tbody>';
                    differencesFound = true;
                }

                const highlightedRow = highlightDifferences(row2, row1);
                resultHtml += `<tr><td>${highlightedRow}</td></tr>`;
            }
        });

        addedKeys.forEach(id => {
            if (!differencesFound) {
                resultHtml += `<div class="table-title">${file2.name}</div>`;
                resultHtml += '<table class="tbl"><thead><tr>';

                resultHtml += '<th>';
                headers1.forEach(header => {
                    resultHtml += `<span class="tblhdrClmName">${header}</span> `;
                });
                resultHtml += '</th>';

                resultHtml += '</tr></thead><tbody>';
                differencesFound = true;
            }

            const row = dataset1[id];
            const highlightedRow = row.map(cell => `<span class="ok">${cell}</span>`).join(';');
            resultHtml += `<tr><td>${highlightedRow}</td></tr>`;
        });

        deletedKeys.forEach(id => {
            if (!differencesFound) {
                resultHtml += `<div class="table-title">${file2.name}</div>`;
                resultHtml += '<table class="tbl"><thead><tr>';

                resultHtml += '<th>';
                headers1.forEach(header => {
                    resultHtml += `<span class="tblhdrClmName">${header}</span> `;
                });
                resultHtml += '</th>';

                resultHtml += '</tr></thead><tbody>';
                differencesFound = true;
            }

            const row = dataset2[id];
            const highlightedRow = row.map(cell => `<span class="notok">${cell}</span>`).join(';');
            resultHtml += `<tr><td>${highlightedRow}</td></tr>`;
        });

        if (differencesFound) {
            resultHtml += '</tbody></table>';
            outputContainer.innerHTML += resultHtml;
        }
    }
}

}

How can I ensure that when a value is missing in the new dataset, the previous value from the old dataset is displayed correctly?

I would appreciate any guidance on how to fix this issue while making minimal changes to the existing script. Thank you!