VBA & Macro Problems

Automate Excel for the Web Cell Updates with Office Scripts

Bushra ParveenBushra Parveen Nov 18, 2026 868 views

Question details

Create a button-triggered automation that finds a database record and updates its location and item.

Automate Excel for the Web Cell Updates with Office Scripts
Product
Microsoft Excel for the web
Device & OS
not provided
Scenario
A user enters a search key and replacement values on a search worksheet, then selects a button to update a matching database row.
Observed behavior
The intended result is an updated matching row, with a visible error when no unique match is found.
Before you start

Open the workbook in Excel for the web and check that the Automate tab is available for your account. The example below uses worksheets named Search and Database and assumes the Database sheet has Location and Item headers in cells A1 and B1.

Solution 1Recommended

Create an Office Script and add a button to the workbook

Use this approach to search either database column for an exact match and replace both values in the matching row.

The example uses Search!B2 for the existing location or item, Search!B3 for the new location, and Search!B4 for the new item. Database column A contains locations and column B contains items.

Matching ignores capitalization and surrounding spaces. The script stops if the key matches more than one row, so use a unique key. It writes both replacement values as text and overwrites any formulas in the two target cells.

1
Set up the input cells

On the Search sheet, enter the existing location or item in B2, the new location in B3, and the new item in B4. On the Database sheet, place existing records in columns A and B below the headers.

2
Create the script

Select Automate > New Script, replace the sample code with the following, and save it with a name such as Update Database: function main(workbook: ExcelScript.Workbook): string { const search = workbook.getWorksheet("Search"); const database = workbook.getWorksheet("Database"); if (!search || !database) throw new Error("Create sheets named Search and Database."); const key = search.getRange("B2").getText().trim().toLowerCase(); const location = search.getRange("B3").getText().trim(); const item = search.getRange("B4").getText().trim(); if (!key || !location || !item) throw new Error("Enter a search key, new location, and new item in Search!B2:B4."); const used = database.getUsedRange(true); if (!used) throw new Error("The Database sheet is empty."); const lastRow = used.getRowIndex() + used.getRowCount(); if (lastRow < 2) throw new Error("No database records were found."); const rows = database.getRangeByIndexes(1, 0, lastRow - 1, 2).getTexts(); const matches: number[] = []; for (let i = 0; i < rows.length; i++) { if (rows[i][0].trim().toLowerCase() === key || rows[i][1].trim().toLowerCase() === key) { matches.push(i + 1); } } if (matches.length === 0) throw new Error("No matching location or item was found."); if (matches.length > 1) throw new Error("More than one row matches. Use a unique search key."); database.getRangeByIndexes(matches[0], 0, 1, 2).setValues([[location, item]]); return "Updated database row " + (matches[0] + 1) + "."; }

3
Test the update

In the Automate pane, select the saved script and choose Run. Check the matching row on Database; a missing or duplicate match produces an error instead of changing a row.

4
Add a run button

In Automate, open the saved script's details and select Add button. Place the button on the Search sheet, then select it after entering the next search key and replacement values.

Create an Office Script and add a button to the workbook
Office Scripts availability: Office Scripts and workbook buttons depend on your Microsoft 365 account and organization settings. If Automate or Add button is unavailable, check with your administrator.
Free Microsoft Office alternative

Work with spreadsheet records in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface for viewing and editing database records. Open compatible Excel files to migrate everyday spreadsheet work; Microsoft Office Scripts and their buttons require Excel and are not fixed by switching to WPS.

Find and edit location and item records in a spreadsheetCompatible with Microsoft Excel formats, including XLSXFamiliar interface for everyday spreadsheet workFree and lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Can I use VBA to run this automation in Excel for the web?

Excel for the web does not run VBA macros. Use the Automate tab to create an Office Script, or run VBA in a supported desktop version of Excel.

What happens if the search key matches multiple records?

The example stops and reports an error without updating a row. Enter a location or item in Search!B2 that matches only one database row.

Can I update only the location or only the item?

The example requires values in both Search!B3 and B4 and replaces both database cells. To update just one field, modify the validation and the final setValues call.

Why can't I see the Automate tab or add a script button?

Office Scripts availability depends on your Microsoft 365 license and organization settings. Check your account and ask your administrator whether Office Scripts are enabled.