Set up Telegram schedules in Holistics using Gmail and Google Apps Script

Tips and Hacks
,
1

image
image1920×1830 211 KB

Ever wish your Holistics dashboards just popped up in Telegram instead of your inbox? I just found out, with a quick script, you can forward scheduled dashboards straight into a Telegram channel, keeping your team updated instantly, no emails needed.

Benefits of Telegram schedules

  • Real-time alerts: Dashboards (like daily sales dashboards, weekly performance summaries, etc.) are delivered instantly to Telegram, so teams don’t need to check email.
  • Mobile-friendly: Executives get notifications directly on their phones.
  • Collaborative: Teammates can reply, pin, or forward dashboards for discussion.

High-level Approach

This guide walks you through creating a simple automation that forwards Holistics email scheduled dashboards from Gmail directly to Telegram. The setup involves three main steps:

  1. Create a Telegram Bot - Generate a bot token and chat ID
  2. Set up Holistics email schedules - Have dashboards automatically delivered to your inbox.
  3. Automate Gmail to Telegram with Google Apps Script – Use a script to forward scheduled dashboards to your Telegram channel via the bot

Disclaimer: This guide uses Gmail and Google Apps Script. If you use Outlook, Exchange, or other providers, you can achieve similar results with IMAP scripts or automation tools (e.g., Microsoft Power Automate).

Step 1: Create a Telegram Bot to retrieve bot token and chat ID

1.1. Open Telegram and search for @BotFather. Use the command /newbot to create a bot.

image
image1920×1366 198 KB

1.2. Get the bot token shown after creation.

image
image962×592 142 KB

1.3. Find your chat ID:

  • If you want the bot to send messages directly to you

    • Open the bot link (t.me/your_bot_name), send a test message “Hello” to your new bot in Telegram.
    • Visit https://api.telegram.org/bot<YOUR_TOKEN>/getUpdates in a browser.
    • Look for the chat.id field in the JSON response.

  • If you want the bot to send messages to a group

    • Invite your bot to an existing channel (or create a new one) as admin
    • Visit https://api.telegram.org/bot<YOUR_TOKEN>/getUpdates in a browser.
    • Look for the chat.id field in the JSON response. The ID of a group chat should be negative (e.g., -123456789).

Step 2: Create email schedules for your dashboards

Now, create email schedules for your Holistics dashboards. The dashboard will arrive in your inbox automatically. You can find step-by-step instructions in the: Holistics Docs – Schedule Report Delivery.

Tips: You can create a dedicated email account (e.g., [email protected]) that Holistics can forward scheduled emails to. This keeps your Telegram notifications separate and easier to manage.

image
image1920×2068 183 KB

Step 3: Create an automation in Google Apps Script

3.1 Create a new project

Go to Google Apps Script, create a new project and paste in the provided script below. The script will process Gmail messages and forward them to Telegram, including attachments.

/** ====== CONFIG via Script Properties ======
 * TELEGRAM_TOKEN: bot token from @BotFather
 * CHAT_ID: target chat/channel ID (number; negative for groups)
 * SEARCH_QUERY: Gmail search query (e.g., 'label:to-telegram is:unread')
 * INCLUDE_INLINE: 'true' to include inline images (cid), else 'false'
 * SIZE_LIMIT_MB: max size to upload directly (default 49)
 * SEND_IMAGES_AS_PHOTOS: 'true' to send images via sendPhoto; else sendDocument
 * POST_PROCESS: 'mark_read' | 'archive' | (leave blank for no-op)
 * PROCESSED_LABEL: label name to add after successful forward (optional)
 * ===========================================
 */

function processEmails() {
  const props = PropertiesService.getScriptProperties();
  const query = props.getProperty('SEARCH_QUERY') || 'label:to-telegram is:unread';
  const includeInline = (props.getProperty('INCLUDE_INLINE') || 'false') === 'true';
  const sizeLimitBytes = ((Number(props.getProperty('SIZE_LIMIT_MB')) || 49) * 1024 * 1024);
  const processedLabelName = props.getProperty('PROCESSED_LABEL') || '';
  const postProcess = (props.getProperty('POST_PROCESS') || '').toLowerCase();

  const threads = GmailApp.search(query, 0, 20); // adjust page size as you like
  const processedLabel = processedLabelName ? GmailApp.createLabel(processedLabelName) : null;

  threads.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(msg => {
      if (msg.isInTrash()) return;

      // 1) Send email meta + body snippet
      const subject = msg.getSubject() || '(no subject)';
      const from = msg.getFrom() || '';
      const date = msg.getDate();
      const snippet = (msg.getPlainBody() || '').trim();
      const excerpt = snippet.length > 3500 ? (snippet.slice(0, 3500) + '\n…') : snippet;
      const header =
        '📧 ' + subject + '\n' +
        'From: ' + from + '\n' +
        'Date: ' + (date ? date.toString() : '') + '\n\n';

      safeSendTextToTelegram(header + excerpt);

      // 2) Send attachments
      const atts = msg.getAttachments({
        includeInlineImages: includeInline,
        includeAttachments: true
      });

      atts.forEach(att => {
        try {
          const blob = att.copyBlob();
          const name = att.getName() || 'file';
          blob.setName(name);

          // size guard
          const bytes = blob.getBytes(); // Apps Script returns byte[]
          const len = bytes ? bytes.length : 0;
          if (len === 0) return;

          if (len <= sizeLimitBytes) {
            const isImage = (blob.getContentType() || '').startsWith('image/');
            const sendAsPhoto = (props.getProperty('SEND_IMAGES_AS_PHOTOS') || 'true') === 'true';

            if (isImage && sendAsPhoto) {
              safeSendPhotoToTelegram(blob, name);
            } else {
              safeSendDocumentToTelegram(blob, name);
            }
          } else {
            safeSendTextToTelegram('⚠️ Attachment too large to send directly: ' +
                                   `${name} (~${Math.round(len / 1048576)} MB)`);
          }
          Utilities.sleep(400); // pacing to be gentle on rate limits
        } catch (e) {
          safeSendTextToTelegram('⚠️ Error sending attachment: ' + (att.getName() || 'file') +
                                 '\n' + (e && e.message ? e.message : e));
        }
      });

      // 3) Post-process
      if (postProcess === 'mark_read') msg.markRead();
      if (postProcess === 'archive') thread.moveToArchive();
      if (processedLabel) thread.addLabel(processedLabel);
    });
  });
}

