queryBuilderLwc

An interactive SOQL query builder designed to run inside the Keelstone Excel add-in taskpane. The user writes any SOQL query, optionally targets a specific Excel range, configures output options, and clicks Run Query to write results directly into the spreadsheet as a formatted Excel Table with picklist validation.

Package: Keelstone-Samples
Component name: kstone:queryBuilderLwc
Flow: Keelstone_Query_Builder (single screen, launched from the add-in)

---

UI layout

┌─────────────────────────────────────────────┐
│  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               │  ← success status
│  [ Save to Salesforce ]                     │  ← appears after successful run
└─────────────────────────────────────────────┘

Pressing Ctrl+Enter (or Cmd+Enter on Mac) inside the SOQL textarea runs the query without clicking the button.

---

API calls

This component extends KSExcel from kstone/api. It does not use keelstoneGenerateDocument or any template.

Action	Method	Description
Pin button clicked	ksGetRange()	Returns the current Excel selection address
Run button clicked	ksQuery(soql, target, options)	Executes SOQL and writes results as a formatted Table with picklist validation
Save button clicked	ksSaveTables()	Reads all sheet tables and returns them as structured data

All calls are async — the component awaits the result and updates the UI when the operation completes. See KSExcel Reference.

---

Options

Option	Default	Description
Auto-fit columns	On	After writing, auto-fits all column widths to their content
Hide ID column	On	Hides the Id column from the written table (column is still present; just hidden)

---

Using the range picker

Click the pin (📍) button to capture the cell or range currently selected in Excel. The address populates the range input and is used as the write target. The user can also type a target directly:

Input	Writes to
(empty)	A1 on the active sheet
B3	Cell B3 on the active sheet
Sheet2!C5	Cell C5 on Sheet2

---

What happens after Run

1. The component calls ksQuery(soql, target, { hideId, autofit }).
2. The Keelstone server executes the SOQL using the stored access token.
3. The server fetches field metadata from Salesforce to identify picklist fields and required fields.
4. The taskpane receives the data and:
   • Deletes any existing Keelstone table at the target range
   • Writes records as an Excel Table with TableStyleMedium2 styling
   • Applies in-cell dropdown validation for picklist fields
   • Highlights required field headers in yellow
   • Auto-fits columns and optionally hides the Id column
5. ksQuery() resolves with { tableName, address, rowCount, totalSize }.
6. The component displays the record count (and total if the query was truncated by Salesforce) — and the Save to Salesforce button appears.

---

Save to Salesforce

Clicking Save to Salesforce calls ksSaveTables(), which reads all Excel Tables on the active worksheet and returns their data to the component. The component fires a ShowToastEvent with the result:

"3 record(s) saved."
"2 record(s) saved with 1 error(s)."

---

Security

The SOQL query is executed server-side using the user's stored Salesforce access token. Users can only query records and fields they have access to in Salesforce — object-level security, field-level security, and sharing rules are enforced by the Salesforce API.

---

Implementing your own version

To add this pattern to your own LWC, extend KSExcel:

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

export default class MyQueryLwc extends KSExcel {
    @api keelstoneSessionId;
    @track activeRange = '';

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

    async handleRun(soql) {
        const result = await this.ksQuery(soql, this.activeRange || undefined, {
            hideId: true,
            autofit: true,
        });
        // result = { tableName, address, rowCount, totalSize }
    }

    async handleSave() {
        const result = await this.ksSaveTables();
        // result = { saved, errors }
        this.dispatchEvent(new ShowToastEvent({
            title: 'Save Complete',
            message: `${result.saved} record(s) saved.`,
            variant: 'success',
        }));
    }
}

No message listeners, no cleanup — just await the method and handle the result.