KSExcel Reference

KSExcel is the class to extend when your LWC component needs to read or write data in Excel. All methods are async and return when the Excel operation completes.

Import

import { KSExcel } from 'kstone/api';

---

Selection & range

ksGetRange()

Returns the address of the active cell.

async ksGetRange() → Promise<string>

Returns: A sheet-qualified address string, e.g. "Sheet1!A1".

const address = await this.ksGetRange();
this.targetRange = address;  // "Sheet1!B2"

---

ksGetSelectedRange()

Returns the address and current values of the user's selection.

async ksGetSelectedRange() → Promise<{ address: string, values: any[][] }>

---

ksGetWorksheetNames()

Returns the names of all worksheets in the workbook.

async ksGetWorksheetNames() → Promise<string[]>

const names = await this.ksGetWorksheetNames();
// ['Sheet1', 'Contacts', 'Opportunities']

---

Reading & writing data

ksWrite(data, target?, namedRange?)

Writes a 2D array to a range.

async ksWrite(data, target?, namedRange = true) → Promise<WriteResult>

Param	Type	Default	Description
data	any[][]	required	2D array of values
target	string	active cell	Cell address ("B3") or sheet-qualified address ("Sheet2!C1")
namedRange	boolean	true	Wrap the data in an Excel Table and create a named range

Returns: { tableName: string | null, address: string, rowCount: number }

const result = await this.ksWrite(
    [['Id', 'Name', 'Revenue'], ['001...', 'Acme', 500000]],
    'Sheet1!A1'
);

---

ksWriteTable(headers, rows, target?, namedRange?)

Convenience wrapper around ksWrite. Accepts an array of record objects and converts them to the 2D format automatically.

ksWriteTable(headers, rows, target?, namedRange = true) → Promise<WriteResult>

Param	Type	Description
headers	string[]	Column names; controls column order
rows	object[]	Array of record objects; missing fields written as empty string
target	string	Same as ksWrite
namedRange	boolean	Same as ksWrite

await this.ksWriteTable(
    ['Id', 'Name', 'Industry'],
    [{ Id: '001...', Name: 'Acme', Industry: 'Tech' }],
    'Sheet1!A1'
);

---

ksWriteFormulas(formulas, target?)

Writes a 2D array of formula strings to a range. Formulas are evaluated by Excel — use this instead of ksWrite when cells should contain live formulas rather than static values.

async ksWriteFormulas(formulas, target?) → Promise<{ address: string }>

Param	Type	Default	Description
formulas	string[][]	required	2D array of Excel formula strings
target	string	active cell	Cell address or sheet-qualified address

Returns: { address: string } — the range that was written.

// Write a totals row beneath a SOQL-generated table
await this.ksWriteFormulas(
    [['=COUNTA(B2:B201)', '=SUM(C2:C201)', '=AVERAGE(D2:D201)']],
    'Sheet1!B202'
);

---

ksAddSheet(name, data?, startRow?)

Adds a new worksheet to the workbook and optionally writes initial data to it.

async ksAddSheet(name, data = [], startRow = 0) → Promise<object>

Param	Type	Default	Description
name	string	required	Sheet name
data	any[][]	[]	2D data array to write
startRow	number	0	Zero-based row index to start writing data

await this.ksAddSheet('Contacts', [['Name', 'Email'], ['Ada Lovelace', 'ada@example.com']]);

---

Files & workbooks

ksGetFile()

Returns the current workbook as a base64-encoded .xlsx file. Useful for uploading the workbook to Salesforce Files after modifying it.

async ksGetFile() → Promise<{ base64: string }>

const { base64 } = await this.ksGetFile();
await this.ksCall('/api/salesforce/files/upload', { base64, filename: 'Report.xlsx', recordId: this.recordId });

---

ksInsert(base64, filename)

Inserts a base64-encoded .xlsx file as new sheets at the end of the active workbook.

async ksInsert(base64, filename) → Promise<{ success: true }>

Param	Type	Description
base64	string	Base64-encoded .xlsx file
filename	string	Filename shown in any error messages

---

ksCreateExcel(sheets)

Builds a new .xlsx workbook from scratch (no template) and returns it as base64.

async ksCreateExcel(sheets) → Promise<{ base64: string }>

Param	Type	Description
sheets	{ name: string, headers: string[], rows: any[][] }[]	Sheet definitions

const { base64 } = await this.ksCreateExcel([
    { name: 'Accounts', headers: ['Name', 'Industry'], rows: [['Acme', 'Tech']] }
]);

