Pack Issues: Dynamic Sync Table Issues w/ Parameters

Making Packs
1

I’m having an issue with the pack that I just can’t figure out. Essentially, I’m creating a pack similar to cross doc but with parameters to allow for a custom Doc ID and a custom Table ID.

I want to use those custom parameters to correctly formulate the schema urls and the GET urls for the rows. For some reason it’s correctly getting the rows for the Sync table with the custom inputs, but it is not getting the parameters whenever running the getSchema functions.

Noitce the difference in URLs with the tableIDs which are different

grid-jgcRoSZjoD = OLD, not the parameter input
grid-naEas2IDNF - GOOD, because the parameter input is present.

A refresh of sync table SyncTable ran successfully
5 mins ago
Overview: A refresh of sync table SyncTable ran successfully
  GET https://coda.io/apis/v1/docs/_bN106Kq_A/tables/grid-naEas2IDNF/rows HTTP 200 (OK)
  getSchema function ran successfully
5 mins ago
Overview: getSchema function ran successfully
  GET https://coda.io/apis/v1/docs/_bN106Kq_A/tables/grid-jgcRoSZjoD/columns/c-VSeP4Axo5t HTTP 200 (OK)
  GET https://coda.io/apis/v1/docs/_bN106Kq_A/tables/grid-jgcRoSZjoD/columns/c-WZ6BbUM6Hc HTTP 200 (OK)
  GET https://coda.io/apis/v1/docs/_bN106Kq_A/tables/grid-jgcRoSZjoD/columns/c-i_WThQQNip HTTP 200 (OK)
  GET https://coda.io/apis/v1/docs/_bN106Kq_A/tables/grid-jgcRoSZjoD/columns HTTP 200 (OK)
  Dynamic URL: _bN106Kq_A|grid-jgcRoSZjoD
  Parameters:

Also notice that last line reporting nothing is in the parameters, when there is something present.

image

Here is the pack code. Help is appreciated!

// Import the necessary dependencies from the Codahq packs-sdk.
import * as coda from "@codahq/packs-sdk";

// Create a new pack instance.
export const pack = coda.newPack();

// Set user authentication for the pack using Coda API header bearer token.
pack.setUserAuthentication({
  type: coda.AuthenticationType.CodaApiHeaderBearerToken,
  shouldAutoAuthSetup: true,
});

// Add the network domain for Coda.
pack.addNetworkDomain("coda.io");

// Define a function to fetch the list of available documents.
async function getAvailableDocs(context: coda.ExecutionContext) {
  let url = "https://coda.io/apis/v1/docs";
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });
  return response.body.items;
}

// Define a function to fetch tables within a specific document.
async function getTablesInDocument(context: coda.ExecutionContext, documentId: string) {
  let url = `https://coda.io/apis/v1/docs/${documentId}/tables`;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });
  console.log("Response from getTablesInDocument:", response.body.items);
  return response.body.items;
}

// Define a function to fetch details of the tables within a document.
async function getTableDetails(context: coda.ExecutionContext, docId: string, tableId: string) {
  let url = `https://coda.io/apis/v1/docs/${docId}/tables/${tableId}`;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });

  let displayColumn = response.body.displayColumn;

  return {
    ...response.body,
    displayColumn: displayColumn,
  };
}

// Define a function to fetch data from a specific table within a document.
async function getTableData(context: coda.ExecutionContext, docId: string, tableId: string, nextPageToken?: string) {
  let url = `https://coda.io/apis/v1/docs/${docId}/tables/${tableId}/rows`;

  if (nextPageToken) {
    url += `?pageToken=${nextPageToken}`;
  }

  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });

  return response.body;
}

// Define a function to fetch columns of a specific table within a document.
async function listColumns(context: coda.ExecutionContext, docId: string, tableId: string) {
  let url = `https://coda.io/apis/v1/docs/${docId}/tables/${tableId}/columns`;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });

  return response.body.items;
}

// Define a function to fetch details of a specific column within a table.
async function getColumnDetails(context: coda.ExecutionContext, docId: string, tableId: string, columnId: string) {
  let url = `https://coda.io/apis/v1/docs/${docId}/tables/${tableId}/columns/${columnId}`;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });
  return response.body;
}

// Define a function to generate a property schema for a column.
function getPropertySchema(column): coda.Schema & coda.ObjectSchemaProperty {
  if (column.name === "RowID") {
    return { type: coda.ValueType.String, fromKey: "RowID" };
  }
  switch (column.type) {
    case "yes_no":
      return { type: coda.ValueType.Boolean, fromKey: column.name };
    case "number":
      return { type: coda.ValueType.Number, fromKey: column.name };
    case "date":
      return {
        type: coda.ValueType.String,
        codaType: coda.ValueHintType.Date,
        fromKey: column.name,
      };
    default:
      return { type: coda.ValueType.String, fromKey: column.name };
  }
}

