Skip to main content

Walkthrough: Building a Salesforce Data Editor

This walkthrough builds a complete bidirectional data editor — query records from Salesforce into Excel, edit them in the spreadsheet, and save changes back — using KSExcel from kstone/api.

The finished component is queryBuilderLwc, which ships in the Keelstone Samples package. Reading this walkthrough first will help you understand how the pieces fit together before adapting the pattern to your own use case.

What we're building​

┌─────────────────────────────────────────────┐
│  SOQL QUERY                                 │
│  ┌─────────────────────────────────────┐    │
│  │ SELECT Id, Name, Phone FROM         │    │
│  │ Account WHERE Industry = ...        │    │
│  └─────────────────────────────────────┘    │
│  PASTE TO CELL                              │
│  ┌──────────────────────┐  ┌─────────┐     │
│  │ A1 or Sheet2!B3      │  │   📍    │     │
│  └──────────────────────┘  └─────────┘     │
│  ☑ Auto-fit columns   ☑ Hide ID column      │
│  ┌───────────────────┐                      │
│  │  ▶  Run Query     │                      │
│  └───────────────────┘                      │
│  ✓ 42 of 150 records written                │
│  ┌─────────────────────────┐                │
│  │  ↑ Save to Salesforce   │                │
│  └─────────────────────────┘                │
└─────────────────────────────────────────────┘

Salesforce → Excel: the user writes any SOQL query, optionally pins a cell target, and clicks Run Query. Keelstone fetches the records, writes them as a formatted Excel Table with picklist dropdowns and required-field highlighting, and returns the table name.

Excel → Salesforce: after editing cells in the spreadsheet, the user clicks Save to Salesforce. Keelstone reads the table back, calls ksSaveRecords for each modified row, and writes per-row error messages into a _Error column.

Step 1 — Create the LWC​

Create a new Lightning Web Component and extend KSExcel:

// myDataEditor.js
import { KSExcel } from 'kstone/api';
import { api, track } from 'lwc';
import { ShowToastEvent } from 'lightning/platformShowToastEvent';

export default class MyDataEditor extends KSExcel {
    @api keelstoneSessionId;  // required — wired by the flow

    @track soql          = '';
    @track activeRange   = '';
    @track isRunning     = false;
    @track isSaving      = false;
    @track errorMessage  = '';
    @track statusMessage = '';
    @track tableName     = '';   // set after a successful Run
}

@api keelstoneSessionId must be declared on your class even though the property is defined in KSExcel. Salesforce flow screen wiring only populates @api properties on the component class itself — not inherited ones. See Flow Wiring.

Step 2 — Read the current Excel selection (range picker)​

Before running a query, the user can click a pin button to capture the currently selected cell in Excel. ksGetRange() returns the address:

async handleGetRange() {
    this.activeRange = await this.ksGetRange();
    // e.g. "Sheet2!C5"
}

ksGetRange sends a message to the Keelstone taskpane, which asks Excel for the current selection and returns the sheet-qualified address. The value populates the text input so the user can see where the results will land.

The user can also type an address directly (A1, Sheet2!B3). If the input is left blank, the results write to A1 on the active sheet.

Step 3 — Run the SOQL query​

ksQuery is the core of the Salesforce → Excel direction. It:

  1. Sends the SOQL to the Keelstone server
  2. The server executes it using the user's stored access token
  3. The server fetches field metadata to identify picklist fields and required fields
  4. Writes the results to Excel as a formatted Table:
    • TableStyleMedium2 styling
    • In-cell dropdown validation for picklist fields
    • Yellow header highlighting for required fields
    • Optional auto-fit and Id column hiding
  5. Returns the table name and record count
async handleRun() {
    this.isRunning    = true;
    this.errorMessage = '';
    this.statusMessage = '';
    this.tableName    = '';

    try {
        const result = await this.ksQuery(
            this.soql.trim(),
            this.activeRange || undefined,   // undefined → defaults to A1
            { hideId: true, autofit: true }
        );

        this.tableName = result.tableName;   // needed for Save step

        // result.totalSize may exceed result.rowCount if Salesforce paginates
        const { rowCount, totalSize } = result;
        this.statusMessage = totalSize > rowCount
            ? `${rowCount.toLocaleString()} of ${totalSize.toLocaleString()} records written`
            : `${rowCount.toLocaleString()} record${rowCount !== 1 ? 's' : ''} written`;
    } catch (err) {
        this.errorMessage = err.message ?? 'An unexpected error occurred.';
    } finally {
        this.isRunning = false;
    }
}