---

ksGenerateFromData(templateKey, data, options?)

Looks up a Keelstone template by its key, merges a data object into it server-side, and inserts the result into the active workbook. The data object can be assembled from any combination of sources — Salesforce records, external REST API responses, computed values, or all three.

async ksGenerateFromData(templateKey, data, options = {}) → Promise<{ base64: string, contentDocumentId?: string }>

Param	Type	Description
templateKey	string	Template_Key__c on the Keelstone_Template__c record
data	object	Any JSON-serializable object; keys become merge tokens
options.filename	string	Output filename, e.g. 'Report.xlsx'
options.linkedEntityId	string	Salesforce record ID to attach the generated file to
options.outputFormat	string	'pdf' to convert the output

// Salesforce record data + external pricing API combined into one merge context
const sfRecord = await getAccountData({ recordId: this.recordId });
const pricing  = await fetch('https://api.example.com/pricing').then(r => r.json());

const result = await this.ksGenerateFromData(
    'data-report',
    { ...sfRecord, ...pricing },
    { filename: 'Report.xlsx', linkedEntityId: this.recordId }
);
// result.contentDocumentId — Salesforce File ID of the generated workbook

---

SOQL query → table

ksQuery(soql, target?, options?)

Executes a SOQL query on Salesforce and writes the results to Excel as a formatted Table.

• Relationship fields are flattened to dot-notation (e.g. Owner.Name).
• Picklist fields automatically get in-cell dropdown validation.
• Required field headers are highlighted in yellow.
• The Id column is hidden by default (value retained in the cell).
• Running the same query again replaces the existing table in place — there is no duplicate.

async ksQuery(soql, target?, options = {}) → Promise<QueryResult>

Param	Type	Default	Description
soql	string	required	Any valid SOQL query string
target	string	active cell	Write target — cell or sheet-qualified address
options.hideId	boolean	true	Hide the Id column
options.autofit	boolean	true	Auto-fit column widths after writing

Returns: { tableName: string, address: string, rowCount: number, totalSize: number }

• tableName — Excel Table name, format SF_<SObject>_<timestamp>, e.g. SF_Account_1714000000000
• address — full range written, e.g. Sheet1!A1:F51
• rowCount — records written to Excel
• totalSize — total matching records in Salesforce (may exceed rowCount if the query has a LIMIT)

const result = await this.ksQuery(
    'SELECT Id, Name, AnnualRevenue, Owner.Name FROM Account LIMIT 200',
    'Sheet1!A1'
);
this.tableName    = result.tableName;   // store for later refresh
this.statusMessage = `${result.rowCount} of ${result.totalSize} records written`;

---

Saving records back to Salesforce

ksSaveTables()

Reads all Excel Tables in the workbook whose names start with SF_ and returns their headers and row data. Use this before ksSaveRecords to get the current table state.

async ksSaveTables() → Promise<{ tables: { tableName: string, headers: string[], rows: any[][] }[] }>

---

ksSaveRecords(sobject, records)

Upserts Salesforce records from an array of plain objects. Include an Id field to update existing records; omit it to insert new ones.

async ksSaveRecords(sobject, records) → Promise<SaveResult>

Param	Type	Description
sobject	string	Salesforce SObject API name, e.g. 'Account'
records	object[]	Each record must include _rowIndex (zero-based row position in the table body) so errors can be written back to the correct row

Returns: { results: { rowIndex: number, success: boolean, id?: string, errors?: { message: string }[] }[] }

---

ksWriteTableErrors(tableName, rowErrors)

Writes per-row error messages into the _Error column of an SF_ table. Pass an empty string for rows that succeeded.

async ksWriteTableErrors(tableName, rowErrors) → Promise<object>

Param	Type	Description
tableName	string	Excel Table name, e.g. 'SF_Account_1714000000000'
rowErrors	string[]	One entry per data row; '' = no error

---

ksGapFill(params)

Fills blank cells in an existing range by looking up matching Salesforce records. Useful for enriching a spreadsheet that already has key values (e.g. Account IDs) but is missing related fields.

async ksGapFill(params) → Promise<object>

See the Gap Fill developer docs for the full parameter reference.

---

Salesforce Reports

Run a named Salesforce Report and write the results to Excel as a formatted Table. Supports Tabular, Summary, and Matrix report formats. Column headers use the report's display labels — no field API name mapping required.

ksListReports()

Lists all Salesforce Reports accessible to the current user. Use this to build a report picker UI before calling ksRunReport.

