awesome-notebooks/Slack/Slack_Add_new_user_to_Google_Sheets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a42f61bb",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "<img width=\"8%\" alt=\"Slack.png\" src=\"https://raw.githubusercontent.com/jupyter-naas/awesome-notebooks/master/.github/assets/logos/Slack.png\" style=\"border-radius: 15%\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be8895fb",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "# Slack - Add new user to Google Sheets\n",
    "<a href=\"https://bit.ly/3JyWIk6\">Give Feedback</a> | <a href=\"https://github.com/jupyter-naas/awesome-notebooks/issues/new?assignees=&labels=bug&template=bug_report.md&title=Slack+-+Add+new+user+to+Google+Sheets:+Error+short+description\">Bug report</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8236b2c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Tags:** #slack #googlesheets #operations #automation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fab5b35f",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Author:** [Sanjeet Attili](https://linkedin.com/in/sanjeet-attili-760bab190/)\n",
    "\n",
    "With this notebook, new Slack users are automatically added to your Google Sheets.\n",
    "<br/>References :\n",
    "- Slack api :\n",
    "- Slack SDK to use : [https://github.com/slackapi/python-slack-sdk](https://github.com/slackapi/python-slack-sdk)\n",
    "- Google Sheet naas driver : [https://docs.naas.ai/templates/google-sheets](https://docs.naas.ai/templates/google-sheets)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bcbcd61-3737-431d-9a02-fa094c6f0f29",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Last update:** 2023-04-12 (Created: 2022-04-25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b63de08",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Input\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "feb96882",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Import libraries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cad38c0e-aadd-4a36-adaa-03c93d2529c2",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "!pip install slack-sdk --user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "27f8e2d9",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "from naas_drivers import gsheet\n",
    "import naas\n",
    "import pandas as pd\n",
    "from slack_sdk import WebClient\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9e9aba2",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Setup Slack\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1ee2ab27",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "SLACK_BOT_TOKEN = \"xoxb-232887839156-1673274923699-vTF6xxxxxxxxxx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "165ecb9c-d89f-4302-87ba-d4957e34e053",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "client = WebClient(token=SLACK_BOT_TOKEN)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd066aa2",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Setup Google Sheets\n",
    "\n",
    "- Share your sheet with our service account : 🔗 [naas-share@naas-gsheets.iam.gserviceaccount.com](mailto:naas-share@naas-gsheets.iam.gserviceaccount.com)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7d0376c0",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "SPREADSHEET_URL = \"---\"\n",
    "SHEET_NAME = \"Sheet1\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4886be11",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Setup Naas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "516ecba7",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Schedule your notebook every hour\n",
    "naas.scheduler.add(cron=\"0 * * * *\")\n",
    "\n",
    "# -> Uncomment the line below and execute this cell to delete your scheduler\n",
    "# naas.scheduler.delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71c33c05",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Model\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb85aed0",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### List users from Slack workspace\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5c146d1",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def list_users():\n",
    "    df = pd.DataFrame()\n",
    "    idx = 0\n",
    "    for user_data in client.users_list().data[\"members\"]:\n",
    "        if (\n",
    "            \"real_name\" in user_data and user_data[\"real_name\"] != \"Slackbot\"\n",
    "        ) and not user_data[\"is_bot\"]:\n",
    "            df.loc[idx, \"NAME\"] = user_data[\"profile\"][\"real_name\"]\n",
    "            df.loc[idx, \"ID\"] = user_data[\"id\"]\n",
    "            df.loc[idx, \"FIRST_VIEWED_AT\"] = datetime.fromtimestamp(\n",
    "                user_data[\"updated\"]\n",
    "            )\n",
    "            idx += 1\n",
    "\n",
    "    return df\n",
    "\n",
    "\n",
    "df_slack = list_users()\n",
    "df_slack"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e156d8d",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get users from Google Sheet\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fda53e11",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_gsheet = gsheet.connect(SPREADSHEET_URL).get(sheet_name=SHEET_NAME)\n",
    "df_gsheet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df0c90b7",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get new users in Slack\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "933cf580",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_new_users(df_slack, df_gsheet):\n",
    "    if len(df_gsheet) == 0:\n",
    "        return df_slack\n",
    "    else:\n",
    "        historical_data = df_gsheet.ID.to_list()\n",
    "        df_new = pd.DataFrame()\n",
    "        for idx, row in df_slack.iterrows():\n",
    "            if row[\"ID\"] not in historical_data:\n",
    "                df_new = df_new.append(row)\n",
    "        return df_new\n",
    "\n",
    "\n",
    "df_new = get_new_users(df_slack, df_gsheet)\n",
    "df_new"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f586f449",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Output\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa96024d",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Send new users to Google Sheets\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "60bc353c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Send data to Google Sheets\n",
    "gsheet.connect(SPREADSHEET_URL).send(sheet_name=SHEET_NAME, data=df_new, append=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "naas": {
   "notebook_id": "69edbf0f3bece275b66ceff5c8dc5510c1db14429f958e7dc109afbf15f02bc2",
   "notebook_path": "Slack/Slack_Add_new_user_to_Google_Sheets.ipynb"
  },
  "papermill": {
   "default_parameters": {},
   "environment_variables": {},
   "parameters": {},
   "version": "2.3.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}