How to trim spreadsheet data after a specific keyword using ONLYOFFICE macro

7 June 2024By Eeshaan

Last month, we developed a macro to delete the contents of spreadsheet cells when a matching word was detected. In this blog post, we will use similar logic to create a macro that deletes all content in a cell after a specific word.

Building the macro

    const oWorksheet = Api.GetActiveSheet();
    const oRange = oWorksheet.GetSelection();
    const wordReference = "apple";

Starting off, we get the active worksheet and the selection in the oWorksheet and oRange variables, and set the word we want to search for, in the wordReference variable.

oRange.ForEach(function (range) {
        const cellValue = oWorksheet.GetRange(range).GetValue().trim();
        const containsIndex = containsWord(cellValue, wordReference);

Next, using a forEach loop, we iterate through the cells in the selected range, store the value of the cell in the cellValue variable, and pass the cellValue and the word to the containsWord method.

if (containsIndex !== -1) {
            const newValue = cellValue.substring(
                0,
                containsIndex + wordReference.length
            );
            oWorksheet.GetRange(range).SetValue(newValue);
        } else {
            console.log(
                "No instance found in the selection where deletion is applicable."
            );
        }

The condition ‘(containsIndex !== -1)’ checks for the specified word in the cell value. If the word is found, the cell value is updated to a sub-string of the original value, essentially deleting everything after the specified word.

    function containsWord(sentence, word) {
        const trimmedSentence = sentence.trim();
        const trimmedWord = word.trim();
        const index = trimmedSentence
            .toLowerCase()
            .indexOf(trimmedWord.toLowerCase());
        return index;
    }

The containsWord method is the core function in this macro. It takes the cell’s value and the word to search for, as parameters. It checks if the word is present in the cell by finding the index of the word within the cell content. The function returns the index where the word starts, or ‘-1’ if the word is not found.

The full macro code

Here is the entire code of the macro:

(function () {
    const oWorksheet = Api.GetActiveSheet();
    const oRange = oWorksheet.GetSelection();
    const wordReference = "apple";

    oRange.ForEach(function (range) {
        const cellValue = oWorksheet.GetRange(range).GetValue().trim();
        const containsIndex = containsWord(cellValue, wordReference);

        if (containsIndex !== -1) {
            const newValue = cellValue.substring(
                0,
                containsIndex + wordReference.length
            );
            oWorksheet.GetRange(range).SetValue(newValue);
        } else {
            console.log(
                "No instance found in the selection where deletion is applicable."
            );
        }
    });

    function containsWord(sentence, word) {
        const trimmedSentence = sentence.trim();
        const trimmedWord = word.trim();
        const index = trimmedSentence
            .toLowerCase()
            .indexOf(trimmedWord.toLowerCase());
        return index;
    }
})();

Now, let us see our macro in action.

That’s it! This handy macro is designed to trim cell values without much effort, enhancing your spreadsheet experience. With support for both desktop and web editors, this macro becomes an obvious choice in your spreadsheet toolkit.

Don’t miss the chance to harness the power of the ONLYOFFICE API. Our extensive library of API methods is your key to transforming your ideas into reality. If you have any questions or innovative concepts, we encourage you to share them with us. Your input is highly valued, and we are excited about the possibility of collaborating with you. Best of luck in your exploratory endeavors!