I have decided to build a Coda Pack even though I’m not a programmer. It’s been fun. I managed to get it to work with many copying and pasting from the samples available.
This particular pack access my country’s mail company API and return the shipping cost of a parcel to be sent to my client.
I created a action formula that I want to use like that in Coda:
The thing is the API returns an object and Coda doesn’t handle it very well.
Example:
{“Codigo”:“4014”,“Valor”:“40,94”,“ValorMaoPropria”:“0,00”,“ValorAvisoRecebimento”:“0,00”,“ValorValorDeclarado”:“2,74”,“Erro”:"",“MsgErro”:"",“ValorSemAdicionais”:“38,20”}
What’s the best approach to parse this object inside a table. I don’t need all of this data but it would be interesting to get at least 3 values from this object?
Maybe I could return a array with these values.
(First, an aside: since you’re not modifying anything on your server, just getting data from your shipping provider, you want to make a regular “formula” in the Pack rather than an “action”).
When making a Pack, you can have a formula return an object like the one you have. It will appear as a “chip” (with rounded rectangle on it) that when you hover your mouse will show you all the details, like this:
In formulas, you can reference the properties of the object like Movie.Runtime
I’ll be able to advise a bit better after seeing your code, but I’m guessing maybe your formula returns a string, with the object in that string? If so, you need to have it return an object instead, and you need to define a schema for that object so it knows what to expect - details here:
One key tip: you can use your schema as a way to pull just a few interesting values out of a complex API response. E.g. if your schema just lists 3 values from your API response, Coda will just silently discard all the other stuff you’re not interested in.
I chose an action instead of a formula because formulas are live, they recalculate. For my case, once I a get the shipping data for a parcel, I’ll never need to hit the API for that.
My code is below:
import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();
// When using the fetcher, this is the domain of the API that your pack makes
// fetcher requests to.
pack.addNetworkDomain("herokuapp.com");
const Frete = coda.makeObjectSchema({
properties: {
Codigo: { type: coda.ValueType.Number },
Valor: { type: coda.ValueType.Number },
ValorMaoPropria: { type: coda.ValueType.Number },
ValorAvisoRecebimento: { type: coda.ValueType.Number },
ValorValorDeclarado: { type: coda.ValueType.Number },
Erro: { type: coda.ValueType.Number },
MsgErro: { type: coda.ValueType.String },
ValorSemAdicionais: { type: coda.ValueType.Number },
},
primary: "Valor", // Which property above to display by default.
});
// This line adds a new formula to this Pack.
pack.addFormula({
name: "ValorFrete",
description: "Retorna o valor do frete pelo Correios.",
isAction: true,
parameters: [
coda.makeParameter({
type: coda.ParameterType.Number,
name: "Comprimento",
description: "Comprimento da embalagem",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "Largura",
description: "Largura da embalagem",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "Altura",
description: "Altura da embalagem",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "Peso",
description: "Peso da embalagem",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "ValorDeclarado",
description: "Valor declarado para seguro",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "CepRemetente",
description: "Cep do Remetente",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "CepDestinatario",
description: "Cep do destinatário",
}),
coda.makeParameter({
type: coda.ParameterType.Number,
name: "Servico",
description: "Código do Serviço",
}),
],
resultType: coda.ValueType.Object,
schema: Frete,
// This function is declared async to that is can wait for the fetcher to
// complete. The context parameter provides access to the fetcher.
execute: async function ([Comprimento,Largura,Altura,Peso,ValorDeclarado,CepRemetente,CepDestinatario,Servico], context) {
let url = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
// The fetcher's fetch method makes the request. The await keyword is used
// to wait for the API's response before continuing on through the code.
let response = await context.fetcher.fetch({
method: "POST",
url: url,
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({
"nCdEmpresa": "",
"sDsSenha": "",
"nCdServico": Servico,
"sCepOrigem": CepRemetente,
"sCepDestino": CepDestinatario,
"nVlPeso": Peso,
"nCdFormato": "1",
"nVlComprimento": Comprimento,
"nVlAltura": Altura,
"nVlLargura": Largura,
"nVlDiametro": "0",
"sCdMaoPropria": "n",
"nVlValorDeclarado": ValorDeclarado,
"sCdAvisoRecebimento": "n"
}),
});
let resposta = response.body;
return {
Codigo: resposta.Codigo,
Valor: resposta.Valor,
ValorMaoPropria: resposta.ValorMaoPropria,
ValorAvisoRecebimento: resposta.ValorAvisoRecebimento,
ValorValorDeclarado: resposta.ValorValorDeclarado,
Erro: resposta.Erro,
MsgErro: resposta.MsgErro,
ValorSemAdicionais: resposta.ValorSemAdicionais,
} ;
},
});
Oh ok I think your code is looking good actually (one shortcut is that, since your schema properties match your API response properties, you can just return resposta and Coda will automatically assign, for example resposta.ValorAvisoRecebimento to ValorAvisoRecebimento in your formula’s output).
I guess I’m not sure what the problem is, since this should return an object that Coda is happy to work with via dot syntax. But maybe I’m misunderstanding the problem
Can you share a screen shot or perhaps sample doc of what the pack’s output looks like in Coda?
(Also, that’s a neat idea for how to use Actions - I hadn’t thought of that! I’m not sure it’s necessary here though? Since you’re connecting it to an automation or button or something that stamps the result into the Shipping Cost column via ModifyRows(), it won’t keep running again unless you run that action again, even if it’s a formula in the Pack)
This is actually a tricky topic, and one we were just discussing internally again yesterday.
In order to “dot” into an object Coda needs to know the schema of the data and needs to associate that schema with the column. In your Pack you are defining the schema correctly, but the “Shipping Cost” column you are adding the object to is a Text column, and not associated with that schema.
If instead of using an action formula in your Pack you use a regular formula, and you insert that formula into the “Shipping Cost” column, Coda will see the schema of that formula and associate it with the column.
The problem in that case though @Eric_Koleda is that it would recalc periodically, and we lose the value of the calculate-once stamping, right? Or am I misunderstanding?
@Breno_Nunes - You can return a Coda list by using the Array result type, as documented here. All items in the array need to be of the same type though, which or may not be an issue for your use case.
@Nick_HE - Ya, I can see how it would be nice to have a record of what the shipping cost was at that time, without further updating. I think the best solution for now would be to not store the object in a column, but instead pull out the fields you want from it and store those in separate columns. It’s not quite as neat, but it allows you to get a permanent record and access each individually. Something like:
