Column format samples¶
A column format is a custom column type that you apply to any column in any Coda table. A column format tells Coda to interpret the value in a cell by executing a formula using that value, typically looking up data related to that value from an external API.
For example, the Weather pack has a column format Current Weather
; when applied to a column, if you type a city or address into a cell in that column, that location will be used as an input to a formula that fetches the current weather at that location, and the resulting object with weather info will be shown in the cell.
Template¶
The basic structure of a column format.
pack.addColumnFormat({
name: "My Column Format",
instructions: "My description.",
formulaName: "MyFormula",
matchers: [
// TODO: If formatting a URL, add a regular expression that matches it.
],
});
Text (Reverse)¶
A column format that formats text. This sample displays the text in the cell in reverse.
import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();
// Adds a column format to the Pack, which will display the contents of the
// column in reverse order.
pack.addColumnFormat({
name: "Reversed Text",
instructions: "Whatever text you enter into this column will be reversed.",
// The formula "Reverse()" (defined below) will be run on the content of the
// column to determine it's display value.
formulaName: "Reverse",
});
// Adds a formula to this Pack to reverse text. It is used by the column format
// above, but can also be used on it's own anywhere in the doc.
pack.addFormula({
resultType: coda.ValueType.String,
name: "Reverse",
description: "Reverses text.",
parameters: [
// Formulas used in column formats can have only one required parameter.
coda.makeParameter({
type: coda.ParameterType.String,
name: "input",
description: "The text to reverse.",
}),
// Optional parameters can't be set when run as a column format.
coda.makeParameter({
type: coda.ParameterType.Boolean,
name: "byWord",
description: "Reverse the text word-by-word.",
suggestedValue: false,
optional: true,
}),
],
execute: async function ([input, byWord = false]) {
let separator = "";
if (byWord) {
separator = " ";
}
return input.split(separator).reverse().join(separator);
},
});
Text (Roman Numeral)¶
A column format that formats a number as text. This sample displays the number in the cell as a Roman numeral.
import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();
// Adds a column format to the Pack, which will display the contents of the
// column as Roman numerals.
pack.addColumnFormat({
name: "Roman Numeral",
instructions: "Displays the number as a Roman numeral.",
formulaName: "RomanNumeral",
});
// Adds a formula to this Pack to convert a number to a Roman numeral. It is
// used by the column format above, but can also be used on it's own anywhere in
// the doc.
pack.addFormula({
name: "RomanNumeral",
description: "Converts a number to the equivalent Roman numeral.",
parameters: [
coda.makeParameter({
type: coda.ParameterType.Number,
name: "value",
description: "The number to convert.",
}),
],
resultType: coda.ValueType.String,
execute: async function ([value], context) {
let pairs = Object.entries(NumberMapping);
// Sort the pairs by the number, largest to smallest.
pairs.sort((a, b) => b[1] - a[1]);
let result = "";
for (let [roman, num] of pairs) {
while (value >= num) {
result += roman;
value -= num;
}
}
return result;
},
});
const NumberMapping = {
I: 1, IV: 4, V: 5, IX: 9, X: 10, XL: 40, L: 50, XC: 90, C: 100, CD: 400,
D: 500, CM: 900, M: 1000,
};
Text (Progress Bar)¶
A column format that formats a number as graphic. This sample displays the number in the cell as a progress bar.
import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();
// Adds a column format to the Pack, which will display the contents of the
// column as a progress bar.
pack.addColumnFormat({
name: "Progress Bar",
instructions: "Draws a progress bar with the given percentage.",
formulaName: "ProgressBar",
});
// Adds a formula to this Pack to draw a number as a progress bar. It is used by
// the column format above, but can also be used on it's own anywhere in the
// doc.
pack.addFormula({
name: "ProgressBar",
description: "Draws a progress bar.",
parameters: [
coda.makeParameter({
type: coda.ParameterType.Number,
name: "percentage",
description: "The percentage complete, as a number between 0 and 1.",
}),
],
resultType: coda.ValueType.String,
execute: async function ([percentage], context) {
if (percentage < 0 || percentage > 1) {
throw new coda.UserVisibleError("Percentage must be between 0 and 1.");
}
let chars = Math.floor(percentage * 10);
return "⬛".repeat(chars) + "⬜".repeat(10 - chars);
},
});
Image (Cats)¶
A column format that formats text as an image. This sample displays the text in the cell as an overlay on a random image of a cat.
import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();
// Column format that displays the cell's value within a random cat image,
// using the CatImage() formula defined above.
pack.addColumnFormat({
name: "Cat Image",
instructions: "Displays the text over the image of a random cat.",
formulaName: "CatImage",
});
// Formula that fetches a random cat image, with various options.
pack.addFormula({
name: "CatImage",
description: "Gets a random cat image.",
parameters: [
coda.makeParameter({
type: coda.ParameterType.String,
name: "text",
description: "Text to display over the image.",
}),
],
resultType: coda.ValueType.String,
codaType: coda.ValueHintType.ImageReference,
execute: async function ([text], context) {
let url = "https://cataas.com/cat/says/" + encodeURIComponent(text);
url = coda.withQueryParams(url, {
json: true,
});
let response = await context.fetcher.fetch({
method: "GET",
url: url,
cacheTtlSecs: 0, // Don't cache the result, so we can get a fresh cat.
});
return "https://cataas.com" + response.body.url;
},
});
// Allow the pack to make requests to Cat-as-a-service API.
pack.addNetworkDomain("cataas.com");
Rich data (Todoist)¶
A column format that formats a URL as rich data. This sample displays the URL of the Todoist task in the cell as a rich data chip.
import * as coda from "@codahq/packs-sdk";
// Regular expressions that match Todoist task URLs. Used by the column format
// and also the formula that powers it.
const TaskUrlPatterns: RegExp[] = [
new RegExp("^https://todoist.com/app/task/([0-9]+)$"),
new RegExp("^https://todoist.com/app/project/[0-9]+/task/([0-9]+)$"),
new RegExp("^https://todoist.com/showTask\\?id=([0-9]+)"),
];
export const pack = coda.newPack();
// Add a column format that displays a task URL as rich metadata.
pack.addColumnFormat({
name: "Task",
// The formula "Task" below will get run on the cell value.
formulaName: "Task",
// If the first values entered into a new column match these patterns then
// this column format will be automatically applied.
matchers: TaskUrlPatterns,
});
// A schema defining the rich metadata to be returned.
const TaskSchema = coda.makeObjectSchema({
properties: {
name: {
description: "The name of the task.",
type: coda.ValueType.String,
required: true,
},
description: {
description: "A detailed description of the task.",
type: coda.ValueType.String,
},
url: {
description: "A link to the task in the Todoist app.",
type: coda.ValueType.String,
codaType: coda.ValueHintType.Url,
},
id: {
description: "The ID of the task.",
type: coda.ValueType.String,
required: true,
},
},
displayProperty: "name",
idProperty: "id",
});
// Formula that looks up rich metadata about a task given it's URL. This is used
// by the "Task" column format above, but is also a regular formula that can be
// used elsewhere.
pack.addFormula({
name: "Task",
description: "Gets a Todoist task by URL",
parameters: [
coda.makeParameter({
type: coda.ParameterType.String,
name: "url",
description: "The URL of the task",
}),
],
resultType: coda.ValueType.Object,
schema: TaskSchema,
execute: async function ([url], context) {
let taskId = extractTaskId(url);
let response = await context.fetcher.fetch({
url: "https://api.todoist.com/rest/v2/tasks/" + taskId,
method: "GET",
});
let task = response.body;
return {
name: task.content,
description: task.description,
url: task.url,
id: task.id,
};
},
});
// Helper function to extract the Task ID from the URL.
function extractTaskId(taskUrl: string) {
for (let pattern of TaskUrlPatterns) {
let matches = taskUrl.match(pattern);
if (matches && matches[1]) {
return matches[1];
}
}
throw new coda.UserVisibleError("Invalid task URL: " + taskUrl);
}
// Allow the pack to make requests to Todoist.
pack.addNetworkDomain("todoist.com");
// Setup authentication using a Todoist API token.
pack.setUserAuthentication({
type: coda.AuthenticationType.HeaderBearerToken,
instructionsUrl: "https://todoist.com/app/settings/integrations",
});