Search code examples
google-apps-scriptgoogle-sheetschartsgoogle-visualizationtelegram

When sending image of a spreadsheet graph to Telegram, the image sent is always the same outdated

The image of the graph I'm trying to send to Telegram is this:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRYIAQWrNu1P9fc0CzBXkMb2jsOCKhrRMoakqeq73BVWHNa6ukJHHK00ZvfNi5QQB2Pr7ACdw3yCBwV/pubchart?oid=1479223143&format=image

The formula I use for shipping is this:

function EnviarTelegram(botSecret, chatId, photoUrl, caption) {
    var response = UrlFetchApp.fetch("https://api.telegram.org/bot" + botSecret + "/sendPhoto?caption=" + encodeURIComponent(caption) + "&photo=" + encodeURIComponent(photoUrl) + "&chat_id=" + chatId + "&parse_mode=HTML");
}

But when I add the graphic link, it does not send the updated version, it always sends the first version of the image when I first published the image.

The formula I use looks like this:

=ENVIARTELEGRAM(W1,W2,"https://docs.google.com/spreadsheets/d/e/2PACX-1vRYIAQWrNu1P9fc0CzBXkMb2jsOCKhrRMoakqeq73BVWHNa6ukJHHK00ZvfNi5QQB2Pr7ACdw3yCBwV/pubchart?oid=1479223143&format=image",W19)

The correct image:

enter image description here

The old image that is always sent to Telegram:

enter image description here

Is there any way to force the update of this image that is sent to Telegram?

Solution

  • Workaround:

    • From the discussions in the comment, I would like to propose the following workaround.
      • Fortunately, when the image is retrieved using UrlFetchApp, I can see the upper image in your question. I thought that this might be able to be used. If in the current stage, the API you want to use cannot retrieve the latest image with the same URL, as a workaround, I would like to create the latest image as a file on Google Drive and send the URL of the image.
    • From your comments, also I confirmed that you had used https://drive.google.com/file/d/1zdEBhYircl9-P9Wtx-QyTgmxgskGLcEq/ as the value of photoUrl. In this case, the image file cannot be correctly shown. I think that the endpoint is required to be modified.

    When this workaround including the modification point is a script, it becomes as follows.

    Sample script 1:

    In this sample script, the chart image is retrieved from https://docs.google.com/spreadsheets/d/e/2PACX-###/pubchart?oid=###&format=image and the image is created as a file, and then, the thumbnail link is retrieved by modifying the image size.

    Before you use this script, please enable Drive API at Advanced Google services.

    const url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vRYIAQWrNu1P9fc0CzBXkMb2jsOCKhrRMoakqeq73BVWHNa6ukJHHK00ZvfNi5QQB2Pr7ACdw3yCBwV/pubchart?oid=1479223143&format=image";
const res = UrlFetchApp.fetch(url);
const file = DriveApp.createFile(res.getBlob());
const outputUrl = Drive.Files.get(file.getId()).thumbnailLink.replace("=s220", "=s1000");
console.log(outputUrl) // You can see the link at the log.

    Sample script 2:

    In this sample script, the chart image is retrieved from https://docs.google.com/spreadsheets/d/e/2PACX-###/pubchart?oid=###&format=image and the image is created as a file, and then, the file is publicly shared and the webContentLink is retrieved.

    const url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vRYIAQWrNu1P9fc0CzBXkMb2jsOCKhrRMoakqeq73BVWHNa6ukJHHK00ZvfNi5QQB2Pr7ACdw3yCBwV/pubchart?oid=1479223143&format=image";
const res = UrlFetchApp.fetch(url);
const file = DriveApp.createFile(res.getBlob());
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
const outputUrl = "https://drive.google.com/uc?export=download&id=" + file.getId();
console.log(outputUrl) // You can see the link at the log.
    • In this case, the file is required to be publicly shared. Please be careful this.

    References: