Neet help in a Telegram pack code

[Omar_Mhammedi]

Hi Coda community,

I’m trying to create my first Coda pack for Telegram with those limitations :

• 0 knowledge in JavaScript → I’m using samples from the documentation as starting point.
• ChatGPT help me to write correctly some sentences → but he does not know coda SDK specific syntaxes.

After many hours, I managed to get this Sync Table function working, but I failed to add one last API get call with method getFile from telegram, that use the file_id of the medias generated by the getUpdate API call, to return a file path of the file.

When i put the getfile API get call in the returned results of the getupdate i get an error saying : “#Promise could not be cloned.”

What i’m trying to do is to include files in message inside coda.
i would appreciate if someone can help

import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();

pack.setSystemAuthentication({
  type: coda.AuthenticationType.Custom,
  params: [
    { name: "bot_api_key", description: "The API key" },
  ],
});

pack.addNetworkDomain("api.telegram.org");

const TaskSchema = coda.makeObjectSchema({
  properties: {
    message: {
      description: "Message.",
      type: coda.ValueType.String,
    },
    from: {
      description: "Message sender.",
      type: coda.ValueType.String,
    },
    date: {
      description: "Date.",
      type: coda.ValueType.Number,
      codaType: coda.ValueHintType.Date,
    },
    caption: {
      description: "caption of the media.",
      type: coda.ValueType.String,
    },
    message_id: {
      description: "The ID of the message.",
      type: coda.ValueType.Number,
    },
    media: {
      description: "The ID of the media.",
      type: coda.ValueType.String,
    },
  },
  displayProperty: "message",
  idProperty: "message_id",
  featuredProperties: ["Message","caption","from","media", "date"],
});


function formatItems(update, username) {
  let message;
  let media;

  if (username) {
    if (!update.message.entities) {
      return;
    }
    let entities = update.message.entities;
    let isMention = entities.some(function (entity) {
      return entity.type === "mention" && update.message.text.substring(entity.offset, entity.offset + entity.length) === username;
    });
    if (!isMention) {
      return;
    }
  }

  if (update.message.photo) {
    message = "Picture";
    media = update.message.photo[0].file_id;
  } else if (update.message.video) {
    message = update.message.video.file_name;
    media = update.message.video.file_id;
  } else if (update.message.document) {
    message = update.message.document.file_name;
    media = update.message.document.file_id;
  } else if (update.message.text) {
    message = update.message.text;
  }

  return {
    message: message,
    from: update.message.from.first_name,
    date: update.message.date,
    caption: update.message.caption,
    message_id: update.message.message_id,
    media: media
  };
}

pack.addSyncTable({
  name: "Table2",
  schema: TaskSchema,
  identityName: "MessageTable2",
  formula: {
    name: "SyncMessages",
    description: "Sync from telegram",
    parameters: [
      coda.makeParameter({
        type: coda.ParameterType.Number,
        name: "chatID",
        description: "The ID of the chat to retrieve messages from",
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "username",
        description: "The username to filter messages by",
        optional: true,
      }),
    ],
    execute: async function ([chatId = null, username = ""], context) {
      let invocationToken = context.invocationToken;
      let keyPlaceholder = "{{bot_api_key-" + invocationToken + "}}";
      let url = "https://api.telegram.org/bot" + keyPlaceholder + "/getUpdates";
      let response = await context.fetcher.fetch({
        method: "GET",
        url: url,
      });
      let results = response.body.result;
      let items = [];
      for (let result of results) {
        let formattedItem = formatItems(result, username);
        if (formattedItem) {
          items.push(formattedItem);
        }
      }
      return {
        result: items,
      };
    },
  },
});

[Bill_French]

@Omar_Mhammedi

When i put the getfile API get call in the returned results of the getupdate i get an error saying : “#Promise could not be cloned.”

I think this is indicative of a missing await. I’m unsure where exactly. Here perhaps?

I believe the issue lay inside this loop - formatItems is creating a promise for every iteration and that part is non-blocking, which would seem to result in a second promise spawned before the first has resolved.

[Eric_Koleda]

Hi @Omar_Mhammedi - Bravo on attempting your first Pack! Creative to use ChatGPT to help, and hopefully us humans can help get you over the line.

You mentioned that it was the addition of the getFile API that broke things, but I don’t see any getFile call in your code. Are you sure you pasted the right version?

[Omar_Mhammedi]

@Bill_French

I think this is indicative of a missing await. I’m unsure where exactly. Here perhaps?

Thanks a lot, that was the problem !
the getFile execution “almost” work fine

It returns the end path of the file like this : “photos/file_14.jpg”

i tried to add the full path of the URL the same way i did it for the Getupdate and Getfile with a placeholder, but i get links formatted like that :
“https://api.telegram.org/file/bot{{bot_api_key-0nTmjxcu9DEGdmaADfApD0C1}}/photos/file_14.jpg”

The correct link should be like this : "https://api.telegram.org/file/bot “My API KEY " /photos/file_14.jpg”

@Eric_Koleda

You mentioned that it was the addition of the getFile API that broke things, but I don’t see any getFile call in your code. Are you sure you pasted the right version?

Sorry i was ashamed to put a code that was completely wrong here is the new version of the code :

import * as coda from "@codahq/packs-sdk";
export const pack = coda.newPack();

pack.setSystemAuthentication({
  type: coda.AuthenticationType.Custom,
  params: [
    { name: "bot_api_key", description: "The API key" },
  ],
});

pack.addNetworkDomain("api.telegram.org");

const TaskSchema = coda.makeObjectSchema({
  properties: {
    message: {
      description: "Message.",
      type: coda.ValueType.String,
    },
    from: {
      description: "Message sender.",
      type: coda.ValueType.String,
    },
    date: {
      description: "Date in Unix format.",
      type: coda.ValueType.Number,
      codaType: coda.ValueHintType.Date,
    },
    caption: {
      description: "caption of the media.",
      type: coda.ValueType.String,
    },
    message_id: {
      description: "The ID of the message.",
      type: coda.ValueType.Number,
    },
    media: {
      description: "The ID of the task.",
      type: coda.ValueType.String,
    },
  },
  displayProperty: "message",
  idProperty: "message_id",
  featuredProperties: ["Message","caption","from","media", "date"],
});


async function getFilePath(fileId, context) {
  let invocationToken = context.invocationToken;
  let keyPlaceholder = "{{bot_api_key-" + invocationToken + "}}";
  let url = "https://api.telegram.org/bot" + keyPlaceholder + "/getFile?file_id=" + fileId;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });
  let path = response.body.result.file_path;
  return  "https://api.telegram.org/file/bot" + keyPlaceholder + "/" + path;
}

async function formatItems(update, username, context) {
  let message;
  let media;

  if (username) {
    if (!update.message.entities) {
      return;
    }
    let entities = update.message.entities;
    let isMention = entities.some(function (entity) {
      return entity.type === "mention" && update.message.text.substring(entity.offset, entity.offset + entity.length) === username;
    });
    if (!isMention) {
      return;
    }
  }

  if (update.message.photo) {
    message = "Picture";
    media = await getFilePath(update.message.photo[0].file_id, context);
  } else if (update.message.video) {
    message = update.message.video.file_name;
    media = await getFilePath(update.message.video.file_id, context);
  } else if (update.message.document) {
    message = update.message.document.file_name;
    media = await getFilePath(update.message.document.file_id, context);
  } else if (update.message.text) {
    message = update.message.text;
  }

  return {
    message: message,
    from: update.message.from.first_name,
    date: update.message.date,
    caption: update.message.caption,
    message_id: update.message.message_id,
    media: media
  };
}

pack.addSyncTable({
  name: "Table2",
  schema: TaskSchema,
  identityName: "MessageTable2",
  formula: {
    name: "SyncMessages",
    description: "Sync from telegram",
    parameters: [
      coda.makeParameter({
        type: coda.ParameterType.Number,
        name: "chatID",
        description: "The ID of the chat to retrieve messages from",
        optional: true,
      }),
      coda.makeParameter({
        type: coda.ParameterType.String,
        name: "username",
        description: "The username to filter messages by",
        optional: true,
      }),
    ],
    execute: async function ([chatId = null, username = ""], context) {
      let invocationToken = context.invocationToken;
      let keyPlaceholder = "{{bot_api_key-" + invocationToken + "}}";
      let url = "https://api.telegram.org/bot" + keyPlaceholder + "/getUpdates";
      let response = await context.fetcher.fetch({
        method: "GET",
        url: url,
      });
      let results = response.body.result;
      let items = [];
      for (let result of results) {
        let formattedItem = await formatItems(result, username, context);
        if (formattedItem) {
          items.push(formattedItem);
        }
      }
      return {
        result: items,
      };
    },
  },
});

