Accessing a regular table from a pack

Making Packs
1

Hello,
I’m trying to create a Pack which uses data from a regular coda table. The Pack function has inputs which it compares to the table content with various statistics then the function returns a result. Currently the Pack is used in the same doc as the table is in but in future, the pack would be also used in other docs but still use the table from the original doc. Claude sonnet gave it’s best attempts but they failed (see below). What is the best way/is there a way to easily reference an regular table in a coda pack?

First attempt:

execute: async ([sex, bodyWeight, liftType, weightLifted], context) => {
const data = await context.fetcher.fetchTable(“LiftCategoryData”, {Sex: sex, LiftType: liftType});

error: * On line 279 Property ‘fetchTable’ does not exist on type ‘Fetcher’.

Second attempt:

execute: async ([sex, bodyWeight, liftType, weightLifted, age], context) => {
const data = await context.sync.fetchDataFromTable(“LiftCategoryData”, {Sex: sex, LiftType: liftType});
`
Error: * On line 301 Property ‘fetchDataFromTable’ does not exist on type ‘Sync’.

Third attempt:

execute: async ([sex, bodyWeight, liftType, weightLifted, age], context) => {
const table = coda.getTable(“LiftCategoryData”);
const data = await table.selectRows({
filter: coda.andFilters([
coda.makeFilter(table.getColumn(“Sex”), coda.FilterOperator.Equal, sex),
coda.makeFilter(table.getColumn(“LiftType”), coda.FilterOperator.Equal, liftType)
])
});

Errors:

  • On line 301 Property ‘getTable’ does not exist on type ‘typeof import(“@codahq/packs-sdk”)’.
  • On line 303 Property ‘andFilters’ does not exist on type ‘typeof import(“@codahq/packs-sdk”)’.
  • On line 304 Property ‘makeFilter’ does not exist on type ‘typeof import(“@codahq/packs-sdk”)’.
  • On line 304 Property ‘FilterOperator’ does not exist on type ‘typeof import(“@codahq/packs-sdk”)’.
  • …

Fourth attempt:

execute: async ([sex, bodyWeight, liftType, age], context) => {
const data = await context.fetcher.fetchData(‘LiftCategoryData’, {
Sex: sex,
LiftType: liftType
});

Error:

  • On line 301 Property ‘fetchData’ does not exist on type ‘Fetcher’.

Fifth attempt:

execute: async ([sex, bodyWeight, liftType, weightLifted, age], context) => {
const data = await context.sync.fetchSyncTable(“LiftCategoryData”);
const filteredData = data.filter(row => row.Sex === sex && row.LiftType === liftType);

Error:

  • On line 301 Property ‘fetchSyncTable’ does not exist on type ‘Sync’.

Sixth attempt:

execute: async ([sex, bodyWeight, liftType, weightLifted, age], context) => {
const response = await context.fetcher.fetch({
method: “GET”,
url: “https://coda.io/apis/v1/docs/{doc_id}/tables/{table_id}/rows”,
headers: {
“Authorization”: “Bearer {your_coda_api_token}”
}
});

I didn’t try this one out as I couldn’t figure out what URL to use for this table and it seems overly complicated. But if it’s the only way to do it, that would be good to know.

Thanks,
Patrick

2

Its a pity that after 5 attempts, you didnt go ahead with your 6th option - which is a valid approach - you just need to work out the doc_id and the table_id etc. to make it work.

The coda pack mechanism is not really designed to provide access to entire tables from the users document (as pointed out by @Eric_Koleda, Coda Developer Champion, and one of the architects of the Coda Pack Ecosystem).

So the preferred approach is to pass the details for the row you need over to the pack as a set of parameters. So the logic for selecting rows and marshalling the parameters is written in CFL in the document. Each call to a pack should contain a small payload of data, and do a single task - not process a whole table. Something like this…

  LiftAnalyzer( Sex, BodyWeight, LiftType, LiftWeight, Age)

HOWEVER…

We (agile-dynamics.com) have been able to pass small tables to packs using various techniques - so I outline some of them here for you to consider…

1. Pass the table as a JSON structure.

Add a JSON column to the table and give it a formula that generates a JSON object containing the columns that you want to pass to the pack. We tend to abbreviate the column names in this JSON, just to minimise the byte-count. The formula looks like this:

Format(
    '{"Sex":"{1}", "BodWt":"{2}", "Type":"{3}","LiftWt":"{4}","Age":"{5}}', 
    sex, bodyWeight, weightLifted, age
)

Then you can pass the entire table to a pack using MyPack(Table.JSON,…)
And your pack gets the table as a JSON string containing an array of rows which you can convert into a JS object in your pack.

2. Pass the table as a MarkDown structure.

Add a MarkDown column to the table and give it a formula that generates a MarkDown string of the row containing the columns that you want to pass to the pack. The formula looks like this:

Format(
    '|{1}|{2}|{3}|{4}|{5}|', 
   sex, bodyWeight, weightLifted, age
)

You will need to add the table-header that looks like this…

| sex | BodyWeight | WeightLifted | Type | LiftWeight | Age |
|-----|------------|--------------|------|------------|-----|

Then you can pass the entire table to a pack using this MarkDown formatted string.
Then the pack can parse the mark-down string to construct the table and access the values.

3. Pass the Table as a HTML structure. (Depricated)

This approach works but is depricated as it uses a hidden, undocumented, unsupported formula ToHtml() which can only be used in an action-formula (a button or an automation).

Place the table in question on its own page in your document. Then use the ToHtml(PageName) formula to generate a HTML text string of the page.

This can then be passed as a string to your pack like this; MyPack(ToHtml(PageName),...) And you then parse the HTML string to find the <table>...</table> structure, and extract the values you need inside your pack.

4. Pass the Table-ID and Fetch via the Coda API

This is probably the most straight-forward method (figuring out the API URL is not difficult).

The Document-ID is available in the context object inside the pack.
The Table-ID is easy to get from within the document, but you can also use the TableName instead.

The process of reading the table via the Fetcher is straight-forward, but you will need to loop over several continuations to receive all the rows.

But the Coda API is far from instantanious - so this will be a slow process. Several seconds.

5. Use a 2-Way Sync Table

This is a more advanced method but is probably the best Coda-Like solution.

Conclusion

You really should not need to pass an entire table to a well-designed pack.
Try to design your packs such that each one does a single, simple, atomic action on a simple set of parameters. You can chain several packs to perform more complex processes.

This aligns with the design intention of the pack mechanism, and places the overall process logic inside the document using CFL, where it can be maintained by the document user.

(CFL = ‘Coda Formula Language’)

Max

2 Likes
3

It is worth noting that a Pack can use the Coda API to retrieve the contents of a table, but the user experience isn’t great, since the API operates on a snapshot of the table that can be a few seconds to a few minutes behind the current state in the doc.

In most cases you are better off moving this logic outside of a Pack and into the doc. For instance, use Cross-doc to copy the table into another doc and then use regular formulas to operate on the data.

2 Likes