Why store tableName? Keelstone assigns each written table a unique name (e.g. SF_Account_1714000000). The Save step uses this name to locate the table when reading it back. The Save button stays disabled until tableName is set — ensuring the user always runs a fresh query before saving.

Pagination note: Salesforce caps SOQL results at 2,000 rows by default. ksQuery respects this limit. If your query could return more rows, always include a LIMIT clause and show the user the totalSize so they know results may be truncated.

Step 4 — Let the user edit in Excel​

At this point the data is in Excel. The user can edit any cell — changing a picklist value via the in-cell dropdown, updating a phone number, correcting a name. Nothing happens in the LWC during this step; the user is just working in the spreadsheet normally.

The _Error column (added automatically by ksSaveRecords in the next step) is hidden until a save attempt. Required fields are highlighted in yellow so users know not to clear them.

Step 5 — Save changes back to Salesforce​

When the user clicks Save, the component reads the table with ksSaveTables and then calls ksSaveRecords for each table whose name starts with SF_ (the prefix Keelstone assigns to SOQL result tables):

async handleSave() {
    this.isSaving = true;
    this.errorMessage = '';

    try {
        const { tables } = await this.ksSaveTables();

        let saved = 0, errors = 0;

        for (const { tableName, headers, rows } of (tables || [])) {
            // Only process tables that came from Keelstone SOQL queries
            if (!tableName?.startsWith('SF_')) { continue; }

            // Table name format: SF_<SObjectType>_<timestamp>
            // Strip the prefix and suffix to get the sObject API name
            const sobject = tableName.replace(/^SF_/, '').replace(/_\d+$/, '');

            // Build record objects, skipping entirely empty rows
            // _rowIndex maps each record back to its original Excel row number
            const records = rows
                .map((row, originalIndex) => ({ row, originalIndex }))
                .filter(({ row }) => row.some(cell => cell !== '' && cell !== null))
                .map(({ row, originalIndex }) => {
                    const rec = { _rowIndex: originalIndex };
                    headers.forEach((h, j) => { rec[h] = row[j] ?? ''; });
                    return rec;
                });

            if (!records.length) { continue; }

            const { results } = await this.ksSaveRecords(sobject, records);

            saved  += results.filter(r =>  r.success).length;
            errors += results.filter(r => !r.success).length;

            // Write error messages back into the _Error column in Excel
            // Successful rows get an empty string (clears any previous error)
            const errorMap = new Map(results.map(r => [r.rowIndex, r]));
            const rowErrors = rows.map((_, i) => {
                const r = errorMap.get(i);
                if (!r) return '';
                return r.success ? '' : (r.errors?.[0]?.message || 'Save failed');
            });

            await this.ksWriteTableErrors(tableName, rowErrors);
        }

        this.dispatchEvent(new ShowToastEvent({
            title:   errors > 0 ? 'Saved with errors' : 'Save complete',
            message: errors > 0
                ? `${saved} row${saved !== 1 ? 's' : ''} saved, ${errors} had errors — see the _Error column.`
                : `${saved} row${saved !== 1 ? 's' : ''} saved successfully.`,
            variant: errors > 0 ? 'warning' : 'success',
        }));
    } catch (err) {
        this.errorMessage = err.message ?? 'Save failed.';
    } finally {
        this.isSaving = false;
    }
}

How ksSaveRecords works​

ksSaveRecords(sobjectType, records) sends all records to the Keelstone server in a single call. The server issues an Apex Database.update (or Database.upsert if the record has no Id) with allOrNothing = false, so one row failing does not roll back the others.

Each result in the returned results array contains:

  • rowIndex — matches _rowIndex on the input, letting you map results back to Excel rows
  • success — boolean
  • errors — array of { message, statusCode } objects

The _Error column​

ksWriteTableErrors(tableName, rowErrors) writes a string array into the _Error column of the named table. The column is added by Keelstone automatically on first save; you do not need to include it in your SOQL or create it manually. Rows that saved successfully receive an empty string, clearing any previous error.

This gives users immediate, in-context feedback — they can see exactly which rows failed and why, correct the values, and click Save again.

Step 6 — Wire computed properties and the template​

