csv-to-google-calendar.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "!git clone https://github.com/mellieho9/technica-2022"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KUig1akczq6E",
        "outputId": "9a171677-dbf7-40ae-a2e8-3ec65afd852a"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Cloning into 'technica-2022'...\n",
            "remote: Enumerating objects: 83, done.\u001b[K\n",
            "remote: Counting objects: 100% (83/83), done.\u001b[K\n",
            "remote: Compressing objects: 100% (76/76), done.\u001b[K\n",
            "remote: Total 83 (delta 36), reused 16 (delta 7), pack-reused 0\u001b[K\n",
            "Unpacking objects: 100% (83/83), done.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install pyexcel"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ol60tnJT1aTb",
        "outputId": "904a2ed5-e66e-48df-9a03-506e28e20ce0"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pyexcel\n",
            "  Downloading pyexcel-0.7.0-py2.py3-none-any.whl (87 kB)\n",
            "\u001b[K     |████████████████████████████████| 87 kB 3.9 MB/s \n",
            "\u001b[?25hRequirement already satisfied: chardet in /usr/local/lib/python3.7/dist-packages (from pyexcel) (3.0.4)\n",
            "Collecting texttable>=0.8.2\n",
            "  Downloading texttable-1.6.4-py2.py3-none-any.whl (10 kB)\n",
            "Collecting pyexcel-io>=0.6.2\n",
            "  Downloading pyexcel_io-0.6.6-py2.py3-none-any.whl (44 kB)\n",
            "\u001b[K     |████████████████████████████████| 44 kB 2.7 MB/s \n",
            "\u001b[?25hCollecting lml>=0.0.4\n",
            "  Downloading lml-0.1.0-py2.py3-none-any.whl (10 kB)\n",
            "Installing collected packages: lml, texttable, pyexcel-io, pyexcel\n",
            "Successfully installed lml-0.1.0 pyexcel-0.7.0 pyexcel-io-0.6.6 texttable-1.6.4\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "pip install --upgrade pip"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5wqx0Wb1126H",
        "outputId": "55d587c4-9d72-4938-e7db-9857ad60ed3f"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: pip in /usr/local/lib/python3.7/dist-packages (21.1.3)\n",
            "Collecting pip\n",
            "  Downloading pip-22.3-py3-none-any.whl (2.1 MB)\n",
            "\u001b[K     |████████████████████████████████| 2.1 MB 7.2 MB/s \n",
            "\u001b[?25hInstalling collected packages: pip\n",
            "  Attempting uninstall: pip\n",
            "    Found existing installation: pip 21.1.3\n",
            "    Uninstalling pip-21.1.3:\n",
            "      Successfully uninstalled pip-21.1.3\n",
            "Successfully installed pip-22.3\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install -U pywin32"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PHI-5sHI1qsG",
        "outputId": "cb68e8bb-9aca-46e4-951f-fe9c73a49e8e"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "\u001b[31mERROR: Could not find a version that satisfies the requirement pywin32 (from versions: none)\u001b[0m\u001b[31m\n",
            "\u001b[0m\u001b[31mERROR: No matching distribution found for pywin32\u001b[0m\u001b[31m\n",
            "\u001b[0m"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 374
        },
        "id": "DoOzFs3aznSu",
        "outputId": "57623d0b-8514-4614-afc3-bced9126c04e"
      },
      "outputs": [
        {
          "output_type": "error",
          "ename": "ModuleNotFoundError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-22-80c70cc5473b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mwin32com\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclient\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDispatch\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpickle\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'win32com'",
            "",
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0;32m\nNOTE: If your import is failing due to a missing package, you can\nmanually install dependencies using either !pip or !apt.\n\nTo view examples of installing some common dependencies, click the\n\"Open Examples\" button below.\n\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n"
          ],
          "errorDetails": {
            "actions": [
              {
                "action": "open_url",
                "actionText": "Open Examples",
                "url": "/notebooks/snippets/importing_libraries.ipynb"
              }
            ]
          }
        }
      ],
      "source": [
        "import openpyxl as xl\n",
        "from openpyxl import Workbook, load_workbook\n",
        "import pyexcel as p\n",
        "from datetime import datetime\n",
        "import pandas as pd\n",
        "import os\n",
        "from win32com.client import Dispatch\n",
        "import pickle\n",
        "import datetime\n",
        "from collections import namedtuple\n",
        "from google_auth_oauthlib.flow import Flow, InstalledAppFlow\n",
        "from googleapiclient.discovery import build\n",
        "from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload\n",
        "from google.auth.transport.requests import Request\n",
        "\n",
        "\n",
        "# create api service function\n",
        "def create_service(client_secret_file, api_name, api_version, *scopes, prefix=''):\n",
        "\tCLIENT_SECRET_FILE = client_secret_file\n",
        "\tAPI_SERVICE_NAME = api_name\n",
        "\tAPI_VERSION = api_version\n",
        "\tSCOPES = [scope for scope in scopes[0]]\n",
        "\tcred = None\n",
        "\tworking_dir = os.getcwd()\n",
        "\ttoken_dir = 'token files'\n",
        "\tpickle_file = f'token_{API_SERVICE_NAME}_{API_VERSION}{prefix}.pickle'\n",
        "\n",
        "\t# check if token dir exists first, if not, create the folder\n",
        "\tif not os.path.exists(os.path.join(working_dir, token_dir)):\n",
        "\t\tos.mkdir(os.path.join(working_dir, token_dir))\n",
        "\tif os.path.exists(os.path.join(working_dir, token_dir, pickle_file)):\n",
        "\t\twith open(os.path.join(working_dir, token_dir, pickle_file), 'rb') as token:\n",
        "\t\t\tcred = pickle.load(token)\n",
        "\n",
        "\tif not cred or not cred.valid:\n",
        "\t\tif cred and cred.expired and cred.refresh_token:\n",
        "\t\t\tcred.refresh(Request())\n",
        "\t\telse:\n",
        "\t\t\tflow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)\n",
        "\t\t\tcred = flow.run_local_server()\n",
        "\n",
        "\t\twith open(os.path.join(working_dir, token_dir, pickle_file), 'wb') as token:\n",
        "\t\t\tpickle.dump(cred, token)\n",
        "\ttry:\n",
        "\t\tservice = build(API_SERVICE_NAME, API_VERSION, credentials=cred)\n",
        "\t\tprint(API_SERVICE_NAME, API_VERSION, 'service created successfully')\n",
        "\t\treturn service\n",
        "\texcept Exception as e:\n",
        "\t\tprint(e)\n",
        "\t\tprint(f'Failed to create service instance for {API_SERVICE_NAME}')\n",
        "\t\tos.remove(os.path.join(working_dir, token_dir, pickle_file))\n",
        "\t\treturn None\n",
        "\n",
        "\n",
        "# convert datetime function\n",
        "def convert_to_RFC_datetime(start_year=1900, start_month=1, start_day=1, hour=0, minute=0):\n",
        "\tdt = datetime.datetime(start_year, start_month, start_day, hour, minute, 0).isoformat() + 'Z'\n",
        "\treturn dt\n",
        "\n",
        "\n",
        "# launch api\n",
        "CLIENT_SECRET_FILE = \"client_secret.json\"      \n",
        "API_NAME = 'calendar'\n",
        "API_VERSION = 'v3'\n",
        "SCOPES = ['https://www.googleapis.com/auth/calendar']\n",
        "\n",
        "service = create_service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)\n",
        "\n",
        "\n",
        "# list calendars\n",
        "calendar_list = service.calendarList().list(pageToken=None).execute()\n",
        "\n",
        "\n",
        "# delete already existing calendar               ### COMPLETELY OPTIONAL - feel free to comment!!!!\n",
        "for calendar_list_entry in calendar_list['items']:  \n",
        "    if 'CALENDAR_NAME' in calendar_list_entry['summary']:\n",
        "        id = calendar_list_entry['id'] \n",
        "        service.calendars().delete(calendarId=id).execute()\n",
        "\n",
        "\n",
        "# create new calendar\n",
        "calendar_body = {\n",
        "    'summary': 'CALENDAR_NAME',\n",
        "    'timeZone': 'America/Sao_Paulo',    ### YOU CAN CHECK TIMEZONES TABLE -> https://en.wikipedia.org/wiki/List_of_tz_database_time_zones\n",
        "}\n",
        "service.calendars().insert(body=calendar_body).execute()\n",
        "\n",
        "\n",
        "# list calendars\n",
        "calendar_list = service.calendarList().list(pageToken=None).execute()\n",
        "\n",
        "\n",
        "# get calendar id\n",
        "for calendar_list_entry in calendar_list['items']:\n",
        "    if 'CALENDAR_NAME' in calendar_list_entry['summary']:\n",
        "        id = calendar_list_entry['id'] \n",
        "\n",
        "\n",
        "\n",
        "# insert events to google calendar function\n",
        "def insert_events(color):\n",
        "    # validate if it is an all-day event or not\n",
        "    def is_all_day_event(i):\n",
        "        if (\"FALSE\" or \"False\" or \"false\") in all_day_event[i]:\n",
        "            return False\n",
        "        else:\n",
        "            return True\n",
        "    x = 0\n",
        "    for i in range(0, max_rows):\n",
        "            adjust_timezone = 3 # (this is for UTC-3)         ### CHANGE TO YOUR OWN TIMEZONE, COULD BE -n, +n, or none, BASED ON UTC TIME (0)\n",
        "            event_request_body = {\n",
        "            'start':{\n",
        "                'dateTime': convert_to_RFC_datetime(int(start_year[i]), int(start_month[i]), int(start_day[i]), int(fstart_time[i]) + adjust_timezone, 0),\n",
        "                'timeZone': 'America/Sao_Paulo',\n",
        "            },\n",
        "            'summary': subject[i],\n",
        "            'description': description[i],\n",
        "            'location': location[i],\n",
        "            'colorId': color\n",
        "            #'attendees':[\n",
        "            #    {\n",
        "            #        'email': '',\n",
        "            #        'optional': False,\n",
        "            #        'responseStatus': 'accepted',\n",
        "            #    }\n",
        "            #],\n",
        "            #'reminders': {\n",
        "            #    'useDefault': False,\n",
        "            #    'overrides':[\n",
        "            #        {'method': 'email', 'minutes': 30},\n",
        "            #    ]\n",
        "            #}\n",
        "            }\n",
        "            service.events().insert(calendarId=id, body=event_request_body).execute()\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "# convert csv file to excel\n",
        "if not os.path.exists('excel_file.xlsx'):\n",
        "    read_file = pd.read_csv('csv_file.csv')                   # YOU MAY NEED TO DECLARE THE SPECIFIC ENCODING -> https://docs.python.org/3.7/library/codecs.html#standard-encodings\n",
        "    read_file.to_excel('excel_file.xlsx', index = None, header = True)\n",
        "\n",
        "\n",
        "\n",
        "# load xlsx file containing the events\n",
        "wb = load_workbook('excel_file.xlsx')\n",
        "ws = wb.active\n",
        "\n",
        "\n",
        "# delete header row\n",
        "ws.delete_rows(1)\n",
        "\n",
        "# row number variable\n",
        "max_rows = ws.max_row\n",
        "\n",
        "\n",
        "# copy subject\n",
        "subject = []\n",
        "for i in range(1, max_rows+1):\n",
        "    subject.append(ws.cell(row = i, column = 1).value)\n",
        "\n",
        "\n",
        "\n",
        "## FORMATTING START DATE\n",
        "# copy start date\n",
        "start_date = []\n",
        "for i in range(1, max_rows+1):\n",
        "    start_date.append(ws.cell(row = i, column = 2).value)\n",
        "\n",
        "# append lists\n",
        "start_month = []\n",
        "start_day = []\n",
        "start_year = []\n",
        "\n",
        "# format start month, day & year\n",
        "for i in range(0, max_rows):\n",
        "    start_month.append(start_date[i])\n",
        "    start_day.append(start_date[i])\n",
        "    start_year.append(start_date[i])\n",
        "\n",
        "start_month = [x[:-6] for x in start_month]\n",
        "start_day = [x[3:-3] for x in start_day]\n",
        "start_year = ['20' + x[6:] for x in start_year]\n",
        "\n",
        "\n",
        "\n",
        "## FORMATTING START TIME\n",
        "# copy start time\n",
        "start_time = []\n",
        "for i in range(1, max_rows+1):\n",
        "    start_time.append(ws.cell(row = i, column = 3).value)\n",
        "\n",
        "# format start time\n",
        "fstart_time = []\n",
        "for i in range(0, max_rows):\n",
        "    fstart_time.append(start_time[i])\n",
        "fstart_time = [x[:-3] for x in fstart_time]\n",
        "\n",
        "\n",
        "## FORMATTING END TIME\n",
        "# copy end time\n",
        "end_time = []\n",
        "for i in range(1, max_rows+1):\n",
        "    end_time.append(ws.cell(row = i, column = 4).value)\n",
        "\n",
        "# format end time\n",
        "fend_time = []\n",
        "for i in range(0, max_rows):\n",
        "    fend_time.append(end_time[i])\n",
        "fend_time = [x[:-3] for x in fend_time]\n",
        "\n",
        "\n",
        "# copy description\n",
        "description = []\n",
        "for i in range(1, max_rows+1):\n",
        "    description.append(ws.cell(row = i, column = 6).value)\n",
        "\n",
        "# copy location\n",
        "location = []\n",
        "for i in range(1, max_rows+1):\n",
        "    location.append(ws.cell(row = i, column = 7).value)\n",
        "\n",
        "\n",
        "# insert events(color) ---> check available colors here => https://lukeboyle.com/blog/posts/google-calendar-api-color-id\n",
        "insert_events(11)    "
      ]
    }
  ]
}