async ksListReports() → Promise<{ id: string, name: string, reportFormat: 'TABULAR'|'SUMMARY'|'MATRIX' }[]>

const reports = await this.ksListReports();
this.reportOptions = reports.map(r => ({ label: r.name, value: r.id }));

---

ksDescribeReport(reportId)

Returns report metadata without running the report — column labels, data types, format, and filter criteria. Useful for previewing report structure before writing to Excel.

async ksDescribeReport(reportId) → Promise<object>

---

ksRunReport(reportId, target?, options?)

Runs a Salesforce Report and writes the results to Excel as a formatted Table.

• Column headers are the report's display labels (e.g. "Account Name", not "ACCOUNT_NAME").
• Summary and Matrix reports are flattened to rows — grouping headers are not written.
• The table is named SF_Report_<ReportName>_<timestamp>. Running the same report again replaces the existing table in place.

async ksRunReport(reportId, target?, options = {}) → Promise<QueryResult>

Param	Type	Default	Description
reportId	string	required	Salesforce Report ID (15 or 18-char)
target	string	active cell	Top-left cell address for the table
options.hideId	boolean	false	Hide the Id column if present (most reports have no Id column)
options.autofit	boolean	true	Auto-fit column widths after writing

Returns: { tableName: string, address: string, rowCount: number, totalSize: number } — same shape as ksQuery.

// Run a report and store the table name for later refresh
const result = await this.ksRunReport(this.reportId, 'Sheet1!A1');
this.tableName     = result.tableName;
this.statusMessage = `${result.rowCount} rows written`;

// Store the report ID in a document property so it can be re-run after reload
await this.ksSetDocumentProperty(`ks_report_${result.tableName}`, this.reportId);

---

Document properties

Document properties are key/value strings stored inside the .xlsx file. They persist across saves and taskpane reloads, making them the right place to store metadata that needs to travel with the file — such as the SOQL behind a query table or a linked Salesforce record ID.

ksGetDocumentProperty(key)

Reads a custom document property from the workbook.

async ksGetDocumentProperty(key) → Promise<string | null>

Returns null if the property does not exist.

const soql = await this.ksGetDocumentProperty('ks_query_SF_Account_1714000000000');

---

ksSetDocumentProperty(key, value)

Writes a custom document property to the workbook.

async ksSetDocumentProperty(key, value) → Promise<object>

// After a successful ksQuery, persist the SOQL so it can be refreshed later
await this.ksSetDocumentProperty(`ks_query_${result.tableName}`, this.soql);

---

ksGetAllDocumentProperties()

Reads all custom document properties from the workbook at once.

async ksGetAllDocumentProperties() → Promise<{ [key: string]: string }>

const props = await this.ksGetAllDocumentProperties();
const queryKeys = Object.keys(props).filter(k => k.startsWith('ks_query_'));

---

Cell comments

ksGetCellComments(address?)

Returns comments on a specific cell, or all comments in the active sheet.

async ksGetCellComments(address?) → Promise<Comment[]>

Returns:

[{
  id: 'comment-id',
  address: 'Sheet1!A1',
  text: 'First comment',
  authorName: 'Matthew',
  resolved: false,
  replies: [{ id, text, authorName }]
}]

---

ksAddCellComment(address, text)

Adds a comment to a cell.

async ksAddCellComment(address, text) → Promise<{ id: string, ok: true }>

---

ksReplyToCellComment(commentId, text)

Adds a reply to an existing comment.

async ksReplyToCellComment(commentId, text) → Promise<{ ok: true }>

---

ksResolveCellComment(commentId, resolved?)

Resolves or reopens a cell comment.

async ksResolveCellComment(commentId, resolved = true) → Promise<{ ok: true }>

---

ksDeleteCellComment(commentId)

Deletes a cell comment.

async ksDeleteCellComment(commentId) → Promise<{ ok: true }>

---

Charts

ksAddChart(dataRange, options?)

Creates a chart from a range that already exists in the sheet.

async ksAddChart(dataRange, options = {}) → Promise<{ chartName: string, ok: true }>

Param	Type	Description
dataRange	string	Source data range, e.g. "Sheet1!A1:C10"
options.chartType	string	Excel chart type (e.g. "ColumnClustered", "Line", "Pie")
options.title	string	Chart title
options.chartName	string	Name for the chart object
options.sheetName	string	Sheet to place the chart on
options.position	object	{ left, top, width, height } in points
options.xAxisTitle	string	X-axis label
options.yAxisTitle	string	Y-axis label
options.showLegend	boolean	Show legend (default true)
options.showDataLabels	boolean	Show data labels (default false)