get isRunDisabled()  { return !this.soql.trim() || this.isRunning; }
get isSaveDisabled() { return !this.tableName || this.isSaving; }
get hasResults()     { return !!this.statusMessage; }
get runBtnLabel()    { return this.isRunning ? 'Running…' : 'Run Query'; }
get saveBtnLabel()   { return this.isSaving  ? 'Saving…'  : 'Save'; }

isSaveDisabled is tied to tableName. Save is only enabled after a successful Run — this prevents the user from saving stale data from a previous session where the table structure might not match the current query.

<!-- myDataEditor.html -->
<template>
    <div>
        <label>SOQL Query</label>
        <textarea value={soql} onchange={handleSoqlChange}
                  onkeydown={handleKeyDown}></textarea>

        <label>Paste to Cell</label>
        <input type="text" value={activeRange} onchange={handleRangeChange}
               placeholder="A1 or Sheet2!B3" />
        <button onclick={handleGetRange} disabled={isRunning}>📍</button>

        <label><input type="checkbox" checked={optAutofit} onchange={handleOptAutofit} /> Auto-fit columns</label>
        <label><input type="checkbox" checked={optHideId}  onchange={handleOptHideId}  /> Hide ID column</label>

        <button onclick={handleRun} disabled={isRunDisabled}>{runBtnLabel}</button>
        <button onclick={handleSave} disabled={isSaveDisabled}>{saveBtnLabel}</button>

        <template if:true={hasResults}>
            <p>{statusMessage}</p>
        </template>
        <template if:true={errorMessage}>
            <p class="error">{errorMessage}</p>
        </template>
    </div>
</template>

Step 7 — Add the keyboard shortcut​

Ctrl+Enter (or Cmd+Enter on Mac) is a standard "run query" shortcut in every SQL tool. Adding it takes three lines:

handleKeyDown(e) {
    if (e.key === 'Enter' && (e.ctrlKey || e.metaKey)) {
        e.preventDefault();
        if (!this.isRunDisabled) this.handleRun();
    }
}

Wire it to onkeydown on the textarea as shown in the template above.

Step 8 — Wire it to a Flow​

.js-meta.xml​

<?xml version="1.0" encoding="UTF-8"?>
<LightningComponentBundle xmlns="http://soap.sforce.com/2006/04/metadata">
    <apiVersion>62.0</apiVersion>
    <isExposed>true</isExposed>
    <targets>
        <target>lightning__FlowScreen</target>
    </targets>
    <targetConfigs>
        <targetConfig targets="lightning__FlowScreen">
            <property name="keelstoneSessionId" type="String"
                      label="Keelstone Session ID" />
        </targetConfig>
    </targetConfigs>
</LightningComponentBundle>

Flow setup​

  1. Create a new Screen Flow
  2. Add a Text variable KeelstoneSessionId — Available for input, not for output
  3. Add a Screen element with your myDataEditor component
  4. Set the keelstoneSessionId property to {!KeelstoneSessionId}
  5. The flow can be a single-screen flow — there are no output variables to collect

Keelstone Action record​

FieldValue
LabelData Editor
Action TypeFlow
Action Targetyour flow's API name
Document TypeExcel / Sheets
Launch TargetTaskpane (recommended for data editing — stays open while the user works)
Active✓

Complete component reference​

Here is the full JS for queryBuilderLwc as it ships in the Samples package:

import { KSExcel } from 'kstone/api';
import { api, track } from 'lwc';
import { ShowToastEvent } from 'lightning/platformShowToastEvent';

export default class QueryBuilderLwc extends KSExcel {

    @api keelstoneSessionId;

    @track soql          = '';
    @track activeRange   = '';
    @track isRunning     = false;
    @track isSaving      = false;
    @track errorMessage  = '';
    @track statusMessage = '';
    @track tableName     = '';
    @track optAutofit    = true;
    @track optHideId     = true;

    handleOptAutofit(e)  { this.optAutofit = e.target.checked; }
    handleOptHideId(e)   { this.optHideId  = e.target.checked; }
    handleSoqlChange(e)  { this.soql       = e.target.value; }
    handleRangeChange(e) { this.activeRange = e.target.value; }

    handleKeyDown(e) {
        if (e.key === 'Enter' && (e.ctrlKey || e.metaKey)) {
            e.preventDefault();
            if (!this.isRunDisabled) this.handleRun();
        }
    }

