Trying to Extract Data from a Table in a Formula and Hitting a Pack Limit or Rate Limt

1

For context, I have created a Document automation that triggers on the first day of the month and use 3 custom coda pack formulas I wrote to performs the following steps:

  1. Formula 1 - Extract a table’s data to json and drop it in blob storage available through the pack context, return the blob storage url as the result. Blob storage reference: Interface: TemporaryBlobStorage - Coda Pack SDK
  2. Formula 2 - call a custom api written at my company to get an AWS S3 presigned url to upload a file to S3. Returns the presigned url as the result.
  3. Formula 3 - Takes the result as formula 1 and 2 as input (blob url and presigned url) and uses it to pull the data from blob storage and Upload to S3.

Step 2 and 3 work flawlessly and so does Formula 1 on small tables. In order to pull large amounts of data (3500 rows), I implemented paging on the api calls, but after 7-8 calls i get an an error:

image
image1638×774 122 KB

I thought it might be rate limiting, but with the available libraries in the pack, I can’t implement a back off retry, because there is no way to implement a sleep. Also, from looking at the docs, I should be well within the rate limits published in Coda’s documentation, since I am only reading data: Coda API (v1) Reference Documentation

Maybe, this includes all user concurrently using the site, not sure..

For reference here is my coda pack code for formula 1:

execute: async function ([docId, tableId], context) {
    //build query
    let url = coda.withQueryParams(
      "https://coda.io/apis/v1/docs/" + docId + "/tables/" + tableId + "/rows", {
      limit: 100,
      useColumnNames: true,
      valueFormat: "simpleWithArrays",
    });

    //make call
    let result = [];
    do {
      let response = await context.fetcher.fetch({
        method: "GET",
        url: url,
        cacheTtlSecs: 0
      });

      let rows = response.body.items;

      for (let row of rows) {
        result.push(JSON.stringify(row.values));
      }

      url = "" + response.body.nextPageLink;
      
    }
    while (url.includes("http"));

    let resultJson = "[\n" + result.join(",\n") + "\n]";
    let buffer = Buffer.from(resultJson);

    let blobUrl = await context.temporaryBlobStorage.storeBlob(buffer, "application/json");

    return blobUrl;
  }

Any Insight would be greatly appreciated.

2

Hey Chris and welcome!
This is an interesting use case! It seems to be a bit much to do all within a single formula though, my best guess is that it times out but that’s a weird error regardless

Perhaps a workaround could be turning into a sync table and use the continuation pattern there

Best of luck!

3

Thanks Rickard,

I have used sync tables before, but I am trying to sync to S3 from a table.

1 Like
5

Hi @Chris_Cummings - Your code seems to be running into the ~1 minunite max runtime for a Pack execution. As @Rickard_Abraham mentioned, the only way to exceed 1 minute of total work is to use continuation in a sync table, but to your point, I’m not sure that’s a good fit for what you are trying to accomplish.

Given the system’s limitations, I think the best path forward is likely to move the majority of the logic to a server outside of Coda and use a Pack that triggers that server. There you could work with large tables without the time limitations.

1 Like