How to delete specific words in spreadsheets using an ONLYOFFICE macro

When working with spreadsheets, it’s common to encounter scenarios requiring the search for a particular word across the cells of a sheet, along with potential modifications or deletions of instances. In this blog post, we’ll develop a macro designed to search for and delete occurrences of a specific word within a spreadsheet.

Building the macro

const oWorksheet = Api.GetActiveSheet();
const oRange = oWorksheet.GetSelection();
const wordToCheck = "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 throughout the spreadsheet.

let index = 0;
  oRange.ForEach(function (range) {
    index++;
    const cellValue = oWorksheet.GetRange(range).GetValue();
    const contains = containsWord(cellValue, wordToCheck);

Next, we iterate through the range using a foreach loop, get the value at each cell in the selection, and pass it to the containsWord method. The containsWord() method takes two parameters: the value at the cell, and the word we want to search for.

    if (contains === 1) {
      //Uncomment the console.log() to view deleted cells in the browser's console.
      // console.log(`The sentence ${cellValue} contained the word you wanted to delete, and was deleted.`);
      oWorksheet.GetRange(range).Clear();
    } else if (contains === true) {
      oWorksheet
        .GetRange(range)
        .SetFillColor(Api.CreateColorFromRGB(204, 0, 0));
    }

Upon the completion of the containsWord() method, the subsequent action is determined by the returned value. If the value is 1, indicating an exact match, the cell is cleared. On the other hand, if the method returns true, signifying the presence of the target word within additional characters, the cell is highlighted.

function containsWord(sentence, word) {
    const trimmedSentence = sentence.trim();
    const trimmedWord = word.trim();
    if (trimmedSentence === trimmedWord) {
      return 1;
    } else {
      const regex = new RegExp("\\b" + trimmedWord + "\\b", "i");
      return regex.test(trimmedSentence);
    }
  }

The containsWord() method serves as the primary computational function in this macro. It accepts the cell’s value and the word to be searched, trimming it to eliminate any additional spaces. If the cell value precisely matches the target word with no additional content, it returns 1. However, if the cell value contains the target word along with other characters, such as within a sentence, it returns true.

The full macro code

(function () {
  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();
  const wordToCheck = "apple";
  let index = 0;
  oRange.ForEach(function (range) {
    index++;
    const cellValue = oWorksheet.GetRange(range).GetValue();
    const contains = containsWord(cellValue, wordToCheck);

    if (contains === 1) {
      //Uncomment the console.log() to view deleted cells in the browser's console.
      // console.log(`The sentence ${cellValue} contained the word you wanted to delete, and was deleted. `);
      oWorksheet.GetRange(range).Clear();
    } else if (contains === true) {
      oWorksheet
        .GetRange(range)
        .SetFillColor(Api.CreateColorFromRGB(204, 0, 0));
    }
  });

  function containsWord(sentence, word) {
    const trimmedSentence = sentence.trim();
    const trimmedWord = word.trim();
    if (trimmedSentence === trimmedWord) {
      return 1;
    } else {
      const regex = new RegExp("\\b" + trimmedWord + "\\b", "i");
      return regex.test(trimmedSentence);
    }
  }
})();

Lets see how our macro performs.

And there you have it, the Spreadsheet Word Delete Macro in action! This nifty tool is designed to effortlessly remove specific words, making your spreadsheet editing a breeze, save time, and enhance your spreadsheet workflow. Happy editing!

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!