Telegram to Google Sheet: Subscribe to my channel: https://bit.ly/van-huy-dev-youtube

SoChiTieu.gs

// original by NoCtrlZ
// https://gist.github.com/NoCtrlZ1110/6bc7dd58d9512a4710c151c6e20478ca
// customized by minoxd114
// Constants

const TOKEN = `<YourTokenHere>`;
const BASE_URL = `https://api.telegram.org/bot${TOKEN}`;
const CHAT_ID = '<ChatId>';
const DEPLOYED_URL = '<YourScriptDeployedURL>';
const SUM_CELL = 'G2';
const METHODS = {
  SEND_MESSAGE: 'sendMessage',
  SET_WEBHOOK: 'setWebhook',
  GET_UPDATES: 'getUpdates',
}

// Utils

const toQueryParamsString = (obj) => {
  return Object.keys(obj)
    .map(key => `${encodeURIComponent(key)}=${encodeURIComponent(obj[key])}`)
    .join('&');
}

// Telegram APIs

const makeRequest = async (method, queryParams = {}) => {
  const url = `${BASE_URL}/${method}?${toQueryParamsString(queryParams)}`
  const response = await UrlFetchApp.fetch(url);
  return response.getContentText();
}

const sendMessage = (text) => {
  makeRequest(METHODS.SEND_MESSAGE, {
    chat_id: CHAT_ID,
    text
  })
}

const setWebhook = () => {
  makeRequest(METHODS.SET_WEBHOOK,{
    url: DEPLOYED_URL
  })
}

const getChatId = async () => {
  const res = await makeRequest(METHODS.GET_UPDATES);
  console.log("ChatId: ", JSON.parse(res)?.result[0]?.message?.chat?.id)
}

// Google Sheet

const addNewRow = (content = []) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const Avals = sheet.getRange("A1:A").getValues();
  const Alast = Avals.filter(String).length;
  const columnNumber = content.length;
  const newRow = sheet.getRange(Alast + 1, 1, 1, columnNumber);
  newRow.setValues([content]);
}

// Extract label & price

/*
const getMultiplyBase = (unitLabel) => {
  switch (unitLabel) {
    case 'k':
    case 'K':
    case 'nghìn':
    case 'ng':
    case 'ngàn':
      return 1;
    case 'lít':
    case 'lit':
    case 'l':
      return 100;
    case 'củ':
    case 'tr':
    case 'm':
    case 'M':
      return 1000;
    default:
      return 1;
  }
};
*/

const addExpense = (text) => {
  // const regex = /(.*)\s(\d*)(\w*)/g;
  const regex = /([tc])\s(.*)\s(\d*[.]\d*|\d*)/g;
  const label = text.replace(regex, '$2');
  const priceText = text.replace(regex, '$3');
  //const unitLabel = text.replace(regex, '$3');
  // const time = new Date().toLocaleString();
  const month = (new Date()).getMonth() + 1;
  // const price = Number(priceText) * getMultiplyBase(unitLabel);
  const price = Number(priceText);

  if (text.replace(regex, '$1')=='c') {
    addNewRow([month, label, price, null]);
  }
  else if (text.replace(regex, '$1')=='t') {
    addNewRow([month, label, null, price]);
  }
}

// Webhooks

const doPost = (request) =>{
  const contents = JSON.parse(request.postData.contents);
  const text = contents.message.text;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  addExpense(text); 
  const totalExpenses = sheet.getRange(SUM_CELL).getValue().toLocaleString('vi-VN', {style : 'currency', currency : 'VND'});
  // sendMessage(`Tổng chi tiêu: ${totalExpenses}`);
  sendMessage('Done!');
}