    async handleGetRange() {
        this.activeRange = await this.ksGetRange();
    }

    async handleRun() {
        this.isRunning     = true;
        this.errorMessage  = '';
        this.statusMessage = '';
        this.tableName     = '';
        try {
            const result = await this.ksQuery(
                this.soql.trim(),
                this.activeRange || undefined,
                { hideId: this.optHideId, autofit: this.optAutofit }
            );
            this.tableName     = result?.tableName || '';
            const { rowCount, totalSize } = result;
            this.statusMessage = totalSize > rowCount
                ? `${rowCount.toLocaleString()} of ${totalSize.toLocaleString()} records written`
                : `${rowCount.toLocaleString()} record${rowCount !== 1 ? 's' : ''} written`;
        } catch (err) {
            this.errorMessage = err.message ?? 'An unexpected error occurred.';
        } finally {
            this.isRunning = false;
        }
    }

    async handleSave() {
        this.isSaving = true;
        this.errorMessage = '';
        try {
            const { tables } = await this.ksSaveTables();
            let saved = 0, errors = 0;

            for (const { tableName, headers, rows } of (tables || [])) {
                if (!tableName?.startsWith('SF_')) { continue; }
                const sobject = tableName.replace(/^SF_/, '').replace(/_\d+$/, '');
                const records = rows
                    .map((row, originalIndex) => ({ row, originalIndex }))
                    .filter(({ row }) => row.some(cell => cell !== '' && cell !== null))
                    .map(({ row, originalIndex }) => {
                        const rec = { _rowIndex: originalIndex };
                        headers.forEach((h, j) => { rec[h] = row[j] ?? ''; });
                        return rec;
                    });
                if (!records.length) { continue; }

                const { results } = await this.ksSaveRecords(sobject, records);
                saved  += results.filter(r =>  r.success).length;
                errors += results.filter(r => !r.success).length;

                const errorMap = new Map(results.map(r => [r.rowIndex, r]));
                const rowErrors = rows.map((_, i) => {
                    const r = errorMap.get(i);
                    if (!r) return '';
                    return r.success ? '' : (r.errors?.[0]?.message || 'Save failed');
                });
                await this.ksWriteTableErrors(tableName, rowErrors);
            }

            const s = n => `${n} row${n !== 1 ? 's' : ''}`;
            this.dispatchEvent(new ShowToastEvent({
                title:   errors > 0 ? 'Saved with errors' : 'Save complete',
                message: errors > 0
                    ? `${s(saved)} saved, ${s(errors)} had errors — see the _Error column.`
                    : `${s(saved)} saved successfully.`,
                variant: errors > 0 ? 'warning' : 'success',
            }));
        } catch (err) {
            this.errorMessage = err.message ?? 'Save failed.';
        } finally {
            this.isSaving = false;
        }
    }

    get isRunDisabled()  { return !this.soql.trim() || this.isRunning; }
    get isSaveDisabled() { return !this.tableName || this.isSaving; }
    get hasResults()     { return !!this.statusMessage; }
    get runBtnLabel()    { return this.isRunning ? 'Running…' : 'Run Query'; }
    get saveBtnLabel()   { return this.isSaving  ? 'Saving…'  : 'Save'; }
}

Common adaptations​

Pre-populate a SOQL query​

Set soql in connectedCallback to pre-fill the query based on a flow input variable or a hard-coded object:

@api targetObject = 'Account';

connectedCallback() {
    this.soql = `SELECT Id, Name, Phone, Industry FROM ${this.targetObject} LIMIT 200`;
    this.handleRun();   // auto-run on load
}

Lock the query and only show Save​

If the SOQL is always the same and the user should only edit and save, hide the textarea in the template and auto-run on connectedCallback. Expose only the Save button.

Filter to a specific record​

Add a recordId input and use it in the SOQL:

@api recordId;

connectedCallback() {
    this.soql = `SELECT Id, Name, Status__c FROM Line_Item__c WHERE Parent__c = '${this.recordId}'`;
    this.handleRun();
}

Multi-table save​

ksSaveTables() reads all tables on the active sheet. If your flow writes two tables (e.g. Contacts and Opportunities), both are returned in the tables array and the loop in handleSave processes each one. The SF_ prefix and the sObject name in the table name provide enough context to route each table to the correct ksSaveRecords call without any extra configuration.