Any idea why the links aren’t formated correctly ?
do you think there is a way to get the files show up diretly as photos or attachement in coda ?

And why do i always get this column as the display column on my table even if i set it to be “message” :

thanks a lot for you help !!

[Bill_French]

@Omar_Mhammedi

And why do i always get this column as the display column on my table even if i set it to be “message”

This is part of the sync table architecture; it returns a single object containing all elements of the row. You are free to hide this column, but it is a required column.

@Omar_Mhammedi

i get links formatted like that :

I think the double curly’s are the problem. Remove one layer of curly braces.

[Eric_Koleda]

I’m glad you got it working, and thanks for all the help @Bill_French!

@Omar_Mhammedi

i get links formatted like that :
“https://api.telegram.org/file/bot{{bot_api_key-0nTmjxcu9DEGdmaADfApD0C1}}/photos/file_14.jpg”

The correct link should be like this : "https://api.telegram.org/file/bot “My API KEY " /photos/file_14.jpg”

The placeholders used by Custom authentication are only replaced during fetcher calls. When you return a URL to your sync table it doesn’t go through the fetcher and so the placeholder isn’t replaced.

For cases where you want to include a private image URL in your sync table you can use temporary blob storage and the ImageAttachment. You can read more about this approach here:

Working with images and files - Coda Pack SDK

@Omar_Mhammedi

do you think there is a way to get the files show up diretly as photos or attachement in coda ?

Yes, there are hints you can apply to your schema to have a URL treated as an image or a file attachment. They are two different hints however, and each schema property can only apply one. You can read more here:

Return data with meaningful types - Coda Pack SDK

[Omar_Mhammedi]

@Eric_Koleda

The placeholders used by Custom authentication are only replaced during fetcher calls. When you return a URL to your sync table it doesn’t go through the fetcher and so the placeholder isn’t replaced.

For cases where you want to include a private image URL in your sync table you can use temporary blob storage and the ImageAttachment. You can read more about this approach here:

Thank you for the clarification.
i managed to get it to work like this :

async function getFilePath(file_id, context) {
  let invocationToken = context.invocationToken;
  let keyPlaceholder = "{{bot_api_key-" + invocationToken + "}}";
  let url = "https://api.telegram.org/bot" + keyPlaceholder + "/getFile?file_id=" + file_id;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });

  let filePath = response.body.result.file_path;
  let privateImageUrl = "https://api.telegram.org/file/bot" + keyPlaceholder + "/" + filePath ;
  let temporaryImageUrl = await context.temporaryBlobStorage.storeUrl(privateImageUrl);
  return temporaryImageUrl;

with this setup for the media

    media: {
      description: "The ID of the task.",
      type: coda.ValueType.String,
      codaType: coda.ValueHintType.ImageReference,
    },

it show the pictures preview in Coda,
But the other type of files like PDF don’t show up correctly

When i switch the media type to:

    media: {
      description: "The ID of the task.",
      type: coda.ValueType.String,
      codaType: coda.ValueHintType.Attachment,
    },

all attachement are named like this : “image.jpeg” even if they are pdfs

when i download them, they open up fine in the browser, but not in the finder, because they don’t have a file extention at the end of the name.

i tried also to add the file extention with javascript based of the original filepath, but then they won’t open up at all.

I looked into the SDK documentation, but i didn’t find a way to show correctly all type of file with the correct naming

did i miss something ?

thanks a lot for your time

[Eric_Koleda]

Hi @Omar_Mhammedi - It sounds like you are on the right track! First, when storing images make sure to use ImageAttachment not ImageReference. A reference always pulls in the original image, which in this case is a temporary URL. An attachment will ingest the image into Coda (as if you uploaded it to the doc) and not rely on the temporary URL after that.

The bad file name for attachments is unfortunately a known issue:

You can set a downloadFilename advanced option when you store the file, but I’m not sure it will fix the filename as shown in the cell:

let temporaryImageUrl =
  await context.temporaryBlobStorage.storeUrl(privateImageUrl, {
    downloadFilename: "foo.txt",
  });

[Omar_Mhammedi]

Thanks, i missed this note on the bug.
the only problem i get with ImageAttachment is that it don’t show a preview of the image inside of coda.

it will only show a preview for a few second while it scan the media.

[Eric_Koleda]

@Omar_Mhammedi

it will only show a preview for a few second while it scan the media.