// Add a dynamic sync table to the pack.
pack.addDynamicSyncTable({
  name: "SyncTable",
  description: "Details of a table from a specific Coda doc.",
  identityName: "Row",

  // Define a function to list dynamic URLs for available documents and tables.
  listDynamicUrls: async function (context, documentId) {
    if (!documentId) {
      // If documentId is not provided, fetch available documents.
      let docs = await getAvailableDocs(context);
      // Map available documents to dynamic URLs.
      return docs.map(doc => ({
        display: doc.name,
        value: doc.id,
        hasChildren: true,
      }));
    }

    // If documentId is provided, fetch tables within the document.
    let tables = await getTablesInDocument(context, documentId);
    // Map tables to dynamic URLs with concatenated values of docId|tableId.
    return tables.map(table => ({
      display: table.name,
      value: `${documentId}|${table.id}`,
    }));
  },

  // Define a function to generate a schema for the dynamic table.
getSchema: async function (context, parameters) {
  console.log("Parameters:", parameters);

  // Extract document and table IDs from the dynamic URL.
  let concatenatedValue = context.sync.dynamicUrl;
  console.log("Dynamic URL:", concatenatedValue);

  let [defaultDocId, defaultTableId] = concatenatedValue.split("|");

  // Use parameters or defaults to determine docId and tableId.
  let docId = parameters["docIdParam"] || defaultDocId;
  let tableId = parameters["tableIdParam"] || defaultTableId;

  // Fetch columns of the specified table.
  let columns = await listColumns(context, docId, tableId);

  // Determine the display column for the schema.
  let displayColumnName = "RowID";
  for (let column of columns) {
    let columnDetails = await getColumnDetails(context, docId, tableId, column.id);
    if (columnDetails.display) {
      displayColumnName = column.name;
      break;
    }
  }

  // Generate property schemas for each column.
  let properties: coda.ObjectSchemaProperties = {};
  for (let column of columns) {
    properties[column.name] = getPropertySchema(column);
  }
  properties["RowID"] = { type: coda.ValueType.String };

  // Return the generated schema.
  return {
    type: coda.ValueType.Object,
    idProperty: "RowID",
    properties: properties,
    displayProperty: displayColumnName,
  };
},

  // Define a function to get the name of the dynamic table.
  getName: async function (context, parameters) {
    // Extract document and table IDs from the dynamic URL.
    let concatenatedValue = context.sync.dynamicUrl;
    let [defaultDocId, defaultTableId] = concatenatedValue.split("|");
    // Use parameters or defaults to determine docId and tableId.
    let docId = parameters["docIdParam"] || defaultDocId;
    let tableId = parameters["tableIdParam"] || defaultTableId;

    // Fetch details of the specified table.
    let tableDetails = await getTableDetails(context, docId, tableId);
    return tableDetails.name;
  },

  // Define a function to get the display URL for the dynamic table.
  getDisplayUrl: async function (context) {
    // Extract document and table IDs from the dynamic URL.
    let concatenatedValue = context.sync.dynamicUrl;
    let [docId, tableId] = concatenatedValue.split("|");

    // Fetch details of the specified table.
    let tableDetails = await getTableDetails(context, docId, tableId);
    return tableDetails.browserLink;
  },

  // Define a formula function to sync the dynamic table.
  formula: {
    name: "SyncTable",
    description: "Sync the table details",
    parameters: [
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "docIdParam", // Rename to docIdParam
        description: "If you'd like to change the original Doc ID, enter the new one here.",
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "tableIdParam", // Rename to tableIdParam
        description: "If you'd like to change the original Table ID, enter the new one here.",
        optional: true,
      }),
    ],
execute: async function ([docIdParam, tableIdParam], context) {
  // Extract document and table IDs from the dynamic URL.
  let concatenatedValue = context.sync.dynamicUrl;
  let [defaultDocId, defaultTableId] = concatenatedValue.split("|");
  // Use parameters or defaults to determine docId and tableId.
  let docId = docIdParam !== undefined ? docIdParam : defaultDocId;
  let tableId = tableIdParam !== undefined ? tableIdParam : defaultTableId;

  // Fetch all rows from the table.
  let allRows = [];
  let nextPageToken;
  do {
    let tableData = await getTableData(context, docId, tableId, nextPageToken);
    allRows.push(...tableData.items);
    nextPageToken = tableData.nextPageToken;
  } while (nextPageToken);

  // Fetch columns of the specified table.
  let columns = await listColumns(context, docId, tableId);
  let columnIdToName = {};
  for (let column of columns) {
    columnIdToName[column.id] = column.name;
  }

  // Transform rows and return the result.
  let transformedRows = allRows.map(row => {
    let transformedRow: any = { RowID: row.id };
    for (let [columnId, value] of Object.entries(row.values)) {
      let columnName = columnIdToName[columnId];
      transformedRow[columnName] = value;
    }
    return transformedRow;
  });

  return {
    result: transformedRows,
  };
},

  },
});
2

Hi @Micah_Lucero - I think the issue may be this bit of code:

getSchema: async function (context, parameters) {

The method signature of the getSchema function has the sync table parameters as the third argument, not the second. So it should look like:

getSchema: async function (context, _, parameters) {

The second parameter doesn’t have any use in getSchema, but is an artifact of the same underlying “metadata function” being used by lots of different parts of the sync table.

Your URLs contained the wrong IDs because they were falling back to the default values, as per this code:

let docId = parameters["docIdParam"] || defaultDocId;
let tableId = parameters["tableIdParam"] || defaultTableId;

Might be best to remove these defaults for testing, or add some logging when they get used.

3

More information about parameter access in getSchema is available here:

4

This was the issue! Thank you so much, everything works now!

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.