// SET THIS PART FIRST
const sheetName = 'Sheet1' // change if needed
const siteName = 'Название сайта'
var botToken = '<Ваш Token>'
var recipientID = '<Айди группы с ботом> // 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(`A${lastrow}:J${lastrow}`).getDisplayValues()[0]
var timeStamp = lastFormSubmitted[0]
var senderName = lastFormSubmitted[1]
var senderBirth = lastFormSubmitted[2]
var senderSphere = lastFormSubmitted[3]
var senderTg = lastFormSubmitted[8]
var message = `<strong>Получен новый ответ из ${siteName}</strong>\n\n` +
`<strong>Время отправки:</strong> ${timeStamp}\n` +
`<strong>Имя:</strong> ${senderName}\n` +
`<strong>Телеграм:</strong> ${senderTg}\n` +
`<strong>Ваш год рождения:</strong> ${senderBirth}\n` +
`Полные данные ответа доступны в <a href="${sourceSpreadsheetURL.getUrl()}">таблице ответов</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)
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1
const newRow = headers.map(function(header) {
return header === 'timestamp' ? 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()
}
}
