Googleスプレッドシートメール送信.ipynb

google.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/gist/yogox/6ed8c0458bbab7870c6a57a4138260de/google.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### メール設定\n",
        "# URL\n",
        "setting_url = \"https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/\"\n",
        "# シート\n",
        "setting_sheet = \"シート1\"\n",
        "\n",
        "### メール内容\n",
        "# URL\n",
        "contents_url = \"https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/\"\n",
        "# シート\n",
        "contents_sheet = \"シート1\""
      ],
      "metadata": {
        "id": "vv3fNUiTkNAH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# スプレッドシート関連をimport\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "import gspread\n",
        "from google.auth import default\n",
        "# メール関連をimport\n",
        "import smtplib\n",
        "from email.mime.text import MIMEText\n",
        "from email.mime.multipart import  MIMEMultipart"
      ],
      "metadata": {
        "id": "EmdsRUjhirpb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "### メールサーバ設定\n",
        "# スプレッドシートを読み込む\n",
        "credential1, _ = default()\n",
        "gc1 = gspread.authorize(credential1)\n",
        "spread_sheet１ = gc1.open_by_url(setting_url)\n",
        "\n",
        "# 認証情報を'認証情報'シートから取得する\n",
        "sheet1 = spread_sheet１.worksheet(setting_sheet)\n",
        "certification_dictonary = sheet1.get_all_records()\n",
        "\n",
        "server_hostname = certification_dictonary[0]['server_hostname']\n",
        "server_port = certification_dictonary[0]['server_port']\n",
        "sever_account = certification_dictonary[0]['sever_account']\n",
        "sever_password = certification_dictonary[0]['sever_password']"
      ],
      "metadata": {
        "id": "1CGt3Lxxg_CL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# メールサーバにログイン\n",
        "server = smtplib.SMTP_SSL(server_hostname, server_port)\n",
        "server.login(sever_account, sever_password)"
      ],
      "metadata": {
        "id": "ybmGR5HIj4kJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "### メール内容読み込み\n",
        "# スプレッドシートを読み込む\n",
        "credential2, _ = default()\n",
        "gc2 = gspread.authorize(credential2)\n",
        "## スプレッドシートを開く（シートURLから）\n",
        "\n",
        "spread_sheet2 = gc2.open_by_url(contents_url)\n",
        "sheet2 = spread_sheet2.worksheet(contents_sheet)\n",
        "\n",
        "# メール内容をシートから取得する\n",
        "mail_contents_list = sheet2.get_all_records()"
      ],
      "metadata": {
        "id": "H80I7p7XedI_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# メール内容を回してメール送信する\n",
        "for r in mail_contents_list:\n",
        "  mail_to = r['to_text'].split('\\n')\n",
        "  mail_cc = r['cc_text'].split('\\n')\n",
        "  mail_subject = r['subject']\n",
        "  mail_message = r['message']\n",
        "\n",
        "  message = MIMEText(mail_message, 'plain')\n",
        "  message['subject'] = mail_subject\n",
        "  message['to'] = \",\".join(mail_to)\n",
        "  message['from'] = sever_account\n",
        "  message['cc'] = \",\".join(mail_cc)\n",
        "  # message['bcc'] = sever_account\n",
        "\n",
        "  server.send_message(message)"
      ],
      "metadata": {
        "id": "DFSQVK6xkEjt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "server.quit()"
      ],
      "metadata": {
        "id": "HP2rJ9dVki1b"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}