Use Google Spread Sheet To Execute Programs

Today I'm going to share a slack reminder bot The bot part is just call slack webhooks I used to do this kind of schedule programs with google cloud function But, because it cost money after the trial period passed So I decide to change to google spread sheet to do this

The user story is... As a member of our company welfare commitee We have to do the worship twice a month (do worship is kind of tradition for some of company in Taiwan) But, it's hard to remember which date is the day It calculated by lunar calendar As an engineer, we can not waste our time on remember this thing So I decide to make a slack reminder Plan is when the date is doing worship day, the bot will slack in our channel

There are three main thing I used, let me introduce them

1. Calculate the lunar date
2. put code on google spread sheet and schedule them
3. if it's the date! call slack webhook

Calculate the lunar date

This part I use Intl.DateTimeFormat(區域).format("date") example as following:

  const lunarDay = Intl.DateTimeFormat("zh-TW-u-ca-chinese").format(new Date("2022/12/12")); // '2022年冬月19'

  // I only need the word after "月"(month in chinese)
  const [, todayOfLunarCalendar] = lunarDay.split("月"); // 19

  // some simple condition, if 1 or 15 we do worship
  if(todayOfLunarCalendar === "1" || todayOfLunarCalendar === '15'){
    // send slack webhooks api
  }

lunar calendar refer from MDN

Google Sheet With code and schedule

First, you need to open a new spread sheet On the top tools, click Extensions -> App script And, here is where code are

  // mostly is same as normal coding
  function reminder () {
    // conditions
    if (todayOfLunarCalendar === "1" || todayOfLunarCalendar === "15") {
      // call slack api url will change to slack api later
      UrlFetchApp.fetch("url", {...config})
      return ContentService.createTextOutput(JSON.stringify({status:200, msg:"success"}))
    }
  }

  // note 1： In App Script, wnat to use fetch, you need to use => UrlFetchApp.fetch

  // note 2： When return Response,use return ContentService.createTextOutput(JSON.stringify(result))

You can find funcstions of app script in docs

Use Slack api send messages

I use slack webhook to send message First of all, Here are the docs. You need to create a new Apps first then in Building Apps for Slack Area, choose 『 Add features and functionality 』 Then choose Incoming Webhooks, click Add New WEbhook Tto Workspace It will create a URL, looks like: https://hooks.slack.com/services/T1CXXXXXX/B04DXXXXXXX/gkXXXXXXXXXXXXXXX You can test it with postman

  method: POST 
  Header: Content-type: application/json
  body:   {"text":"Hello, World!"}

Google Sheet sets schedule

After slack part is done. Back to App script and replace slack api inside to UrlFetchApp.fetch url (Now you can test your function with Run button on the top) Then, left hand side, in the sidebar click the alarm-icon(Triggers) Click blur button: Add Trigger + And, adjust for the triggers you need For example: I need that trigger once a week

Select event source: Time-driven Select type of time based trigger: Week timer Select day of week: Every Monday Select time of day: 9am to 10am

If there are errors, it will notify you If this is the first time of setting schedule App Script will ask for permission to do this After confirmed, everything are set up

This time, I learned how to calculate in chinese lunar date , Use google sheet as google cloud function I feel it convenient, and should not need to pay so far If there's any mistakes, misunderstanding or unclear You can leave a message for it, thank you!

this is today's sharing 👋