Clean Spreadsheet Text with a Reusable ONLYOFFICE Macro

For repeatable spreadsheet cleanup, create a small JavaScript macro in the Spreadsheet Editor, target only the text range you intend to change, and test it on a duplicate workbook first. The example below removes nonprinting characters and trims extra whitespace from an email column while keeping every row in place.

ONLYOFFICE macros run inside the editor and use its Office JavaScript API. The official documentation still describes the workflow as opening View > Macros, writing or pasting code, and running it in the editor; no recent change identified in the current documentation alters this basic process. The macro itself is an illustrative, reusable script, not a report of a software test. See the ONLYOFFICE macro quick start and Spreadsheet Editor View tab reference.

Prepare a safe copy and define the cleanup

Suppose a workbook contains a contact list with a header in row 1 and email addresses in column B, from B2 through B500. A few imported values may contain line breaks, tabs, or leading and trailing spaces. This example cleans that one text column only. Adjust the range to match your sheet before running the code.

  • Save a separate copy of the workbook, or duplicate the worksheet. Keep the original intact until you have checked the output.
  • Confirm the selected range contains raw text, not formulas, IDs that rely on spaces, or data that must preserve exact spacing.
  • Keep the header row out of the range. The macro does not detect headers or decide which columns are safe to change.

Use a column-specific range such as B2:B500 instead of the entire used range. Cleaning every cell indiscriminately can change names, addresses, codes, or formula results in ways you did not intend.

Illustrative spreadsheet interface with the View tab selected and the Macros command above a sample worksheet.
In this illustrative editor view, the View tab and Macros command appear above the worksheet. Open it after making a working copy and confirming which column contains the text to clean.

Open the macro editor and add the script

Open the workbook in the ONLYOFFICE Spreadsheet Editor. Choose View > Macros to open the macro editor, then create a macro or replace the starter code in the editor. ONLYOFFICE macros use JavaScript syntax and the Office API; they are not VBA macros. The editor’s macro workflow and supported API methods are described in the official macros getting-started guide.

Paste this example, then change B2:B500 to your actual text-only range:

(function () {
    var sheet = Api.GetActiveSheet();
    var range = sheet.GetRange("B2:B500");
    var rows = range.GetValue();
    var functions = Api.WorksheetFunction;

    for (var i = 0; i < rows.length; i++) {
        var value = rows[i][0];
        if (typeof value === "string") {
            value = functions.CLEAN(value);
            rows[i][0] = value.replace(/^\s+|\s+$/g, "")
                              .replace(/\s{2,}/g, " ");
        }
    }

    range.SetValue(rows);
})();

The macro reads the range into a two-dimensional array, processes string cells, and writes the array back to that same range. CLEAN removes nonprinting characters; the two regular-expression replacements remove whitespace at the edges and reduce repeated whitespace inside a string to one space. The API reference confirms GetValue can return a two-dimensional array for a range and that SetValue accepts arrays; the GetValue, SetValue, and CLEAN references document these calls.

Because the script writes values back, do not point it at cells containing formulas: formula cells may be replaced by their current calculated values. It also does not remove duplicate records, standardize capitalization, validate whether an address is deliverable, or change phone-number formats. Those are separate cleanup rules and should be designed and tested independently.

Illustrative macro editor showing verified ONLYOFFICE API calls to get the active sheet, read a cell value, and set a value, with a Run button.
In this illustrative macro editor view, the code area and Run control sit above the active spreadsheet. The article’s cleanup script uses the documented range read and write methods shown here as a simplified API example.

Run it on a controlled range

  1. Check the range literal one more time. If the list ends at row 240, for example, use B2:B240 rather than processing hundreds of unused rows.
  2. Click Run in the macro editor. If the editor reports a syntax error, review quotes, parentheses, and braces before changing the range or adding more logic.
  3. Return to the worksheet and inspect several cells, including a value that needed cleanup, a value already in the correct format, and a blank cell.

Starting with a narrow range makes the effect easier to understand. Once the output is correct, you can reuse the macro on another sheet by editing the range deliberately. Do not assume that a column letter or row boundary remains valid in a different workbook.

