How to create a sync table in Coda Packs

Nov 10

Why consider sync tables in Coda - the evolution of docs and work

Sync tables are a means to enrich your experience of internal operations process management. As a business owner/team member, you may use multiple tools to do different jobs as part of your business. Coda is one of the most flexible tools for building digital business management systems, including knowledge management, project management, CRM, and more depending on your specific use cases. However, Coda may not replace all your tools yet. As a user, you may find Coda Packs valuable—because they allow you to sync data from third-party tools to your Coda docs with a high degree of customization and without relying on additional automation software such as Make, Zapier, n8n, or similar. Especially if your vision is to make Coda your main source of truth, Coda Packs can support you in achieving that objective.

What is a Coda Pack - the power of custom code to enrich your next-generation doc

What exactly are Coda Packs, you may be wondering. Coda Packs are an extension of your Coda docs, customized for your use cases. Anyone can build custom Coda Packs using Typescript in the dedicated Coda Packs environment accessible from the Coda workspace sidebar menu. Coda Packs can be made public and accessible to anyone for free or monthly payment, or they can be kept private for use only within your organization. Due to the wildly flexible nature of Coda Packs (which can be custom-coded, as explained above), there are many different types of Packs. You can find examples in the official Coda Packs directory. Some packs extend Coda’s capabilities and features by using the Coda API or similar. Other Coda Packs sync data from and to external tools to Coda, to turn your Coda docs into the single source of truth for your business operations. This essay focuses on a feature of the latter type of pack: how to create sync tables in Coda Packs.

How to create a sync table between third-party tool and Coda - key principles

Before delving into the specific steps involved in creating a sync table in a Coda Pack, let’s define a sync table. In Coda, a sync table is a non-native table (although it looks pretty much the same) that syncs data from and to a third-party tool into Coda docs. A table is a structured display of information, composed of columns of specific data types, and expandable rows of data. Tables can be seamlessly integrated within Coda docs as part of the flow of information, to tell important stories through data. Sync tables refresh at a specific frequency that you can define (e.g., every day, manually, every hour—if on the team plan). You can also allow users to edit the data of specific columns in the sync table directly from Coda, and sync changes back to the source third-party tool with the click of a button.

Below are the key steps and principles to create a sync table in a Coda Pack, following a specific example of a Pipedrive Coda Pack I have been developing recently. This guide is mostly meant for beginners, as I consider myself a beginner in using Typescript, but I have gone through many months of experimentation and practical learning, so my skills may be one step ahead of yours, in which case you can benefit from this guide because it saves you time with experimenting and provides a concise guide to building sync tables in Coda Packs.

Follow the API documents of the third-party tool throughout your pack creation

This guiding principle may sound obvious, and it is worth outlining. There are two documentation sources you want to follow attentively throughout your Coda Pack building process when developing a sync table:

The official Coda Packs SDK - this is your source of truth for troubleshooting, syntax correctness, and overall guidance on building Coda Packs. It includes examples of code for any use case of a Coda Pack and helpful tutorials.
The official API documents of the third-party tool (e.g., Pipedrive) - when creating sync tables in a Coda Pack, you will need to understand and define the tables you would like to create and their schema, as well as make API calls to the third-party tool within your Coda Pack. All this knowledge can be found in and refined through your judgment via the API documentation of the tool you intend to sync with Coda via the Coda Pack.

2. Allow users to authenticate

Before building any sync table in your Coda Pack, you need to define the authentication flow of users. Explaining authentication flows in Coda Packs is outside of the scope of this essay but worth mentioning nonetheless. You can find detailed information about the available authentication flows in the Coda Packs SDK here. The type of authentication flow is determined by the third-party tool's preferred method, which you will find in their API documentation. For example, Pipedrive uses OAuth 2.0 authentication, whose details for implementation can be found here.

3. Define the schema of the table you want to create

Before creating your first sync table, it can be beneficial to define all the tables you intend to sync through your Coda Pack and any relationship that exists among them. This enables you to begin with a solid foundation before proceeding with the implementation of the code for syncing the chosen tables.

Once that is in your mind and well-defined, you can begin building your first sync table, by defining its schema in the Coda Packs development environment. Defining a table’s schema involves declaring all the properties of the table and their respective data type in Coda (e.g., string, number, select, etc.). Each property can also contain a description (which will be visible to users via the “Info” icon on the column), the “required” key if it is required by the API, and a “mutable” parameter if you would like to allow users to edit the values in the column in Coda (for two-way sync purposes—see the dedicated section below). Finally, sync table schemas need to contain a “displayProperty” and “idProperty” object properties. These specify which is the primary key and the unique ID in the Coda sync table. For a Pipedrive “Deals” sync table, the table schema may look like this:

