Send Form Data to Google Sheet & Get Notification in Telegram

[raniaamina]

I shared component in BSS called “zectStudio_Web Form to Google Sheet (Dark)”, you can find it in your BSS component panel. This post is about how to use my component. I hope it can help someone or be useful for anyone who need it.

Step by Step How to Use

• Make a copy of this template of google sheet that i created. Open this link. At the first, i suggest you to not change anything, including possition or table name.

• Open Google AppScript inside your copied sheet. Go to Extension > AppScript. Then you will see this page
  

  

• Delete all text in thos page and insert this code to the AppScript

// Original code from https://github.com/jamiewilson/form-to-google-sheets
// Updated for 2021 and ES6 standards
// Modified by Rania Amina (Add Telegram Integration)


// SET THIS PART FIRST
const sheetName = 'Sheet1' // change if needed
const siteName = 'YOURSITENAME'
var botToken = 'YOURBOT_TOKEN'
var recipientID = 'GROUP_OR_YOUR_PERSONAL_ID' // Group or User ID that will receive this notification i.e 123456789 or -1001123456789
// END OF SET THIS PART FIRST

var botURL = 'https://api.telegram.org/bot'+botToken
const scriptProp = PropertiesService.getScriptProperties()
var sourceSpreadsheetURL = SpreadsheetApp.getActiveSpreadsheet()
var sheetTarget = sourceSpreadsheetURL.getSheetByName(sheetName)

function initialSetup () {
  const activeSpreadsheet = sourceSpreadsheetURL
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function sendToTelegram(message) {
    var response = UrlFetchApp.fetch(botURL + "/sendMessage?chat_id=" + recipientID + "&parse_mode=HTML&text=" + encodeURIComponent(message))
    Logger.log(response.getContentText())
  }

  // check bot
  function getMe() {
    var response = UrlFetchApp.fetch(botURL + "/getMe")
    Logger.log(response.getContentText())
  }

  // send hello world test 
  function sendTest() {
    sendToTelegram("Hello world test")
  }

  function sendMessageToTelegram(){
    let lastrow = sheetTarget.getLastRow()
    var lastFormSubmitted = sheetTarget.getRange(`B${lastrow}:F${lastrow}`).getDisplayValues()[0]
    var timeStamp = lastFormSubmitted[0]
    var senderName = lastFormSubmitted[1]
    var senderEmail = lastFormSubmitted[2]
    var senderWhatsApp = lastFormSubmitted[3]
    var senderMessage = lastFormSubmitted[4]

    var message = `<strong>New Message From ${siteName} site form!</strong>\n` +
        `From: ${senderName} (${senderEmail})\n` +
        `WhatsApp: ${senderWhatsApp}\n\n` +
        `Message:\n` +
        `${senderMessage}\n\n` +

        `Received at ${timeStamp} \nvia <a href="${sourceSpreadsheetURL.getUrl()}">Webform to Sheet</a>`

    // Logger.log(message)
    sendToTelegram(message)
  }

function doPost (e) {
  // Logger.log(JSON.stringify(e))


  const lock = LockService.getScriptLock()
  lock.tryLock(10000)
  
  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    const sheet = doc.getSheetByName(sheetName)
  
    // Row, Column
    const headers = sheet.getRange(4, 1, 3, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

  
    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : e.parameter[header]
    })
  
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }
  
  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }
  
  finally {
    lock.releaseLock()
    sendMessageToTelegram()
  }
  
}

• Modify lines after SET THIS PART FIRST, this script allow you to get notification in your Telegram if someone submit new data. To use this feature, you need create the Telegram bot First and get the Bot Token via @BotFather. Also, you need get recipient ID (it can be Group, private channel, or your personal account) that receive chat for notification. You can use this RawDataBot.

• Save the script and then get the script permission by running the initialSetup.
  

• Set the trigger of script. In side menu, click clock icon to open the trigger page setup. Click Add Triger and set as seen bellow.
  

  

• Deploy script by clicking apply button in up right of page, then choose new deployment. Set the description, run as Me, and set everyone that has access, then Apply.

• You’ll get deplyment URL, copy it
  

  

• Now, go to your Bootstrap Studio (I assume you’ve insert the component) then select Form insde the component. Paste the deployment URL in action field.
  

  

• Now you can test the form :")

Please ping me if this tuturial doesn’t works :")