My function

Background

The original Intent of the bot is to help me add values to my Finance Google Sheets

Along the way I figured out more things I could do to the bot that can help in my Daily life.

For example, there was a day I forgot that I had a Medical apoointment. Hence I add a calandar function to my bot so I can see my Calendar from the bot.

The bot will also send me a daily update of:

• Calandar Events in my calandar
• Weather
• My month budget
• Todo list

The bot functions include:

• Check my budget in different category (Overall, Spending and Earning)
• Singapore Bus Arrival Time
• Google Calendar
• LTA Traffic Camera
• Weather (Rainfall and Forecast)
• Todo List

Last update 3/8/23

---

Get Value from google sheets

• Bellow is a code example of how to get a value from google sheets.
• Assuming that your Google App Script is an extension from your Existing Google Sheets

function get_value(){
    const ss = SpreadsheetApp.getActiveSpreadsheet() // To get the sheet
    const sheet1 = ss.getSheetByName('sheet1') // To define the sheet
    var get_cell_C2_value = sheet1.getRange(2,3).getValue(); // Row is 2 Column is 3
    // Alternatively
    var get_cell_C2_value = sheet1.getRange('C2').getValue(); // You can insert the Cell directly in
}

---

Insert Value to google sheets

function update_sheets(col,row,input) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('Sheet1');
  const add_to_sheet = bot.getRange(col+row).setValue(input)
}

---

Singapore LTA Bus Arrival

Prerequisite

• Register for LTA API at LTA DATAMALL
  • LTA will Email you with your API Account Key
  • Note!! Not the SDK Account
• Insert it to your script variable
  • In my case I named it as lta_key
  • My Code : const lta_key = "xxxxxxxxx"

Code