// A schema defining the data in the Deals sync table.
const DealSchema = coda.makeObjectSchema({
  properties: {
    title: {
      description: "The name of the deal.",
      type: coda.ValueType.String,
      required: true,
      mutable: true,
    },
    status: {
      description: "A deal status.",
      type: coda.ValueType.String,
    },
    // Reference a stage from the stages sync table.
		// not mentioned specifically in the essay, but you can watch 
		// a video showing this here https://youtu.be/PLLUIp0Z26k
    stage: StagesReferenceSchema,
    stageId: {
      description: "The ID of the stage.",
      type: coda.ValueType.Number,
      required: true,
      fromKey: "stage_id",
    },
    dealId: {
      description: "The ID of the deal.",
      type: coda.ValueType.Number,
      required: true,
      fromKey: "id",
    },
    value: {
			type: coda.ValueType.Number,
      mutable: true,
            },
  },
  displayProperty: "title",
  idProperty: "dealId",
  featuredProperties: ["status", "stage", "dealId"],
});

4. Define the API call to the data you want to sync

The next step in creating a sync table through Coda Packs is to define the sync table logic and execute the async function to call the API endpoint required to retrieve data in the sync table, as well as define the response format, which you will get from the API docs. Below you can see the example of making a GET request to the /deals endpoint of the Pipedrive API, after defining the logic of the sync table. Finally, the code below defines the response format and loops through the list of results, also handling the logic of the “Stage ID” column (which is related to another sync table called “Stages”), to ensure it works properly in case the “Stage ID” in Pipedrive is empty.

// The definition and logic for the Deals sync table.
pack.addSyncTable({
  name: "Deals",
  schema: DealSchema,
  identityName: "Deal",
  formula: {
    name: "Syncdeals",
    description: "Sync deals",
    parameters: [],
    execute: async function ([], context) {
      let url = "https://api.pipedrive.com/v1/deals";
      let response = await context.fetcher.fetch({
        method: "GET",
        url: url,
      });

let results = response.body.data;
for (let result of results) {
  if (result.stage_id) {
    result.stage = {
      id: result.stage_id,
      name: "Not found",
    };
  }
};
      return {
        result: results,
      };
    },

5. (Optional) Add two-way sync

Coda 4.0 included the release of two-way sync options in Coda Packs (and cross-docs). This enables users to edit data from a Coda sync table and push data back to the main source of truth (e.g., Pipedrive) on specific columns you can define in the pack code, and either automatically or through user confirmation via the click of a button. This webinar by Coda Developer Advocate Erik Koleda explains two-way sync in Coda Packs in depth. At its foundation, there are three important steps to do when it comes to activating two-way sync in a Coda sync table:

Include “mutable”: true in the properties you want users to edit in Coda
Add a dedicated function that handles row updates in the Coda sync table
Execute the necessary API calls to update the object data from Coda to the third-party tool

Below is an example code that contains the logic for two-way sync in the “Deals” table on the Pipedrive pack. This code follows from the code above in step #4.

// Function that handles the row updates.
    executeUpdate: async function (args, updates, context) {
      // By default only one row is processed at a time.
      let update = updates[0];
      let { previousValue, newValue } = update;
      let dealID = newValue.id;
      let stageId = newValue.stage_id;
// Update the properties of the deal.
      let response = await context.fetcher.fetch({
        url: `https://api.pipedrive.com/v1/deals/${dealID}`,
        method: "PUT",
        headers: {
          "Content-Type": "application/json",
        },
        body: JSON.stringify(newValue),
      });
// Return the updated deal.
      let updated = response.body.data;
      if (updated.stage_id) {
    updated.stage = {
      id: updated.stage_id,
      name: "Not found",
    };
  };
      return {
        result: [updated],
      };
    },

  },
});

Use sync tables in your Coda Docs and enrich your experience

Sync tables can add a layer of centralization to your data across the entire business, which can make Coda docs your main source of truth for high-level overviews of data and accessibility for all stakeholders. This increases clarity of information and communication in your teams, which can increase morale, efficiency, and overall effectiveness in conducting your daily operations. This post delved into an example of how to create a sync table in a Coda pack, from a beginner to beginners. For any questions or feedback on the content I shared in this post, you can contact me here.