Invalid Range Error for single range in the worksheet

[AlexanderTunick]

Hi. Observing some error that doesn't spoil my file structure nohow but being outputted in the console for an unknown reason.

Error: Invalid Range for: A1:G1. Some cells in this range are already included in another merged cell range: A1:G1.

Condition: I have a simple file structure where: (screenshot: https://monosnap.com/file/ZPvKPi9WFDlBH52k0ShTbiHUKLEPmd) columns range: A-G (7) cells in A1-G1 are merged (NOTE: this the only one merge in the worksheet I use)

ws.cell(1, 1, 1, 7, true)
        .date(new Date());

So there is an impossibility of any merge conflict, what can be the reason?

In you need any more information about it, I will add.

[natergj]

If you could provide a full gist that demonstrates the issue I can take a look at it. One thing to look for is if you are trying to write data to any single cell in that range. With that range, if somewhere in the code you have ws.cell(1, 2).string('something'), for example, you would see that warning because you are trying to write a value to a cell that is part of a merged block.

[AlexanderTunick]

Researched an issue again but didn't find any relevant place where can be an intention to write into that field over and over again. Provide you with this simple function I have written for file filling

async function excelDataFiller(rowNumber) {
    const startIndex = 2;
    const j = rowNumber + 1;
    const numDiff = getCountDiff(storage.allCountResults[rowNumber].csdbCount, storage.allCountResults[rowNumber].esCount);
    const perDiff = (storage.allCountResults[rowNumber].csdbCount - storage.allCountResults[rowNumber].esCount) / storage.allCountResults[rowNumber].csdbCount;

    /* Column parameters */
    ws.column(1).setWidth(30);
    ws.column(2).setWidth(27);

    /* Header */
    ws.cell(1, 1, 1, 7, true)
        .date(new Date());
    ws.cell([
        ws.cell(2, 1).string('Institution').style(styles.header),
        ws.cell(2, 2).string('CD Select').style(styles.header),
        ws.cell(2, 3).string('CSDB').style(styles.header),
        ws.cell(2, 4).string('CD').style(styles.header),
        ws.cell(2, 5).string('Diff').style(styles.header),
        ws.cell(2, 6).string('Variance').style(styles.header),
        ws.cell(2, 7).string('Result').style(styles.header)
    ]);

    /* Data */
    ws.cell([
        ws.cell(j + startIndex, 1).string(storage.allCountResults[rowNumber].institutionType),
        ws.cell(j + startIndex, 2).string(storage.allCountResults[rowNumber].selectName),
        ws.cell(j + startIndex, 3).number(storage.allCountResults[rowNumber].csdbCount),
        ws.cell(j + startIndex, 4).number(storage.allCountResults[rowNumber].esCount),
        ws.cell(j + startIndex, 5).number(numDiff),
        ws.cell(j + startIndex, 6).number(perDiff || 0).style(styles.percentage), // If 0 in both counts it returns NaN, so we just write 0 down to the file.
        ws.cell(j + startIndex, 7).string(storage.allCountResults[rowNumber].testStatus)
    ])
}

async function excelFileGenerator() {
    for (let i = 0; i < storage.allCountResults.length; i++) {
        excelDataFiller(i)
    }
    wb.write('./reports/ConnectedDataCounts.xlsx');
}

Just to refresh, I have plenty of these notifications:

Invalid Range for: A1:G1. Some cells in this range are already included in another merged cell range: A1:G1.
Invalid Range for: A1:G1. Some cells in this range are already included in another merged cell range: A1:G1.

[natergj]

Thanks for the code snippet, @AlexanderTunick . The issue you are seeing with the range is caused by the following being included in your excelDataFiller method and being called on every row iteration. This should only be called once, so it should be moved to outside that iteration.

ws.cell(1, 1, 1, 7, true)
        .date(new Date());

I'm not sure where this syntax came from, but the ws.cell method does not take an array as an argument

    /* Data */
    ws.cell([   // <-- This line and the associated "])" should be removed.
        ws.cell(j + startIndex, 1).string(storage.allCountResults[rowNumber].institutionType),
        ws.cell(j + startIndex, 2).string(storage.allCountResults[rowNumber].selectName),
        ws.cell(j + startIndex, 3).number(storage.allCountResults[rowNumber].csdbCount),
        ws.cell(j + startIndex, 4).number(storage.allCountResults[rowNumber].esCount),
        ws.cell(j + startIndex, 5).number(numDiff),
        ws.cell(j + startIndex, 6).number(perDiff || 0).style(styles.percentage), // If 0 in both counts it returns NaN, so we just write 0 down to the file.
        ws.cell(j + startIndex, 7).string(storage.allCountResults[rowNumber].testStatus)
    ])

Also, if you are performing asynchronous actions in excelDataFiller, you'll want to be sure to add await when calling that method. Otherwise, the wb.write method may be called before all of the data is added to the workbook.

I've updated your code and put it into the following gist. That should take care of the issues you are seeing. Let me know how it turns out.

https://gist.github.com/natergj/7dd897eec10068d227eea6de5bdaf9b1

[AlexanderTunick]

@nateratseas Thank you very much. I see the point and having followed your kindly provided snippet with changes I've run tests and got a successful resolution on it. Yes, everything works. Also considered your recommendation on asynchronous actions and added await

Thank you one more time.