Log received SMS to Google Sheets automatically

import { createHmac, timingSafeEqual } from 'node:crypto'
import { createServer } from 'node:http'

const SECRET = process.env.TEXTBEE_WEBHOOK_SECRET
if (!SECRET) throw new Error('Set TEXTBEE_WEBHOOK_SECRET')
const APPS_SCRIPT_URL = process.env.APPS_SCRIPT_URL
const RELAY_TOKEN = process.env.RELAY_TOKEN
const MAX_BODY = 64 * 1024
const seen = new Set() // use your database in production

async function forward(event) {
  // Apps Script answers the POST with a redirect; fetch follows it to the script output.
  const response = await fetch(APPS_SCRIPT_URL, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    signal: AbortSignal.timeout(20_000),
    body: JSON.stringify({
      token: RELAY_TOKEN,
      receivedAt: event.receivedAt,
      sender: event.sender,
      message: event.message,
    }),
  })
  const output = await response.text()
  if (!response.ok) throw failure('Apps Script', response.status)
  if (output !== 'ok') throw new Error(`Apps Script answered ${output}`)
}

// A 4xx other than 408 or 429 will not fix itself, so it is logged instead of retried.
function failure(name, status) {
  const error = new Error(`${name} answered ${status}`)
  error.permanent = status >= 400 && status < 500 && status !== 408 && status !== 429
  return error
}

function verified(rawBody, signature = '') {
  const expected = Buffer.from(createHmac('sha256', SECRET).update(rawBody).digest('hex'))
  const received = Buffer.from(signature)
  return received.length === expected.length && timingSafeEqual(received, expected)
}

createServer((request, response) => {
  const chunks = []
  let size = 0
  request.on('data', (chunk) => {
    size += chunk.length
    if (size <= MAX_BODY) chunks.push(chunk)
  })
  request.on('end', async () => {
    if (size > MAX_BODY) return response.writeHead(413).end()
    const rawBody = Buffer.concat(chunks)
    if (!verified(rawBody, request.headers['x-signature'])) return response.writeHead(401).end()

    const event = JSON.parse(rawBody.toString())
    if (event.webhookEvent !== 'MESSAGE_RECEIVED' || seen.has(event.idempotencyKey)) {
      return response.writeHead(200).end()
    }

    try {
      await forward(event)
      seen.add(event.idempotencyKey)
      response.writeHead(200).end()
    } catch (error) {
      console.error(error.message)
      // 502 makes textbee retry the delivery; a permanent failure is not worth retrying.
      response.writeHead(error.permanent ? 200 : 502).end()
    }
  })
}).listen(process.env.PORT ?? 3000)

const TOKEN = PropertiesService.getScriptProperties().getProperty('RELAY_TOKEN')

// A leading apostrophe keeps a value as plain text: no formulas, no lost leading zeros.
function asText(value) {
  return "'" + value
}

function doPost(e) {
  const data = JSON.parse(e.postData.contents)
  if (!TOKEN || data.token !== TOKEN) return ContentService.createTextOutput('forbidden')

  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('SMS')
    .appendRow([new Date(data.receivedAt), asText(data.sender), asText(data.message)])
  return ContentService.createTextOutput('ok')
}