If the Macros command is missing or the code cannot run, check whether macros are enabled in the editor or disabled by the host application’s configuration. In the online editor, an administrator may control macro availability; the product’s Spreadsheet Editor advanced settings describe the user-facing macro options. Avoid enabling automatic execution for files from unknown sources; run only code you understand.

Illustrative contact worksheet with a Name, Email, Department, and Phone column and a visible sample of contact rows.
This illustrative contact sheet shows a practical kind of data for a text-cleaning macro, provided the selected range contains only the intended text field and excludes the header.

Verify the result before reusing the macro

Compare the cleaned column with the original copy rather than judging success by appearance alone. Confirm that each nonblank email is still on the same row, no contact disappeared, and the only differences are the characters and spacing you intended to normalize. Check a sample near the top, middle, and end of the chosen range. Search for a space at the beginning or end of a value if your editor makes that visible, and inspect imported values that previously contained line breaks or control characters.

Also compare the row count and any dependent formulas, filters, or lookups that use the column. If a formula result changed unexpectedly, undo the edit or restore the working copy; the macro should be restricted to raw text cells. For a larger or business-critical workbook, compare the full cleaned column against the backup or use a separate output column first so changes remain reviewable.

Illustrative spreadsheet showing Original and Cleaned email columns side by side with the same row count and a separate Copy of Original worksheet tab.
This illustrative sheet places original and cleaned email columns beside each other with the same displayed row count; compare your actual values before relying on the cleaned column.

Keep the cleanup repeatable

After the first run is correct, save the workbook so the macro remains available with the document according to the editor’s macro workflow. A macro stored in one workbook should not be assumed to appear automatically in every new file. For recurring imports, keep a clean template or a protected source copy, document the range the macro expects, and update it when the sheet layout changes. ONLYOFFICE macros are useful for deterministic cleanup rules; they cannot decide whether a value is factually correct or whether a change is appropriate for your dataset.

The reliable pattern is simple: preserve a source copy, target a known range, make one defined transformation, and compare the result before sharing or importing the data elsewhere.

Leave a Comment

Clear Nextcloud’s File Locking Warning with Redis

Enable reliable Nextcloud file locking, choose the right Redis or Valkey backend, and verify the warning clears without risking concurrent file changes.

What It Takes to Target an A+ Nextcloud Security Rating

What It Takes to Target an A+ Nextcloud Security Rating

Fix common Nextcloud scan findings with current updates, HTTPS and HSTS, precise reverse-proxy trust, and checks against the public server.

Automate Consistent Nextcloud Backups with Restic and Cron

Automate Consistent Nextcloud Backups with Restic and Cron

Back up Nextcloud’s files and MariaDB together, schedule encrypted Restic snapshots with cron, and verify recovery without risking the live server.

Enable Nextcloud Server-Side Encryption with Measured Impact

Configure Nextcloud SSE for the storage that needs it, protect encryption keys, and measure transfer and CPU impact before encrypting existing files.

Add Elasticsearch Full-Text Search to Nextcloud

Connect Nextcloud’s file search to Elasticsearch, index document contents, and choose a setup that fits your server and security needs.

Set a Reliable Expiration Policy for Nextcloud Deleted Files

Set a Reliable Expiration Policy for Nextcloud Deleted Files

Configure Nextcloud to remove deleted files after a defined retention period, schedule background jobs reliably, and avoid commands that purge every user’s trash.

Clear Nextcloud’s Missing Database Indexes Warning

Clear Nextcloud’s Missing Database Indexes Warning

Run Nextcloud’s OCC repair safely, preview the database changes, and check why the warning might remain.

Raise Nextcloud’s Web Upload Limit Past 2 GB

Raise Nextcloud’s Web Upload Limit Past 2 GB

Allow larger Nextcloud browser uploads by aligning PHP, Nginx or Apache, temporary storage, and timeout settings—and verify which limit is blocking the file.

Clear Nextcloud’s PHP Memory Warning at the Web Server

Set the PHP memory limit Nextcloud actually uses, restart the right service, and confirm the 512 MB setting in Admin Overview.

Connect an S3-Compatible Bucket to Nextcloud External Storage

Mount a compatible object-storage bucket in Nextcloud using its S3 backend, with the right endpoint, credentials, access scope, and verification steps.