Excel as a Database: Updating, Deleting, and Merging Rows Over One API

Most teams don't think of Excel as a database. It's the thing you open before a meeting, not something you'd call from production code. But look at how a lot of internal tools actually work: a workbook sits in a shared drive, a script or a no-code flow reads it, changes a few rows, and writes it back. Nobody built that on purpose. It accumulated, one manual edit at a time, until the spreadsheet became the source of truth nobody wants to admit is the source of truth.

The problem isn't that Excel is a bad database. It's that the operations you'd actually want (updating a row when a status changes, deleting rows that failed validation, merging duplicate entries after two systems both wrote to the same sheet) still get done by hand in most shops. Someone opens the file, finds the row, edits it, saves it, and hopes nobody else had it open at the same time.

PDF4me's Excel API turns three of the most common versions of that work, update, delete, merge, into calls you can drop into a pipeline. No macro, no VBA, no "don't touch that cell" tribal knowledge. Here's what each one actually does, the request shape behind it, and where it fits.

Authenticating your requests

Every call below hits the same base URL, https://api.pdf4me.com, under the /api/v2/ path, authenticated with an API key from your PDF4me dashboard. Full setup is in the Connect to PDF4me API guide; the short version is a Basic auth header carrying your Base64-encoded key.

Updating rows without opening the file

The Update Rows endpoint (POST office/ApiV2Excel/ExcelUpdateRows) takes an existing Excel file and a set of new values, and writes those values into specific rows, targeted either by table structure or by direct coordinates. Coordinate-based targeting is what you want when you know exactly which cell you're touching, row 14, column C. Table-based targeting is what you want when the sheet has headers and you'd rather match by field name than by position, which matters the moment someone inserts a column and every hardcoded coordinate in your script quietly points at the wrong data.

import base64
import requests

API_KEY = "YOUR_API_KEY"
BASE_URL = "https://api.pdf4me.com/api/v2/"

with open("data.xlsx", "rb") as f:
    doc_content = base64.b64encode(f.read()).decode("utf-8")

payload = {
    "document": {"Name": "data.xlsx"},
    "docContent": doc_content,
    "updateRowsToExcelAction": {
        "jsonInput": '[{"Name": "John", "Age": 31}]',
        "worksheetName": "Sheet1",
        "tableName": "SalesTable",
        "excelRowNumber": 5,
        "convertNumericAndDate": True,
        "cultureName": "en-US"
    }
}

headers = {
    "Content-Type": "application/json",
    "Authorization": f"Basic {API_KEY}"
}

response = requests.post(BASE_URL + "office/ApiV2Excel/ExcelUpdateRows", json=payload, headers=headers)
result = response.json()

tableName switches the call into table-based mode; leave it empty and use insertFromRow / insertFromColumn instead for coordinate-based mode. The response comes back as a Base64-encoded document alongside fileName, success, and errorMessage fields, the same shape across all three endpoints below.

The automation-platform versions add the parts a developer would otherwise have to build by hand. In Make, the module does automatic header matching, type conversion, and culture-specific formatting, so a JSON payload from another app lands in the right cell as the right data type instead of as a string that happens to look like a number. Power Automate does the same header matching and type conversion, aimed at flows where a form submission or a database row needs to land back in a shared workbook with nobody touching it manually. Zapier's action modifies existing spreadsheet rows in place from JSON, exactly the shape you'd reach for to keep CRM, inventory, or reporting data synced against a spreadsheet without manual editing. And in n8n, the node matches JSON arrays to worksheet headers while giving you control over the starting row and column offsets, and it preserves the workbook's existing formulas while it refreshes the data around them. That last detail decides whether a self-hosted workflow is safe to run against a sheet someone else already built calculations into.

Picture the workflow this actually replaces: a support ticket closes, and someone is supposed to open the shared "Active Accounts" workbook finance still swears by, find the account row, and flip a status column from "Open" to "Resolved." Multiply that by however many tickets close in a day, and the manual version is where status columns quietly go stale. Wiring the ticket-close event straight into an Update Rows call means the workbook reflects reality the moment the ticket does, not whenever someone remembers to open it.

Deleting rows without breaking the ones after them

Deleting rows sounds simpler than updating them, right up until you've done it wrong once. Delete row 5, and every row after it shifts up by one. Delete rows 5 and 8 in that order, and row 8 was never actually row 8 by the time your script got to it.

The Delete Rows endpoint (POST office/ApiV2Excel/ExcelDeleteRows) removes rows by index range, scoped to a specific worksheet:

payload = {
    "document": {"Name": "data.xlsx"},
    "docContent": doc_content,
    "deleteRowsToExcelAction": {
        "worksheetName": "Sheet1",
        "fromRow": 5,
        "toRow": 10
    }
}

response = requests.post(BASE_URL + "office/ApiV2Excel/ExcelDeleteRows", json=payload, headers=headers)

The endpoint, and every automation-platform version, handles the ordering problem the same way: bottom to top. In Zapier, you pass a range syntax like "1-5,10,15-20" and bottom-to-top processing prevents the index-shifting error described above. Make uses the same comma-separated range syntax with safe bottom-to-top deletion, built for cleanup work: pulling failed rows out of a report, trimming a sheet before it goes to a client, or clearing rows that a retention schedule says shouldn't stick around. Power Automate removes single rows, multiple rows, or comma-separated ranges from any worksheet inside a flow. n8n's version removes single rows, ranges, or mixed lists from any worksheet, without shifting the rows you didn't ask it to touch.

Don't write your own row-deletion loop unless you enjoy debugging off-by-one errors in someone else's spreadsheet. A nightly QA job that flags failing rows and needs them out of the working sheet before it goes anywhere else is the textbook case: a single Delete Rows call, driven by whatever range your validation step already produced, removes the guesswork along with the rows.

Merging duplicate rows without a pivot table

The Merge Rows endpoint (POST office/ApiV2Excel/ExcelMergeRows) is really a deduplication tool wearing a merge-rows name. It targets a worksheet or table, groups rows by a key column you choose, and consolidates the values that differ across the group instead of picking one row and discarding the rest:

payload = {
    "document": {"Name": "data.xlsx"},
    "docContent": doc_content,
    "mergeRowsToExcelAction": {
        "jsonInput": '[{"Name": "John", "Age": 30}]',
        "worksheetName": "Sheet1",
        "tableName": "CustomerTable",
        "convertNumericAndDate": True,
        "cultureName": "en-US"
    }
}

response = requests.post(BASE_URL + "office/ApiV2Excel/ExcelMergeRows", json=payload, headers=headers)

That consolidation behavior is where the automation-platform versions get specific. Make's implementation groups on key columns and does value consolidation using a semicolon-separated format, then removes the duplicates, built for comprehensive data deduplication across a sheet two systems have both been writing to. Zapier's action groups on key columns to combine customer orders, product variants, or category data that landed as separate rows when they should have been one. Power Automate groups duplicate rows by a key column and combines the differing values with semicolon separators inside the flow. n8n's node groups by key columns, combines values with the same deduplication logic, and exports the result to multiple formats, useful when the merged sheet needs to go somewhere other than back into Excel.

Merge Rows is the one of the three that most often gets skipped in favor of a manual pivot table and a lot of copy-paste. It's also the one where doing it by hand is most likely to silently drop a value nobody notices missing until a customer asks about it. Two intake forms feeding the same customer list, one row with a phone number and the other with a billing address for the same person, is the scenario this exists for: group on the customer key, let the endpoint consolidate the differing fields, and nobody has to make that judgment call thirty times before lunch.

Picking REST or a no-code platform, and why it's not really a hard choice

None of this requires picking a side. The REST endpoints and the no-code platform actions do the same underlying work, so the choice comes down to where the trigger for the row change actually lives, not which tool is "better." If you're already inside a codebase, the REST API is the direct path: authenticate once and call the endpoint from wherever your pipeline already runs. If the trigger lives somewhere else, a form submission, a webhook, a scheduled check, that's what Power Automate, Make, Zapier, and n8n connections exist for, so a team without a developer on hand for that one workflow still gets the same row-level behavior through a flow they can read and edit themselves.

Here's the honest limit: none of these three run inside a single combined call. Update, delete, and merge are three separate requests, chained in whatever order your workflow needs, not one operation that does all three at once. On the REST side, that's three sequential calls, exactly as simple and exactly as much your own responsibility to sequence correctly as it sounds.

Spreadsheets aren't going away as a business interface. Realistically, they shouldn't. The teams that get the most out of an Excel API like this one aren't the ones who stopped using Excel. They're the ones who stopped treating every row edit as a task for a human.

Full runnable samples in more languages (C#, Java, Node.js) live in the pdf4me-api-samples repo under Excel/Update Rows, Excel/Delete Rows, and Excel/Merge Rows. New to the API: start with Getting Started with API Portal.

Website: pdf4me.com
Documentation: docs.pdf4me.com
Developer portal: dev.pdf4me.com