---

ksAddChartFromData(labels, series, options?)

Writes data arrays to the sheet and creates a chart from them in one call.

async ksAddChartFromData(labels, series, options = {}) → Promise<{ chartName: string, ok: true }>

Param	Type	Description
labels	string[]	Category labels
series	{ label: string, data: number[], color?: string }[]	Data series
options.dataTarget	string	Where to write the data (default: active cell)
options.chartType	string	Same as ksAddChart
options.chartPosition	object	Same as options.position in ksAddChart
All other options	—	Same as ksAddChart

---

ksDeleteChart(chartName, sheetName?)

Deletes a chart by name.

async ksDeleteChart(chartName, sheetName?) → Promise<{ ok: true }>

---

ksListCharts()

Lists all charts across all sheets in the workbook.

async ksListCharts() → Promise<{ name: string, sheetName: string }[]>

---

Range formatting & operations

ksFormatRange(address, format, sheet?)

Applies font, fill, and alignment formatting to a range without changing values.

async ksFormatRange(address, format, sheet?) → Promise<{ ok: true }>

Param	Type	Description
address	string	Cell address or range, e.g. 'A1:D1'
format.bold	boolean	Bold font
format.italic	boolean	Italic font
format.underline	boolean	Single underline
format.fontSize	number	Font size in points
format.fontColor	string	Font colour hex, e.g. '#032D60'
format.fillColor	string	Cell background colour hex
format.wrapText	boolean	Wrap cell text
format.horizontalAlignment	string	'Left' | 'Center' | 'Right'
format.verticalAlignment	string	'Top' | 'Middle' | 'Bottom'
sheet?	string	Sheet name; defaults to active sheet

// Bold and blue header row
await this.ksFormatRange('A1:E1', { bold: true, fillColor: '#032D60', fontColor: '#FFFFFF' });

---

ksClearRange(address, clearType?, sheet?)

Clears a range. Does not delete rows or columns.

async ksClearRange(address, clearType = 'All', sheet?) → Promise<{ ok: true }>

clearType: 'All' | 'Contents' | 'Formats' | 'Hyperlinks'

---

ksMergeRange(address, merge?, across?, sheet?)

Merges or unmerges cells in a range.

async ksMergeRange(address, merge = true, across = false, sheet?) → Promise<{ ok: true }>

Param	Type	Description
merge	boolean	true to merge, false to unmerge
across	boolean	Merge each row independently instead of the whole range

---

ksCopyRange(sourceAddress, destAddress, sourceSheet?, destSheet?)

Copies values from one range to another (values only, no formatting).

async ksCopyRange(sourceAddress, destAddress, sourceSheet?, destSheet?) → Promise<{ ok: true }>

---

ksNumberFormat(address, numberFormat, sheet?)

Sets the number format for a range.

async ksNumberFormat(address, numberFormat, sheet?) → Promise<{ ok: true }>

Common format strings: '#,##0.00', '0%', 'mm/dd/yyyy', '$#,##0.00', '@' (text)

await this.ksNumberFormat('C2:C100', '#,##0.00', 'Revenue');

---

ksAddHyperlink(address, url, textToDisplay?, sheet?)

Sets a hyperlink on a cell.

async ksAddHyperlink(address, url, textToDisplay?, sheet?) → Promise<{ ok: true }>

---

ksSortRange(address, fields, sheet?)

Sorts a range. fields is an array of { key: columnIndex, ascending: bool } where key is 0-based within the range.

async ksSortRange(address, fields, sheet?) → Promise<{ ok: true }>

// Sort A1:D50 by column 1 ascending, then column 3 descending
await this.ksSortRange('A1:D50', [{ key: 1, ascending: true }, { key: 3, ascending: false }]);

---

ksAutofit(address?, fitColumns?, fitRows?, sheet?)

Auto-fits column widths and/or row heights. If address is omitted, applies to the used range.

async ksAutofit(address?, fitColumns = true, fitRows = false, sheet?) → Promise<{ ok: true }>

---

ksFindReplace(find, replace, options?)

Finds and replaces text within a sheet.

async ksFindReplace(find, replace, options = {}) → Promise<{ replacedCount: number }>

Param	Type	Default	Description
find	string	required	Text to search for
replace	string	required	Replacement text
options.sheet	string	active	Target sheet name
options.matchCase	boolean	false	Case-sensitive match
options.matchEntireCell	boolean	false	Match whole cell content only

---

ksGetUsedRange(sheet?)

Returns the address, dimensions, and values of the used range.

async ksGetUsedRange(sheet?) → Promise<{ address: string, rowCount: number, columnCount: number, values: any[][] }>

---

ksConditionalFormat(address, type, rule?, sheet?)

Adds conditional formatting to a range.

async ksConditionalFormat(address, type, rule = {}, sheet?) → Promise<{ ok: true }>

type	rule fields	Description
'ColorScale'	minColor?, midColor?, maxColor? (hex)	3-colour scale from min to max value
'DataBar'	—	Data bar proportional to cell value
'Custom'	formula, format.fontColor?, format.fillColor?	Custom formula-driven format

// Highlight cells > 1000 in green
await this.ksConditionalFormat('C2:C100', 'Custom', {
    formula: '=C2>1000',
    format: { fillColor: '#CCFFCC' }
});

---

Sheet management

ksActivateSheet(sheet)

Switches the active sheet.

async ksActivateSheet(sheet) → Promise<{ ok: true }>

---

ksRenameSheet(sheet, newName)

Renames a worksheet.

async ksRenameSheet(sheet, newName) → Promise<{ ok: true }>

---

ksDeleteSheet(sheet)

Deletes a worksheet. Irreversible.

async ksDeleteSheet(sheet) → Promise<{ ok: true }>

---

ksProtectSheet(protect, sheet?, password?)

Protects or unprotects a worksheet.

async ksProtectSheet(protect, sheet?, password?) → Promise<{ ok: true }>

---

ksFreezePanes(frozenRows?, frozenColumns?, sheet?)

Freezes the top rows and/or leftmost columns. Pass 0, 0 to unfreeze.

async ksFreezePanes(frozenRows = 0, frozenColumns = 0, sheet?) → Promise<{ ok: true }>

await this.ksFreezePanes(1, 0);  // freeze top row
await this.ksFreezePanes(1, 2);  // freeze first row and first two columns
await this.ksFreezePanes(0, 0);  // unfreeze all

---

Rows & columns

ksInsertRows(rowIndex, count?, sheet?)

Inserts blank rows above rowIndex (0-based).

async ksInsertRows(rowIndex, count = 1, sheet?) → Promise<{ ok: true }>

---

ksDeleteRows(rowIndex, count?, sheet?)

Deletes rows starting at rowIndex.

async ksDeleteRows(rowIndex, count = 1, sheet?) → Promise<{ ok: true }>

---

ksInsertColumns(columnIndex, count?, sheet?)

Inserts blank columns to the right of columnIndex (0-based).

async ksInsertColumns(columnIndex, count = 1, sheet?) → Promise<{ ok: true }>

---

ksDeleteColumns(columnIndex, count?, sheet?)

Deletes columns starting at columnIndex.

async ksDeleteColumns(columnIndex, count = 1, sheet?) → Promise<{ ok: true }>

---

ksHideRows(rowIndex, count?, hidden?, sheet?)

Hides or unhides rows.

async ksHideRows(rowIndex, count = 1, hidden = true, sheet?) → Promise<{ ok: true }>

---

ksHideColumns(columnIndex, count?, hidden?, sheet?)

Hides or unhides columns.

async ksHideColumns(columnIndex, count = 1, hidden = true, sheet?) → Promise<{ ok: true }>

---

Tables & named ranges

ksGetTables(sheet?)

Returns all Excel Table names and IDs in the workbook or a specific sheet.

async ksGetTables(sheet?) → Promise<{ tables: { name: string, id: string }[] }>

---

ksAddTableRow(tableName, values, index?)

Appends a row to the end of a named Excel Table, or inserts it at a specific index.

async ksAddTableRow(tableName, values, index?) → Promise<{ ok: true }>

Param	Type	Description
tableName	string	Name of the Excel Table
values	any[]	One value per column in table order
index?	number	Row position to insert at; omit to append

await this.ksAddTableRow('SF_Accounts_1714000000000', ['Acme', 'Technology', 500000]);

---

ksGetNamedRanges()

Lists all named ranges and named items in the workbook.

async ksGetNamedRanges() → Promise<{ names: { name: string, type: string, comment: string }[] }>

---

ksCall(endpoint, body?)

Low-level method inherited from DocumentAPI. Call this when you need to reach a server endpoint not covered by a named method.

async ksCall(endpoint, body = {}) → Promise<any>

const result = await this.ksCall('/api/salesforce/files/upload', {
    base64,
    filename: 'Report.xlsx',
    recordId: this.recordId,
});