function get_bus_info(id, bsc,mssgid) {
  var requestOptions = {
    method: 'GET',
    headers: { "AccountKey": lta_key },
    redirect: 'follow'
  };
  var response = UrlFetchApp.fetch("http://datamall2.mytransport.sg/ltaodataservice/BusArrivalv2?BusStopCode=" + bsc, requestOptions)
  var lta = JSON.parse(response);

  var busservices_array = []
  var bus_dict = {};

  var busservices = lta.Services


  function load(type){
    if (type == 'SEA'){
      return 'Seats Available'
    } else if (type == 'SDA'){
      return 'Only Standing'
    } else if (type == 'LSD'){
      return ' Limited Standing'
    }
  }

  function type(bus){
    if (bus == "SD"){
      return "Single Deck"
    } else if (bus == "DD"){
      return "Double Deck"
    } else if (bus =="BD"){
      return "Long Train Bus"
    }
  }

  for (var no_of_bus = 0; no_of_bus < busservices.length; no_of_bus ++) {
    busservices_array.push(lta.Services[no_of_bus].ServiceNo)

    var arrival1 = lta.Services[no_of_bus].NextBus.EstimatedArrival
    var a1min = time_diff(arrival1)

    var a1_load = lta.Services[no_of_bus].NextBus.Load
    a1Load = load(a1_load)

    a1_type = lta.Services[no_of_bus].NextBus.Type
    a1Type = type(a1_type)

    var arrival2 = lta.Services[no_of_bus].NextBus2.EstimatedArrival
    var a2min = time_diff(arrival2)

    var a2_load = lta.Services[no_of_bus].NextBus2.Load
    a2Load = load(a2_load)

    a2_type = lta.Services[no_of_bus].NextBus2.Type
    a2Type = type(a2_type)

    var arrival3 = lta.Services[no_of_bus].NextBus3.EstimatedArrival
    var a3min = time_diff(arrival3)

    var a3_load = lta.Services[no_of_bus].NextBus3.Load
    a3Load = load(a3_load)

    a3_type = lta.Services[no_of_bus].NextBus3.Type
    a3Type = type(a3_type)

    bus_dict[lta.Services[no_of_bus].ServiceNo + "A1"] = a1min
    bus_dict[lta.Services[no_of_bus].ServiceNo + "A1_Load"] = a1Load
    bus_dict[lta.Services[no_of_bus].ServiceNo + "A1_Type"] = a1Type

    bus_dict[lta.Services[no_of_bus].ServiceNo + "A2"] = a2min
    bus_dict[lta.Services[no_of_bus].ServiceNo + "A2_Load"] = a2Load
    bus_dict[lta.Services[no_of_bus].ServiceNo + "A2_Type"] = a2Type

    bus_dict[lta.Services[no_of_bus].ServiceNo + "A3"] = a3min
    bus_dict[lta.Services[no_of_bus].ServiceNo + "A3_Load"] = a3Load
    bus_dict[lta.Services[no_of_bus].ServiceNo + "A3_Type"] = a3Type
  }

  var test = bus_dict[busservices_array[0] + "A1"]

  if (bsc == 66351){
    var text = "Bus stop Code : " + bsc + ", Serangoon go home"

  } else if(bsc == 63031) {
    var text = "Bus stop Code : " + bsc + ", Kovan go home"

  } else if(bsc == 77281) {
    var text = "Bus stop Code : " + bsc + ", Opp West Plaza"

  } else {
    var text = "Bus stop Code : " + bsc
  }

  for (var buses = 0; buses < busservices_array.length;buses ++) {
    var arrival_text = bus_dict[busservices_array[buses] + "A1"]
    var a1_load = bus_dict[busservices_array[buses] + "A1_Load"]
    var a1_Type = bus_dict[busservices_array[buses] + "A1_Type"]


    var next_bus = bus_dict[busservices_array[buses] + "A2"]
    var a2_load = bus_dict[busservices_array[buses] + "A2_Load"]
    var a2_Type = bus_dict[busservices_array[buses] + "A2_Type"]

    var next_next_bus = bus_dict[busservices_array[buses] + "A3"]
    var a3_load = bus_dict[busservices_array[buses] + "A3_Load"]
    var a3_Type = bus_dict[busservices_array[buses] + "A3_Type"]


    var text = text + "\n\nBus Number : " + busservices_array[buses] + "\nArriving In : " + arrival_text +  "\nBus Load : " + a1_load +  "\nBus Type : " + a1_Type + "\n\nNext Bus : " + next_bus +  "\nBus Load : " + a2_load +  "\nBus Type : " + a2_Type+  "\n\nNext Next Bus : " + next_next_bus  +  "\nBus Load : " + a3_load +  "\nBus Type : " + a3_Type+   "\n\n\n"
  }

  var newmsgid = Number(mssgid) + 1

  const command = "/updatebus" + newmsgid + "," + bsc + "," + id
  const delete_command = "/delete " + newmsgid + "," + id

  sendText(id,text,reply_markup(["Update","Done"], [command,delete_command]))
}

Explanation

• The Bus Stop Code will be from Bot as well as Chat ID and Message ID

1. Firstly, it will fetch the data of the Bus Stop Code from LTA API
2. I Nested 2 helper Function for Load type and Bus Type
3. I created a For loop for all the buses to get :
   • Bus arrival time
   • Bus Load
   • Bus Type
   • The same for the Next 2 arrving buses
   • All the data will be store in a dict
4. I preset some bus stop code name to suit my needs
5. The Dict I had store will be printed out in to a text
6. Created the Markup command
7. Send Text to user

For /updatebus : When the bot recieve that it will just call for this function

---

Google Calendar

• Get all your calendar events for that specific date
• Helper Function for the next code

Code