That’s not expected. When I run the Google Drive sample Pack which uses ImageAttachment it shows the preview in the cell:

Is that the case even when you refresh the page?

[Omar_Mhammedi]

this is what happen in my coda page :

My code like that :

    download: {
      description: "The ID of the task.",
      type: coda.ValueType.String,
      codaType: coda.ValueHintType.ImageAttachment,
    },

  },
  displayProperty: "message",
  idProperty: "message_id",
  featuredProperties: ["Message", "from", "date", "preview", "download",],
});

async function getFilePath(file_id, context, update) {
  let allowedSize = 4194304;
  let invocationToken = context.invocationToken;
  let keyPlaceholder = "{{bot_api_key-" + invocationToken + "}}";
  let url = "https://api.telegram.org/bot" + keyPlaceholder + "/getFile?file_id=" + file_id;
  let response = await context.fetcher.fetch({
    method: "GET",
    url: url,
  });
  let size = response.body.result.file_size;
  if (size > allowedSize) return;

  let filePath = response.body.result.file_path;
  let fileName;
  if (update.message.document) fileName = update.message.document.file_name;
  else if (update.message.video) fileName = update.message.video.file_name;
  else fileName = 'file.jpg'


  let privateImageUrl = "https://api.telegram.org/file/bot" + keyPlaceholder + "/" + filePath;
  let temporaryImageUrl = await context.temporaryBlobStorage.storeUrl(privateImageUrl, {
    downloadFilename: fileName,
  });
  return temporaryImageUrl;
}

[Eric_Koleda]

What is the type of that column in the Coda doc? It looks like it’s set to File, instead of Image. The codaType you set influences what column type the column has, but only when the column is created. Changing the codaType later doesn’t change existing columns. It looks like you may have created the column using Attachment and then switched it to ImageAttachment but didn’t re-create the column / table.

[Omar_Mhammedi]

Hi Eric,

i didn’t change the type of the column,
Here is my telegram page test with the code used.
Re-sync the table to see the last pictures preview loaded, before they disappear

The files column become greyed when after a few seconds from loading the results.
I have put the coda pack code used and the raw output of both getUpdate and Getfile

[Eric_Koleda]

Very strange! I tried refreshing the table and it seems to have wiped out the results, not sure why. In general there isn’t one column type that is going to be perfect for images and files. Attachment should be displayed as a paperclip icon and a filename, and ImageAttachment should be displayed as an image. You may want to have two columns, and put the file in one or the other depending on the file extension.

[Omar_Mhammedi]

Hi Eric, sorry about that, the telegram messages are stored in the api for only 24h.
i have uploaded news pictures, i will to keep posting before the 24h limit.

you should see new pictures if you refresh now

[Eric_Koleda]

Thanks! I’ve been trying to reproduce the problem, but I can’t quite get the same result. Putting an MP4 into the ImageAttachment column results in the same weird paperclip image, but I can’t get that result from an actual image.

Can you look at the HTTP logs for the requests that fetch the images and see what the Content-Type header is set to in the response? I wonder if that is influencing how we ingest the image.

[Omar_Mhammedi]

Hi @Eric_Koleda ,
Sorry for my late answer,

he is my HTTP Log :

it seems to be set on “application/octet-stream”

[Eric_Koleda]

Thanks for the additional information @Omar_Mhammedi. I’ve to reproduce the bug a ton of different ways, but it’s proving challenging. One thing I noticed is that the same sync table with the same data sometimes displays the images differently in an old doc vs a new one. Can you trying loading your sync table into a fresh doc and see if the images still load incorrectly?

[Eric_Koleda]

After some more debugging I think I’ve located the source of the problem. The cause of the issue appears to be that the image is being returned with the content type header set to “application/octet-stream”. If the image was returned with the correct content type (like “image/png”, etc) then it would work even after the virus scan.

I think you can work around this by manually downloading the image first, and then storing it with the correct content type. Something like:

let response = await context.fetcher.fetch({
  method: "GET",
  url: imageUrl,
  isBinaryResponse: true,
});
let buffer = response.body;
let tempUrl = await context.temporaryBlobStorage.storeBlob(buffer, "image/png", {
  downloadFilename: filename,
});

You may have to guess the content type based on the file extension, if the API doesn’t provide that information anywhere else.

[Omar_Mhammedi]

Hi Eric,

Thanks for the feedback

I will try your other suggestion