Best way to write to user tables from Pack formula?

I’m working on a template that has some complex actions involving extracting data from a remote API and then parsing it to add some rows to multiple tables. I had been doing this in the button action handler as an inline formula, but it’s really hard to maintain that way (especially since the full-screen expanded formula editor isn’t working right now), so I figured I’d pull it out into a pack action that looks something like:

IngestData(media_table_id, creatives_table_id, data) and then the behavior of that action would insert or update rows in those two tables based on what’s in the data.

The challenge is that you can’t pass a table itself as an input into a pack function, so I’m going to have to use the coda API and add rows that way.

To my surprise, though, I can’t find a coda pack that gives me access to that API. I am hoping that there’s some import available in the Pack Builder interface that’ll just give my logged in account access to things like getTable(id) etc?

I noticed there’s a specific auth type for Packs that want to interact with Coda’s API (CodaApiHeaderBearerToken). Unsure if you can combine that with auth for your remote API (but maybe you don’t need to?)

I’m assuming this is all for you so things like remote API auth and maybe even Coda API auth can be hard-coded? Or does this have to be more flexible for multiple users, multiple tables, etc.

I usually reach for Firebase Cloud Functions (or serverless function provider of your choice) for stuff like this when I don’t need it to be very interactive within Coda, but maybe in your use case here you do need that interactivity/extensibility.

Oh interesting, I see that this is for “interacting with coda’s API” but I don’t see an example of what that would look like in practice.

This is for a pretty simple use case. I’m building an IMDB pack, where you can look up movies and shows. I’d like to define a function in my pack that consuming documents can invoke, as a convenience, that writes data from the IMDB API to a local table.

Then I’m making a template for a Media Manager. It will use the IMDB pack, as well as a few other packs, to track a user’s media viewing history. The idea is for my template to invoke this function, passing in its table IDs so that the tables can be populated by the pack instead of having to implement that logic in every document.

Oh I think I get it. There’s no API node library I can import, I would just make the API calls directly using the REST API’s endpoints after registering the auth to use CodaApiBearerTokenAuthentication. Thanks, @Nick_HE!

Here’s my IMDB pack so far if you want to take it for a spin:

Right now it’s just movies and needs work on filtering the search results, but basically you set your column type to IMDBMovie, write a movie title in the cell, and it’ll return a rich object that you can pull columns off of for poster, runtime, etc.

Let me know if you want to collaborate on it and I’ll throw it up on github

// Initialize Packs SDK and create the pack
import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();

// IMDB doesn't have a publicly-available API, but RapidAPI provides one with free and paid tiers
// https://rapidapi.com/apidojo/api/imdb8/
pack.addNetworkDomain('rapidapi.com');
pack.setUserAuthentication({
  type: coda.AuthenticationType.CustomHeaderToken,
  headerName: "x-rapidapi-key",
  instructionsUrl: "https://coda.io/@nickhe/imdb-pack"
});

// Movie schema
const movieSchema = coda.makeObjectSchema({
  type: coda.ValueType.Object,
  properties: {
    Title: {type: coda.ValueType.String},
    Year: {type: coda.ValueType.Number},
    Runtime: {
      type: coda.ValueType.String,
      codaType: coda.ValueHintType.Duration,
      // fromKey: "runningTimeInMinutes",
    },
    Poster: {
      type: coda.ValueType.String,
      codaType: coda.ValueHintType.ImageReference,
      // fromKey: "image.url",
    },
    IMDBId: {type: coda.ValueType.String},
  },
  primary: "Title",
  featured: ["Title", "Year", "Poster"],
  id: "IMDBId",
  // identity: {name: "IMDBMovie"}
});

pack.addColumnFormat({
  name: "IMDB Movie",
  instructions: "Shows movie details from IMDB",
  formulaName: "IMDBMovie",
  formulaNamespace: "Deprecated",
});

pack.addFormula({
  name: "IMDBMovie",
  description: "Search for a movie and get details about it from IMDB",
  parameters: [
    coda.makeParameter({
      type: coda.ParameterType.String,
      name: "query",
      description: "Search IMDB (try movie name, or movie name and year)",
    }),
  ],

  resultType: coda.ValueType.Object,
  schema: movieSchema,

  execute: async function ([query], context) {
    const url = "https://imdb8.p.rapidapi.com/title/find?q=" + encodeURIComponent(query);
    const response = await context.fetcher.fetch({
      method: 'GET',
      headers: {
        "x-rapidapi-host": "imdb8.p.rapidapi.com",
      },
      url: url
    });
    const movie = response.body.results[0];
    return {
      // grab the ID (tt0109830) out of the API's id response (/title/tt0109830/)
      IMDBId: movie.id.replace("/title/","").replace("/",""),
      Title: movie.title,
      Year: movie.year,
      Runtime: movie.runningTimeInMinutes + " minutes",
      Poster: movie.image.url,
    }
  },
});

I believe you can use node dependencies using the CLI.

Neat! Mine is… a bit more ambitious haha. I’m almost ready to share it, I’ve gotta get custom auth working first.

OK cool haha, looking forward to checking it out.

What are you using for the IMDB API? Are you direct with IMDB? Are you proxying calls through your own backend? 3rd party API provider?

I’m using https://imdb-api.com, which has a bunch of neat underlying API calls and is free for up to 100 calls per day.

Oh wow yeah, that’s a lot better than the one I’m working with :sweat_smile:

I love how they pull from outside sources like Wikipedia as well (particularly since we can’t do the same thing ourselves with Coda’s single-domain API calls for Packs).

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