How to get Telegram notifications for Google Form submissions

For a recent project I was looking into possible ways of getting notifications for Google Form submissions through a messenger app, where the notification includes the responses of a submission. One way to do this for absolutely free and not requiring self-hosting is to use Google Apps Script (Google’s managed Javascript scripting environment for Google Workspace apps) and the Telegram Bot API to send notifications in a Telegram group. In the following I assume familiarity with creation of Telegram bots (see the official docs otherwise) and basic programming skills.

So, the first thing we need to make sure is that the Google form has an associated spreadsheet. Within the form’s “Responses” tab on the top right one can link an existing spreadsheet or create a new one based on the form’s fields. The sheet just acts as the data source for all the responses and additionally to record a single piece of data: the latest response that we already successfully sent a Telegram notification for. In order to record the latter, we create a second sheet inside the linked spreadsheet and write “1” to any cell (rows in Google sheets start at index 1 and the first row of the data sheet contains the form’s field names).

Next, we need to create the Telegram bot used for sending notifications and for ease of use we create a Telegram group and add the bot and any users that should receive notifications on form submissions.

We will need a couple of pieces of information for the script:

• Form ID (obtained from form’s URL)
• Sheet ID (obtained from sheet’s URL)
• Telegram Bot API token (obtained on bot creation)
• Telegram Group Chat ID (obtained form group chat’s URL in Telegram web)
• List of form fields

With all the prerequisites out of the way, let’s start looking at the Google Apps Script environment and the code that automates the Telegram notifications. We will be creating a standalone script (as opposed to a script linked to a Google workspace resource like a sheet) because only standalone scripts are allowed to make network calls (which we need to call the Telegram Bot API). Go to the Apps Script site and create a new “project” which will contain one blank “Code.gs” script file.

First we need to define a bunch of constants for the information mentioned above:

const fields = [
  "field1",
];

const formId = "1caPXETvdrYPr19oV_uvrTm-3h0yeJDNhqfDYN7BkPx0";
const spreadsheetId = "1hpmP1Z3OsS6-TMydDTiARQIxHefaPkNphUGloAFrROw";
const API_TOKEN = "YOUR_TOKEN";
const bookingsChatId = "-4287861338"

Then the core logic is contained in a function to send outstanding notifications:

function sendOutstandingNotifications() {
  const sheet = SpreadsheetApp.openById(spreadsheetId);
  const dataSheet = sheet.getSheets()[0]
  const persistenceSheet = sheet.getSheets()[1]

  // use the right index for the last notified cell here
  const lastNotifiedCell = persistenceSheet.getRange(1, 2);
  let lastNotified = Number(lastNotifiedCell.getValue());
  const numberRows = dataSheet.getLastRow();
  const numberRowsNotNotified = numberRows - lastNotified;
  if (numberRowsNotNotified == 0) {
    return;
  }

  // select all rows for booking requests that have not been notified on
  // start from column 2, as we don't need the timestamp (column 1)
  const rowsNotNotified = dataSheet.getRange(lastNotified + 1, 2, numberRowsNotNotified, fields.length).getValues();
  for (let i = 0; i < rowsNotNotified.length; i++) {
    const row = rowsNotNotified[i];
    const msg = formatRow(row);
    try {
      sendNotification(msg);
      lastNotified += 1;
      lastNotifiedCell.setValue(lastNotified);
    } catch (error) {
      console.error('Error on sending notification:', error);
      return;
    }
  }
}

The logic is straightforward we read all the responses that were not notified on yet and send notifications for them one by one. The basic flow per response is:

1. create the content of the notification message (formatRow)
2. send message by calling Telegram Bot API (sendNotification)
3. record successful notification by updating the “lastNotified” cell

In case the sending of the notification fails we exit early and next time sendOutstandingNotifications is called we try again starting from that response.

We’ll skip looking at formatRow as that’s uninteresting and depends on what you want to send exactly, but let’s have a look at sendNotification which is also straightforward:

function sendNotification(msg) {
  const payload = {
    method: "sendMessage",
    chat_id: bookingsChatId,
    text: msg,
  };

  const request = {
    method: "post",
    payload: payload,
  };

  UrlFetchApp.fetch("https://api.telegram.org/bot" + API_TOKEN + "/", request);
}

We issue a “sendMessage” API call that targets the group chat we use for receiving the notification messages. This function may fail due to various reasons such as the Telegram Bot API being down, so this is why we need the error handling and persistence of which response was last notified on.

We can now manually execute the sendOutstandingNotfications function from within the Google Apps Script environment and as a result we should receive messages for all form responses submitted so far. What is missing is the automatic execution of this function. To receive timely notifications we can register a “trigger” that will cause the execution of the sendOutstandingNotifications function every time a form submission occurs. And in order to handle the scenario of a failed notification transmission, we must register a second “timed trigger” that executes the function every few minutes.

Add the below code to the script and execute the registerTriggers function.

function registerTriggers() {
  registerEventTrigger();
  registerTimedTrigger();
}

function registerTimedTrigger() {
  ScriptApp.newTrigger('sendOutstandingNotifications')
    .timeBased()
    .everyMinutes(10)
    .create();
}

function registerEventTrigger() {
  ScriptApp.newTrigger('sendOutstandingNotifications')
    .forForm(formId)
    .onFormSubmit()
    .create();
}

After doing so, the Google Apps Script project “Triggers” tab (on the left) will show the two triggers. This is all that’s needed to implement free Telegram notifications for Google Form submissions :)