I have hard coded a Cards Grid widget. I mean hard coded in the sense it works for a specific table with specific child table
It fetches the child records and display them as a second grid inside the card.
Now I am trying to configure the Widget to have column mappings. And I have no clue how to map the Child Table it’s columns. It seems I am only able to access directly from the config panel the columns of the main table.
Hi, I have the same question: is it possible from a custom widget to map columns from another table? I feel like it’s not. Maybe there’s a workaround?
Thanks in advance!
Yes, there are. I will post how. But check my thread about my “ultra Kanban”.
The key is that the widget doesn’t just get your table’s data; it first needs to understand your document’s structure . It does this by reading Grist’s internal “metadata” to learn about all your columns, their types, and especially the relationships you’ve created with Reference and Reference List columns.
Here’s the step-by-step process from the widget’s point of view:
Get the Main Record: The widget starts with a record from the table it’s placed on. Let’s say this record is from a Tasks table and looks like this: {id: 1, Title: ‘Design new logo’, Owner: 3}. The Owner column is a Reference.
Inspect the Column Schema: The widget needs to understand what Owner means. It queries a special Grist system table called _grist_Tables_column. This gives it a master list of every column in your document. From here, the widget finds the Owner column and sees its type is something like ‘Ref:Employees’.
Identify the Other Table: From that type string ‘Ref:Employees’, the widget’s code now knows two critical things:
It’s a reference to another record.
It points specifically to the Employees table.
Fetch Data from the Other Table: Now that the widget knows the name of the referenced table (Employees), it can fetch all of its data using a command like grist.docApi.fetchTable(‘Employees’). This gives the widget a list of all employee records, for example: [{id: 3, Name: ‘Alice’, Email: ‘…’}, …].
Link the Data: This is the final step where the magic happens. The widget takes the Owner value from the Tasks record (which was 3) and finds the matching record in the Employees data it just fetched. Now it knows that ‘Task 1’ belongs to ‘Alice’ and can display her name, email, or any other field from the Employees table right inside the widget.
Here’s a simplified code concept of what’s happening:
// Conceptual code for a widget
async function displayLinkedData(taskRecord) {
// taskRecord is {id: 1, Title: 'Design new logo', Owner: 3}
// 1. Assume we've already figured out 'Owner' points to the 'Employees' table.
const referencedTableId = 'Employees';
// 2. Fetch the other table's data.
const employeesData = await grist.docApi.fetchTable(referencedTableId);
// 3. Find the specific employee record using the ID from the task record.
const ownerId = taskRecord.Owner; // This is 3
const employeeRecord = findRecordById(employeesData, ownerId); // A helper function to find the record
if (employeeRecord) {
// 4. Now the widget can display data from both tables!
console.log(`Task '${taskRecord.Title}' is owned by '${employeeRecord.Name}'.`);
// This could be rendered into the widget's HTML.
}
}
Try this code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Grist Document Explorer</title>
<script src="https://docs.getgrist.com/grist-plugin-api.js"></script>
<style>
:root {
--border-color: #d1d5db;
--sidebar-bg: #f9fafb;
--hover-bg: #eff6ff;
--active-bg: #dbeafe;
--header-bg: #f3f4f6;
--font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
}
html, body {
height: 100%;
margin: 0;
font-family: var(--font-family);
color: #1f2937;
background-color: #fff;
overflow: hidden;
}
.container {
display: flex;
height: 100%;
}
#sidebar {
width: 250px;
flex-shrink: 0;
background-color: var(--sidebar-bg);
border-right: 1px solid var(--border-color);
padding: 1rem;
overflow-y: auto;
}
#sidebar h2 {
margin-top: 0;
font-size: 1.1rem;
border-bottom: 1px solid var(--border-color);
padding-bottom: 0.5rem;
}
#table-list {
list-style: none;
padding: 0;
margin: 0;
}
#table-list button {
width: 100%;
padding: 0.75rem 0.5rem;
border: none;
background: none;
text-align: left;
cursor: pointer;
border-radius: 4px;
font-size: 0.9rem;
}
#table-list button:hover {
background-color: var(--hover-bg);
}
#table-list button.active {
background-color: var(--active-bg);
font-weight: 600;
}
#content-area {
flex-grow: 1;
padding: 1.5rem;
overflow: auto;
}
.placeholder, .loading, .error {
color: #6b7280;
font-style: italic;
}
.error {
color: #dc2626;
font-weight: bold;
}
.section {
margin-bottom: 2rem;
}
.section h3 {
margin-top: 0;
border-bottom: 1px solid var(--border-color);
padding-bottom: 0.5rem;
}
table {
width: 100%;
border-collapse: collapse;
font-size: 0.85rem;
}
th, td {
border: 1px solid var(--border-color);
padding: 0.5rem;
text-align: left;
vertical-align: top;
max-width: 300px;
overflow: hidden;
text-overflow: ellipsis;
white-space: nowrap;
}
th {
background-color: var(--header-bg);
font-weight: 600;
}
.raw-data {
font-family: monospace;
white-space: pre-wrap; /* Allows wrapping of long data */
word-break: break-all;
}
</style>
</head>
<body>
<div class="container">
<aside id="sidebar">
<h2>Tables</h2>
<ul id="table-list">
<!-- Table list will be populated by JS -->
</ul>
</aside>
<main id="content-area">
<div id="initial-placeholder" class="placeholder">
<p>Select a table from the list on the left to view its metadata and data.</p>
</div>
<div id="table-details" style="display: none;">
<h2 id="table-title"></h2>
<div id="metadata-container" class="section">
<!-- Metadata table will be populated by JS -->
</div>
<div id="data-container" class="section">
<!-- Data table will be populated by JS -->
</div>
</div>
</main>
</div>
<script>
document.addEventListener('DOMContentLoaded', () => {
grist.ready({ requiredAccess: 'full' });
const tableListElement = document.getElementById('table-list');
const initialPlaceholder = document.getElementById('initial-placeholder');
const tableDetailsContainer = document.getElementById('table-details');
const tableTitleElement = document.getElementById('table-title');
const metadataContainer = document.getElementById('metadata-container');
const dataContainer = document.getElementById('data-container');
const contentArea = document.getElementById('content-area');
let allTablesSchema = [];
function transformGristData(gristData) {
const records = [];
const columnIds = Object.keys(gristData);
if (columnIds.length === 0 || !gristData[columnIds[0]] || gristData[columnIds[0]].length === 0) return [];
const numRecords = gristData[columnIds[0]].length;
for (let i = 0; i < numRecords; i++) {
const record = {};
for (const colId of columnIds) {
record[colId] = gristData[colId][i];
}
records.push(record);
}
return records;
}
// FIX: This function is now completely rewritten to be robust.
async function buildSchemas() {
const tablesData = await grist.docApi.fetchTable('_grist_Tables');
const columnsData = await grist.docApi.fetchTable('_grist_Tables_column');
const tables = transformGristData(tablesData);
const columns = transformGristData(columnsData);
// Create a map of numeric table ID to its text ID (e.g., 1 -> "Tasks")
const tableIdMap = new Map();
tables.forEach(table => {
tableIdMap.set(table.id, table.tableId);
});
const schemas = {};
// Determine the correct key for linking columns to tables ('parentId' or 'tableId')
const linkingKey = columns.length > 0 && 'parentId' in columns[0] ? 'parentId' : 'tableId';
columns.forEach(col => {
const tableTextId = tableIdMap.get(col[linkingKey]);
if (!tableTextId) return; // Skip columns for tables not found
if (!schemas[tableTextId]) {
schemas[tableTextId] = { id: tableTextId, fields: [] };
}
schemas[tableTextId].fields.push({
id: col.colId,
label: col.label,
type: col.type,
isFormula: Boolean(col.isFormula)
});
});
// Return the schemas as an array of objects
return Object.values(schemas);
}
async function initializeApp() {
try {
allTablesSchema = await buildSchemas();
const userTables = allTablesSchema.filter(table => !table.id.startsWith('_grist_'));
tableListElement.innerHTML = '';
userTables.forEach(table => {
const li = document.createElement('li');
const button = document.createElement('button');
button.textContent = table.id;
button.dataset.tableId = table.id;
button.addEventListener('click', () => {
document.querySelectorAll('#table-list button').forEach(btn => btn.classList.remove('active'));
button.classList.add('active');
loadTableDetails(table.id);
});
li.appendChild(button);
tableListElement.appendChild(li);
});
} catch (error) {
console.error("Error initializing widget:", error);
contentArea.innerHTML = `<p class="error">Failed to load tables. Error: ${error.message}. See console for details.</p>`;
}
}
async function loadTableDetails(tableId) {
initialPlaceholder.style.display = 'none';
tableDetailsContainer.style.display = 'block';
tableTitleElement.textContent = `Table: ${tableId}`;
metadataContainer.innerHTML = '<h3>Metadata (Columns)</h3><p class="loading">Loading...</p>';
dataContainer.innerHTML = '<h3>Data (Records)</h3><p class="loading">Loading...</p>';
try {
const tableSchema = allTablesSchema.find(t => t.id === tableId);
if (!tableSchema) throw new Error(`Schema for table "${tableId}" not found.`);
renderMetadata(tableSchema.fields);
const rawData = await grist.docApi.fetchTable(tableId);
const records = transformGristData(rawData);
const columnOrder = tableSchema.fields.map(f => f.id);
renderData(records, columnOrder);
} catch (error) {
console.error(`Error loading details for table ${tableId}:`, error);
dataContainer.innerHTML = `<p class="error">Failed to load data for ${tableId}: ${error.message}</p>`;
}
}
function renderMetadata(fields) {
let html = '<h3>Metadata (Columns)</h3>';
if (!fields || fields.length === 0) {
html += '<p>No columns found.</p>';
metadataContainer.innerHTML = html;
return;
}
html += '<table>';
html += '<thead><tr><th>Column ID</th><th>Label</th><th>Type</th><th>Is Formula?</th></tr></thead>';
html += '<tbody>';
fields.forEach(field => {
html += `<tr>
<td>${field.id || ''}</td>
<td>${field.label || ''}</td>
<td>${field.type || ''}</td>
<td>${field.isFormula || false}</td>
</tr>`;
});
html += '</tbody></table>';
metadataContainer.innerHTML = html;
}
function renderData(records, columnIds) {
let html = '<h3>Data (Records)</h3>';
if (!records || records.length === 0) {
html += '<p>This table has no data.</p>';
dataContainer.innerHTML = html;
return;
}
html += '<table>';
html += `<thead><tr>${columnIds.map(id => `<th>${id}</th>`).join('')}</tr></thead>`;
html += '<tbody>';
records.forEach(record => {
html += '<tr>';
columnIds.forEach(colId => {
let value = record[colId];
if (typeof value === 'object' && value !== null) {
value = JSON.stringify(value);
}
const escapedValue = (value ?? '').toString().replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">");
html += `<td><div class="raw-data">${escapedValue}</div></td>`;
});
html += '</tr>';
});
html += '</tbody></table>';
dataContainer.innerHTML = html;
}
initializeApp();
});
</script>
</body>
</html>
here… another version of the code above but with even more metadata info about each table
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Grist Document Explorer</title>
<script src="https://docs.getgrist.com/grist-plugin-api.js"></script>
<style>
:root {
--border-color: #d1d5db;
--sidebar-bg: #f9fafb;
--hover-bg: #eff6ff;
--active-bg: #dbeafe;
--header-bg: #f3f4f6;
--font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
}
html, body {
height: 100%;
margin: 0;
font-family: var(--font-family);
color: #1f2937;
background-color: #fff;
overflow: hidden;
}
.container {
display: flex;
height: 100%;
}
#sidebar {
width: 250px;
flex-shrink: 0;
background-color: var(--sidebar-bg);
border-right: 1px solid var(--border-color);
padding: 1rem;
overflow-y: auto;
}
#sidebar h2 {
margin-top: 0;
font-size: 1.1rem;
border-bottom: 1px solid var(--border-color);
padding-bottom: 0.5rem;
}
#table-list {
list-style: none;
padding: 0;
margin: 0;
}
#table-list button {
width: 100%;
padding: 0.75rem 0.5rem;
border: none;
background: none;
text-align: left;
cursor: pointer;
border-radius: 4px;
font-size: 0.9rem;
}
#table-list button:hover {
background-color: var(--hover-bg);
}
#table-list button.active {
background-color: var(--active-bg);
font-weight: 600;
}
#content-area {
flex-grow: 1;
padding: 1.5rem;
overflow: auto;
}
.placeholder, .loading, .error {
color: #6b7280;
font-style: italic;
}
.error {
color: #dc2626;
font-weight: bold;
}
.section {
margin-bottom: 2rem;
}
.section h3 {
margin-top: 0;
border-bottom: 1px solid var(--border-color);
padding-bottom: 0.5rem;
}
table {
width: 100%;
border-collapse: collapse;
font-size: 0.85rem;
}
th, td {
border: 1px solid var(--border-color);
padding: 0.5rem;
text-align: left;
vertical-align: top;
max-width: 350px;
overflow-x: auto; /* Allow horizontal scroll for wide content */
}
td {
white-space: nowrap;
}
th {
background-color: var(--header-bg);
font-weight: 600;
position: sticky;
top: 0;
}
.raw-data, pre {
font-family: monospace;
white-space: pre-wrap;
word-break: break-all;
margin: 0;
font-size: 0.8rem;
}
</style>
</head>
<body>
<div class="container">
<aside id="sidebar">
<h2>Tables</h2>
<ul id="table-list"></ul>
</aside>
<main id="content-area">
<div id="initial-placeholder" class="placeholder">
<p>Select a table from the list on the left to view its metadata and data.</p>
</div>
<div id="table-details" style="display: none;">
<h2 id="table-title"></h2>
<div id="metadata-container" class="section"></div>
<div id="data-container" class="section"></div>
</div>
</main>
</div>
<script>
document.addEventListener('DOMContentLoaded', () => {
grist.ready({ requiredAccess: 'full' });
const tableListElement = document.getElementById('table-list');
const initialPlaceholder = document.getElementById('initial-placeholder');
const tableDetailsContainer = document.getElementById('table-details');
const tableTitleElement = document.getElementById('table-title');
const metadataContainer = document.getElementById('metadata-container');
const dataContainer = document.getElementById('data-container');
const contentArea = document.getElementById('content-area');
let allTablesSchema = [];
function transformGristData(gristData) {
const records = [];
const columnIds = Object.keys(gristData);
if (columnIds.length === 0 || !gristData[columnIds[0]] || gristData[columnIds[0]].length === 0) return [];
const numRecords = gristData[columnIds[0]].length;
for (let i = 0; i < numRecords; i++) {
const record = {};
for (const colId of columnIds) {
record[colId] = gristData[colId][i];
}
records.push(record);
}
return records;
}
async function buildSchemas() {
const tablesData = await grist.docApi.fetchTable('_grist_Tables');
const columnsData = await grist.docApi.fetchTable('_grist_Tables_column');
const tables = transformGristData(tablesData);
const columns = transformGristData(columnsData);
const tableIdMap = new Map();
tables.forEach(table => tableIdMap.set(table.id, table.tableId));
const schemas = {};
const linkingKey = columns.length > 0 && 'parentId' in columns[0] ? 'parentId' : 'tableId';
columns.forEach(col => {
const tableTextId = tableIdMap.get(col[linkingKey]);
if (!tableTextId) return;
if (!schemas[tableTextId]) {
schemas[tableTextId] = { id: tableTextId, fields: [] };
}
// FIX: Push the entire, unmodified column object to preserve all metadata.
schemas[tableTextId].fields.push(col);
});
return Object.values(schemas);
}
async function initializeApp() {
try {
allTablesSchema = await buildSchemas();
const userTables = allTablesSchema.filter(table => !table.id.startsWith('_grist_'));
tableListElement.innerHTML = '';
userTables.forEach(table => {
const li = document.createElement('li');
const button = document.createElement('button');
button.textContent = table.id;
button.dataset.tableId = table.id;
button.addEventListener('click', () => {
document.querySelectorAll('#table-list button').forEach(btn => btn.classList.remove('active'));
button.classList.add('active');
loadTableDetails(table.id);
});
li.appendChild(button);
tableListElement.appendChild(li);
});
} catch (error) {
console.error("Error initializing widget:", error);
contentArea.innerHTML = `<p class="error">Failed to load tables. Error: ${error.message}. See console for details.</p>`;
}
}
async function loadTableDetails(tableId) {
initialPlaceholder.style.display = 'none';
tableDetailsContainer.style.display = 'block';
tableTitleElement.textContent = `Table: ${tableId}`;
metadataContainer.innerHTML = '<h3>Metadata (Columns)</h3><p class="loading">Loading...</p>';
dataContainer.innerHTML = '<h3>Data (Records)</h3><p class="loading">Loading...</p>';
try {
const tableSchema = allTablesSchema.find(t => t.id === tableId);
if (!tableSchema) throw new Error(`Schema for table "${tableId}" not found.`);
renderMetadata(tableSchema.fields);
const rawData = await grist.docApi.fetchTable(tableId);
const records = transformGristData(rawData);
const columnOrder = tableSchema.fields.map(f => f.colId);
renderData(records, columnOrder);
} catch (error) {
console.error(`Error loading details for table ${tableId}:`, error);
dataContainer.innerHTML = `<p class="error">Failed to load data for ${tableId}: ${error.message}</p>`;
}
}
// FIX: This function is now fully dynamic.
function renderMetadata(fields) {
let html = '<h3>Metadata (Columns)</h3>';
if (!fields || fields.length === 0) {
html += '<p>No columns found for this table.</p>';
metadataContainer.innerHTML = html;
return;
}
// Dynamically discover all possible header keys from all fields
const allKeys = new Set();
fields.forEach(field => {
Object.keys(field).forEach(key => allKeys.add(key));
});
const headers = Array.from(allKeys);
html += '<table>';
html += `<thead><tr>${headers.map(h => `<th>${h}</th>`).join('')}</tr></thead>`;
html += '<tbody>';
fields.forEach(field => {
html += '<tr>';
headers.forEach(headerKey => {
let value = field[headerKey];
// Try to parse JSON strings for better display
if (typeof value === 'string' && value.startsWith('{') && value.endsWith('}')) {
try {
value = JSON.parse(value);
} catch (e) { /* ignore if not valid json */ }
}
if (typeof value === 'object' && value !== null) {
// Pretty-print objects and arrays
const jsonString = JSON.stringify(value, null, 2);
const escapedJson = jsonString.replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">");
html += `<td><pre>${escapedJson}</pre></td>`;
} else {
const escapedValue = (value ?? '').toString().replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">");
html += `<td>${escapedValue}</td>`;
}
});
html += '</tr>';
});
html += '</tbody></table>';
metadataContainer.innerHTML = html;
}
function renderData(records, columnIds) {
let html = '<h3>Data (Records)</h3>';
if (!records || records.length === 0) {
html += '<p>This table has no data.</p>';
dataContainer.innerHTML = html;
return;
}
html += '<table>';
html += `<thead><tr>${columnIds.map(id => `<th>${id}</th>`).join('')}</tr></thead>`;
html += '<tbody>';
records.forEach(record => {
html += '<tr>';
columnIds.forEach(colId => {
let value = record[colId];
if (typeof value === 'object' && value !== null) {
value = JSON.stringify(value);
}
const escapedValue = (value ?? '').toString().replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">");
html += `<td><div class="raw-data">${escapedValue}</div></td>`;
});
html += '</tr>';
});
html += '</tbody></table>';
dataContainer.innerHTML = html;
}
initializeApp();
});
</script>
</body>
</html>
Thank you very much, i’m gonna test it and let you know!