/** ============== Telegram helpers ============== */

function safeSendTextToTelegram(text) {
  try {
    sendTextToTelegram(text);
  } catch (e) {
    console.error('sendMessage error:', e);
  }
}

function safeSendDocumentToTelegram(blob, caption) {
  try {
    sendMultipartToTelegram_('sendDocument', 'document', blob, caption);
  } catch (e) {
    console.error('sendDocument error:', e);
    safeSendTextToTelegram('⚠️ Failed to send document: ' + (blob && blob.getName ? blob.getName() : 'file'));
  }
}

function safeSendPhotoToTelegram(blob, caption) {
  try {
    sendMultipartToTelegram_('sendPhoto', 'photo', blob, caption);
  } catch (e) {
    console.error('sendPhoto error:', e);
    safeSendTextToTelegram('⚠️ Failed to send photo: ' + (blob && blob.getName ? blob.getName() : 'image'));
  }
}

function sendTextToTelegram(text) {
  const { token, chatId } = getBotConfig_();
  const url = `https://api.telegram.org/bot${token}/sendMessage`;
  const payload = {
    chat_id: chatId,
    text: text,
    disable_web_page_preview: true
    // You can also add parse_mode: 'HTML' and escape content if you prefer
  };
  const res = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });
  logIfNotOk_(res, 'sendMessage');
}

function sendMultipartToTelegram_(methodName, fileField, blob, caption) {
  const { token, chatId } = getBotConfig_();
  const url = `https://api.telegram.org/bot${token}/${methodName}`;
  const payload = {
    chat_id: chatId,
    caption: caption || ''
  };
  // When payload contains a Blob, Apps Script will build a multipart/form-data request automatically.
  payload[fileField] = blob;

  const res = UrlFetchApp.fetch(url, {
    method: 'post',
    payload: payload,
    muteHttpExceptions: true
  });
  logIfNotOk_(res, methodName);
}

/** ============== utils ============== */

function getBotConfig_() {
  const props = PropertiesService.getScriptProperties();
  const token = props.getProperty('TELEGRAM_TOKEN');
  const chatId = props.getProperty('CHAT_ID');
  if (!token || !chatId) {
    throw new Error('Missing TELEGRAM_TOKEN or CHAT_ID in Script Properties.');
  }
  return { token, chatId };
}

function logIfNotOk_(res, label) {
  const code = res.getResponseCode();
  if (code < 200 || code >= 300) {
    console.error(`[${label}] HTTP ${code}: ${res.getContentText()}`);
  }
}

/** Optional: quick sanity test */
function testSend() {
  safeSendTextToTelegram('Hello from Apps Script 👋');
}

image
image1920×1366 111 KB

3.2 Configure script properties

Use Script Properties for configuration. In Apps Script → click Project Settings → Script properties → Open editor. Add the following key–value pairs:

Property Key Example Value Notes
TELEGRAM_TOKEN 123456789:ABCdefGhIjKlMnOpQrStUvWxYz * Required (From Step 1)
CHAT_ID 1001234567890 * Required (From Step 1)
SEARCH_QUERY from:[email protected] subject:Sales Dashboard Gmail search query targeting Holistics schedules
SIZE_LIMIT_MB 49 Telegram limit ~50MB
INCLUDE_INLINE false Whether to forward inline images.
SEND_IMAGES_AS_PHOTOS true Sends images via sendPhoto
POST_PROCESS mark_read Options: mark_read, archive, or leave blank

image
image2048×1457 199 KB

3.3 Add Triggers

In Apps Script, go to Triggers (clock icon). Add trigger:

  • Function: processEmails
  • Event source: Time-driven
  • Select every hour (or desired frequency).

Now, the script will check Gmail periodically and forward any matching emails to Telegram.

image
image2048×1457 134 KB

4. Result

From now on, the script runs on schedule. Gmail will be checked automatically every few minutes (or at the interval you set, such as hourly), and whenever Holistics delivers a dashboard to your inbox, it will be forwarded straight to your Telegram channel.

image
image1920×1830 211 KB

Best practices

Duplicates: To avoid re-sending the same Holistics schedule multiple times, configure Gmail queries carefully (e.g., is:unread) and use post-processing options such as marking messages as read or applying a processed label. Without these safeguards, the script may forward duplicate emails.

4 Likes