🧠 QnA Slack – Google Sheets Automation

Sistem ini menghubungkan:

Google Sheets → Node.js Backend → Slack Bot → Update kembali ke Google Sheets

🔄 Flow Sistem

1. Data QnA masuk ke Google Sheets
2. Apps Script trigger mengirim event ke backend
3. Backend mengirim pesan ke Slack
4. User klik tombol "Jawab"
5. Modal muncul
6. Jawaban disubmit
7. Backend update kembali ke Google Sheets

---

🏗 Architecture

Google Sheets (Apps Script Trigger)
↓
Node.js Backend
↓
Slack Bot (Interactive + Modal)
↓
Node.js Backend
↓
Update Google Sheets

---

🚀 1. Setup Slack App

1.1 Create App

1. Buka https://api.slack.com/apps
2. Klik "Create New App"
3. Pilih "From Scratch"
4. Beri nama app
5. Pilih workspace

---

1.2 OAuth & Bot Token

Masuk ke:

OAuth & Permissions

Tambahkan Bot Token Scopes:

• chat:write
• commands
• channels:read
• channels:join

Klik "Install to Workspace"

Copy Bot User OAuth Token

Simpan sebagai environment variable:

SLACK_BOT_TOKEN

---

1.3 Enable Interactivity

Masuk ke:

Interactivity & Shortcuts

Enable Interactivity → ON

Isi Request URL:

https://your-ngrok-url.ngrok-free.app/slack-action

Save.

---

1.4 Invite Bot ke Channel

Di Slack channel:

/invite @your-bot-name

Ambil Channel ID dari URL Slack (format seperti C1234567890)

Simpan sebagai:

CHANNEL_ID

---

📊 2. Setup Google Sheets

2.1 Buat Spreadsheet

Contoh struktur:

No	NIM	Pertanyaan (Mahasiswa)	Jawaban (Asisten)	Tanggapan (Mahasiswa)	Tanggapan (Assisten)
1	13522120	Kenapa harus Labpro?	Karena bisa belajar banyak core programming	Ahh masa, bukannya karena duit?	heheheh

---

2.2 Enable Google Sheets API

1. Buka https://console.cloud.google.com/
2. Create Project
3. Enable "Google Sheets API"
4. Go to Credentials
5. Create Credentials → Service Account
6. Download JSON file

Rename menjadi:

service-account.json

Letakkan di root project.

---

2.3 Share Spreadsheet ke Service Account

Buka Google Sheets → Share

Masukkan email service account:

your-service-account@project-id.iam.gserviceaccount.com

Beri akses Editor.

Jika tidak dilakukan → akan muncul permission error.

---

⚙️ 3. Setup Apps Script (Trigger dari Sheet)

Di Google Sheets:

Extensions → Apps Script

Berikut adalah contoh script yang dapat digunakan:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const col = e.range.getColumn();

  console.log("Sheet: "+ sheet)
  console.log("col, row" + col+" "+row)
  if (row > 8 && (col == 4 || col == 6)) {
    const id = sheet.getRange(row, 2).getValue();
    const nim = sheet.getRange(row, 3).getValue();
    const question = sheet.getRange(row, col).getValue();
    console.log(question)
    if (id && nim && question && sheet.getRange(row, col+1).getValue() == "") {
      let payload = {
        id: id,
        nim: nim, 
        question: question,
        rowToAns: row,
        colToAns: col+1,
      };
      if(col==6){
        const thread = {
          "q1": sheet.getRange(row, 4).getValue(),
          "a1": sheet.getRange(row, 5).getValue(),
        }
        payload = {
          ...payload, thread
        }
      }
      console.log(payload)
      try{
        UrlFetchApp.fetch("your endpoint", {
          method: "post",
          contentType: "application/json",
          payload: JSON.stringify(payload)
        });
      }catch(err){
        console.error(err)
      }
    }
  }
}

🖥 4. Backend Setup (Node.js)

4.1 Install Dependencies

npm init -y
npm install express googleapis dotenv node-fetch

---

4.2 Struktur Folder

project/
├─ app.js
├─ service-account.json
├─ .env

4.3 File .env

PORT=3000
SLACK_BOT_TOKEN=xoxb-xxxxxxxx
CHANNEL_ID=CXXXXXXXX
SPREADSHEET_ID=your_spreadsheet_id

Ambil SPREADSHEET_ID dari URL:

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit 

4.4 Jalankan Server

node app.js

🌍 5. Deployment

5.1 Local

1. Anda dapat mendeploynya secara locak lalu melakukan tunneling menggunakan ngrok ataupun melakukan deployment di vps kesayangan anda.
2. Copy HTTPS URL.
3. Update:
   • Slack Interactivity URL
   • Apps Script fetch URL