function get_calendar(date){
  var now = new Date();
  var cal = CalendarApp.getAllCalendars()
  var event_title_arr = []
  var desc_arr = []
  var start_time_arr = []
  var end_time_arr = []

  if (date == undefined){
    var set_date = 0
  } else {
    var set_date = date
  }

  var today = new Date(now.getTime() + Number(set_date) * 1000 * 60 * 60 * 24)

  var today_date = Utilities.formatDate(today, "GMT+8", "dd/MM/yy")

  for (var i = 0; i < cal.length;i++){
    var events = cal[i].getEventsForDay(today)
    var cal_name = cal[i].getName()

    for (var x = 0; x < events.length; x++){
        var event_title = events[x].getTitle()
        event_title_arr.push(event_title)

        var desc = events[x].getDescription()
        if (desc.includes("<div>") || desc == ""){
            desc_arr.push("")
        } else {
            desc_arr.push(desc)
        }

        var start_time = events[x].getStartTime()
        start_time = Utilities.formatDate(start_time, "GMT+8", "HH:mm")
        start_time_arr.push(start_time)

        var end_time = events[x].getEndTime()
        end_time = Utilities.formatDate(end_time, "GMT+8", "HH:mm")
        end_time_arr.push(end_time)

    }
  }

  var combine = [today_date,event_title_arr,desc_arr,start_time_arr,end_time_arr]
  return combine
}

Explanation

• If the Date is other Date, it will count the difference in date
• The Date have to be in this format Wed Mar 08 00:00:00 GMT+08:00 2023 . Hence just dd/MM/yy is not sufficient as input
• It will loop through all the events with details and add it into the list
• The text will be return

---

Get Events from Google Calendar

• One of my command in my bot /today so I can see what is up for today
• Else you can insert date to get the calendar for that date

Code

function events_from_cal(date_input){
  var now = new Date()

  if (date_input == undefined){
    var set_date = 0
    var day = Utilities.formatDate(now, "GMT+8", "E")
  } else {
    var date = date_input.split("/")
    var day = date[0]
    day = Number(day)
    var month = date[1]
    var year = date[2]

    // get time
    var now_time = new Date()
    var input_time = new Date(month+' '+ day +', '+ year+' 00:00:00 +0800')
    var day = Utilities.formatDate(input_time, "GMT+8", "E")

    // get mili second
    var mili_now = now_time.getTime()
    var mili_input = input_time.getTime()
    var diffInday = Math.floor((mili_input-mili_now) / (86400000))

    // 1 day == 86,400,000 miliseconds
    var set_date = diffInday + 1
  }


  var all_today = get_calendar(set_date)
  var text = 'Calendar for : ' + all_today[0] + " ,"+ day + '\n' ;

  //Loop the number of stuff in all today

  for (var i = 0; i < all_today[1].length; i++){

    var title = all_today[1][i]
    var desc = all_today[2][i]
    var start = all_today[3][i]
    var end = all_today[4][i]

    if (start == end  || start == '00:00' || end == '00:00'){
      var time = 'Whole Day'
    } else {
      var time = start + ' : ' + end
    }

    if (desc == ''){
      desc = ''
    } else {
      desc = '\nEvent Description : ' + desc
    }

    var text = text +  '\nTitle : ' + title + desc + '\nTime : '+time +'\n'
  }
  return text
}

Explanation

• If date is not set, It will assume it is today
• It will call for previous function to get the calendar
• The text will be returned

---

Daily Update

• You can make use of Google App Script Triggers (Clock Icon at the side left side of the script) to run your script at preprogrammed timing
• In this case will be daily.
• But the time can only be in a range, for me I set 6am to 7am
• Note!! There is some bug where when I ran it on 1am , the date is still on previous date, hence to be safe I recomend to run your daily script after 3am.

Code

function daily_update(){
  now_text = today_events_from_cal() + "\n_________________________\n\n~~~~ Current Weather~~~~ \n"+ rain() +"\n\n"+forecast()+"\n\n_________________________" +"\n\n$$$$$$$$ Finance $$$$$$$$\n" + now() + "\n\n\n_________________________\n\n\n" + get_todo() + "\n\n\n"

  sendText(myid,now_text)
}

---

LTA Camera

• Same API as Bus Arrival
• I only getting Both SG-MY causeway images

Code

function get_lta_camera(id,mssgid,location) {
  var requestOptions = {
    method: 'GET',
    headers: { "AccountKey": lta_key },
    redirect: 'follow'
  };
  var response = UrlFetchApp.fetch("http://datamall2.mytransport.sg/ltaodataservice/Traffic-Imagesv2?", requestOptions)

  var lta = JSON.parse(response);

  var values = lta.value.length
  var image_counter = 0
  if (location == "first link"){
  // cause way first link   1.447023728     1.445554109
    for (counter = 0; counter < values; counter ++){
      var lat = lta.value[counter].Latitude
      // Logger.log(link)    

      if (Number(lat) == 1.447023728 || Number(lat) == 1.445554109 ){
        image_link = lta.value[counter].ImageLink
        var newmsgid = Number(mssgid) + image_counter + 1
        const delete_command = "/delete " + newmsgid + "," + id
        sendPhoto(id,image_link,'First Link',reply_markup(["Done"], [delete_command]))
        image_counter = image_counter + 1
      }
    }  

  } else if (location == "second link"){

    for (counter = 0; counter < values; counter ++){
      var long = lta.value[counter].Longitude
      var lat = lta.value[counter].Latitude
      // Logger.log(link)    

      if (Number(long) < 103.65 && Number(lat) < 1.35 ){
        image_link = lta.value[counter].ImageLink
        Logger.log(image_link)

        var newmsgid = Number(mssgid) + image_counter + 1 
        const delete_command = "/delete " + newmsgid + "," + id
        sendPhoto(id,image_link,'Second Link',reply_markup(["Done"], [delete_command]))
        image_counter = image_counter + 1
      }
    }

  }

}

---

Current Rain Fall

• Called from NEA API
• I use 'agaration' to split the area.

Code

function rain(){
  var now = new Date()
  var now_time = Utilities.formatDate(now, "GMT+8", "hh:mm:ss")
  var now_date = Utilities.formatDate(now, "GMT+8", "yyyy-MM-dd")
  var req_now = now_date + "T" + now_time
  var requestOptions = {
    method: 'GET',
    date_time: req_now,
    redirect: 'follow'
  };
  var response = UrlFetchApp.fetch("https://api.data.gov.sg/v1/environment/rainfall" ,requestOptions)
  var rain = JSON.parse(response)

  var from_nea = rain.metadata.stations

  var north_id = []
  var southcentral_id = []
  var east_id = []
  var west_id = []

  for (var i = 0; i < from_nea.length;i ++){
    var get_id = from_nea[i]["id"]
    var lat = from_nea[i]["location"]["latitude"]
    var long = from_nea[i]["location"]["longitude"]

    if (Number(lat) > 1.369 && Number(long) > 103.77 && Number(long) < 103.9 ){
      north_id.push(get_id)

    } else if (Number(long) > 103.9){
      east_id.push(get_id)

    } else if (Number(long) < 103.77) {
      west_id.push(get_id)

    } else {
      southcentral_id.push(get_id)
    }
  }


  var time = rain.items[0].timestamp
  var stations = rain.items[0].readings

  var total_north = 0
  var total_souuthcentral = 0
  var total_east = 0
  var total_west = 0

  for (var x = 0; x < stations.length; x ++){
    var station_id = stations[x]["station_id"]

    station_rainfall =stations[x]["value"]
    // Logger.log(station_id + " rainfall : "+ station_rainfall)
    if (north_id.includes(station_id)){
      total_north = total_north + Number(station_rainfall)

    } else if (southcentral_id.includes(station_id)){
      total_souuthcentral = total_souuthcentral + Number(station_rainfall)

    } else if (east_id.includes(station_id)){
      total_east = total_east + Number(station_rainfall)

    } else if (west_id.includes(station_id)){
      total_west = total_west + Number(station_rainfall)
    }

  }

  total_rainfall = total_north + total_souuthcentral + total_east + total_west


  text = "Currently Rain Area\n"

  // Logger.log(east_id)

  if (total_rainfall > 0){
    if (total_north > 0){
      text = text + "\n- North"

    } 
    if (total_souuthcentral > 0){
      text = text + "\n- South & Central"

    } 
    if (total_east > 0){
      text = text + "\n- East"

    } 
    if (total_west > 0){
      text = text + "\n- West"
    }

    return text

  } else {
    return "No Rain :)"
  }

}

---

Forecast

• Same API as Rain
• Some may argue with me, but I only care is there water droping from the sky and not the intensity
• You may adjust it to your needs

Code

function forecast(){
  var now = new Date()
  var now_time = Utilities.formatDate(now, "GMT+8", "hh:mm:ss")
  var now_date = Utilities.formatDate(now, "GMT+8", "yyyy-MM-DD")
  var req_now = now_date + "T" + now_time

  var requestOptions = {
    method: 'GET',
    date_time: req_now,
    redirect: 'follow'
  };

  var response = UrlFetchApp.fetch("https://api.data.gov.sg/v1/environment/2-hour-weather-forecast" ,requestOptions)
  var forecast = JSON.parse(response)


  var areas = forecast.items[0].forecasts
  var forecast_rain = []

  for (var x = 0 ; x < areas.length; x ++){
    var area = areas[x]["area"]
    var forecasts = areas[x]["forecast"]

    if (forecasts == "Light Rain" || forecasts == "Moderate Rain" || forecasts == "Heavy Rain" || forecasts == "Passing Showers" || forecasts == "Light Showers" || forecasts == "Showers" || forecasts == "Heavy Showers" || forecasts == "Thundery Showers" || forecasts == "Heavy Thundery Showers" || forecasts == "Heavy Thundery Showers with Gusty Winds") {
      forecast_rain.push(area)
    }
  }

  if (forecast_rain.length == 0){
    return "2 Hour Forecast : No Rain"
  } else {
    var forecast_text = "2 Hour Forecast : \nRain expected at"

    for (var i = 0; i < forecast_rain.length; i ++){
      forecast_text = forecast_text + "\n- " + forecast_rain[i]
    }

    return forecast_text
  }
}

Todo List

• As I add stuff to do, I get lost track of stuff I wanted to do, Hence why not make one and the bot will remind me daily!
• There are 3 part to it, Adding, Removing and Viewing of the list in google sheets

Code

function add_todo(id,text,mssgid){
  var ss = SpreadsheetApp.getActive();
  const bot = ss.getSheetByName('bot');

  for (var row = 25; row <= 44; row++){
    const get_curr_row = bot.getRange(row,2).getValue()
    if (get_curr_row == ""){

      var now = new Date()
      var date = Utilities.formatDate(now, "GMT+8", "dd/MM/yy")

      bot.getRange(row,2).setValue(text)
      bot.getRange(row,3).setValue(date)

      var newmsgid = Number(mssgid) + 1
      const delete_command = "/delete " + newmsgid + "," + id
      sendText(id,get_todo(),reply_markup(["Done"],[delete_command]))

      break;
    }
  }
}


function done_todo(id,index,mssgid){
  var ss = SpreadsheetApp.getActive();
  const bot = ss.getSheetByName('bot');
  index = Number(index)
  var get_last_row = bot.getRange("B25:B44").getValues();
  var last_row = get_last_row.filter(String).length;
  last_row = last_row + 24

  bot.getRange(index+24,2).setValue("")
  bot.getRange(index+24,3).setValue("")

  for (var row = index+24; row <= last_row; row++){
    const next_row_task = bot.getRange(row+1,2).getValue()
    const next_row_date = bot.getRange(row+1,3).getValue()
    bot.getRange(row,2).setValue(next_row_task)
    bot.getRange(row,3).setValue(next_row_date)
  }

  var newmsgid = Number(mssgid) + 1
  const delete_command = "/delete " + newmsgid + "," + id
  sendText(id,get_todo(),reply_markup(["Done"],[delete_command]))
}


function get_todo(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const bot = ss.getSheetByName('bot');

  const todo_index = bot.getRange("A25:A44").getValues()
  var no_of_row = Number(todo_index.filter(String).length)

  var todo_list = "Todo :\n"
  var first_row = 25
  for (var i = 0 ; i < no_of_row; i ++){
    var task = bot.getRange(first_row,2).getValue()

    var text = i+1 + ". " + task

    todo_list = todo_list +"\n "+text
    first_row = first_row + 1
  }

  return todo_list
}