Automate Excel for the Web Cell Updates with Office Scripts
Question details
Create a button-triggered automation that finds a database record and updates its location and item.

- 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.
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.
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.
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.
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) + "."; }
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.
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.

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.

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.




