[GO] I tried to make it possible to automatically send an email just by double-clicking the [GAS / Python] icon


It is troublesome because the work of sending e-mails is constantly occurring. I decided to automate it.

For automation, we decided to create one that meets the following requirements.

    1. I want to set the send trigger to click the desktop icon instead of the timer etc.
  1. I want to attach a specific file automatically
    1. I want to go online only when sending an email and go offline immediately after sending

Therefore, GAS (Google Apps Script) seems to be convenient and I have no experience in using it, so I decided to make a tool with GAS that meets the above requirements.

First of all, I considered whether the above three requirements were really realized.

Requirement 1: I want the send trigger to be a click such as a desktop icon instead of a timer

As far as I actually tried and investigated GAS a little, unfortunately the GAS execution trigger is There seemed to be only the following.

① From the spreadsheet ② Time-driven ③ From the calendar

Of these, (1) If you check the details of the trigger setting in the spreadsheet,

・ At startup ・ When editing ・ At the time of change ・ When submitting a form

There is, isn't it possible to use "at startup" as a trigger? I thought Trigger triggered by opening a spreadsheet and trying to send an email I came up with it.

So, by opening a spreadsheet with GAS in Python's Selenium, I decided to use it as a trigger.

... I think there might be an easier and easier way, Triggering a spreadsheet launch doesn't seem to hurt anything, There is also the purpose of using an unexperienced GAS, so I decided to go with this.

I downloaded the chrome driver required when running Selenium from the following page. https://sites.google.com/a/chromium.org/chromedriver/downloads

Requirement 2: I want to attach a specific file automatically

It turned out to be surprisingly difficult to do this with GAS.

Because, as long as the spreadsheet exists on the server, the GAS script also seems to be executed on the server side, and it seems that it is difficult to operate the file on the local PC that is the client.

For that reason, for example, I knew how to read a local CSV or text file and then extract the information described in those files, but I didn't know how to attach the local file in that file format.

On the other hand, it seems that you can easily attach files on the server side, such as Google Drive.

Therefore, I decided to upload the local file I want to attach to Google Drive and attach it. It's a bit confusing because it goes through Google Drive once, but if there are no operational problems, this is OK.

For uploading to Google Drive, Python's PyDrive seems to be convenient, so I decided to go with it.

I first thought of using GAS, but Python again. ..

Requirement 3: I want to go online only when sending an email and go offline immediately after sending

Since GAS is executed on the server side, it is difficult to operate the local environment as well as the local file, and since Python is involved in requirements 1 and 2 above, I thought that requirement 3 would also be implemented in Python. I thought lightly that there was only a package that could easily connect / disconnect Wi-Fi, but unfortunately I couldn't find it.

However, I found that PowserShell makes it easy to operate Wi-Fi, and that PowerShell scripts can also be called easily from the Python side, so I decided to perform Wi-Fi operation with PowerShell.

Automation tool flow

I found that requirements 1 to 3 were all realized, but the means for realizing them became a little complicated, so I decided to organize them once.


The first half to the middle of the process is as already mentioned, but the problem is the second half. Ideally, if you start GAS and send an email, send the ack to Python running locally, and at that time call a PowerShell script to disconnect Wi-Fi, take it offline, and finish. However, as usual, it is difficult to link with the local environment, so we have decided to deal with it below.

    1. After hitting the URL of the spreadsheet on the Python side, go to the Gmail inbox about every second and check every time if the automatically sent email has arrived
  1. If it arrives, the "Send completed" dialog is displayed on the Python side.
    1. If the automatically sent email does not arrive within a certain period of time (about 30 seconds?) After hitting the URL of the spreadsheet, the "Send failed" dialog is displayed on the Python side.

With the above measures, the user will be able to know the success / failure of sending an email.

When this happens, I'm wondering if it's okay to use Python for everything ... but one of the purposes of this case is to "try using a GAS that I have never used", so it can be realized with GAS. I will go with GAS for the part.

Other / detailed design

It's lonely that the spreadsheet that opens as a trigger to start GAS really has no role other than just a trigger, and I decided that it was not good to hard-code the destination address when the number of email destinations increases in the future. I decided to list the shipping addresses in a spreadsheet.


As shown in the figure, write in order from cell A1 to the bottom.

Implementation results

Put the created Python script and PowerShell script in the appropriate place, write GAS as described above, set the trigger, enable the GoogleDrive API, put the .py shortcut on the desktop, and hit it to automatically mail You can send. For reference, the implementation results are as follows.



    1. Python: main part


import os
import time
import email
import imaplib
import datetime
import chromedriver_binary
from selenium import webdriver
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from selenium.webdriver.chrome.options import Options

#Set Google account address and password
address = 'mail address'
pw = 'password'

#Open the spreadsheet, launch gas and send an email
def generate_gas_script():

    print('Sending an email ...')

    #Headless launch of browser(Background display)Settings for
    option = Options()  #Options available
    option.add_argument('--headless')  #Give headless mode setting

    #Open the browser to operate
    drv = webdriver.Chrome(options=option)

    #Open the page to operate
    drv.get("URL of the spreadsheet you want to open")

    #Enter the address in the address bar

    #Click the "Next" button

    #Enter password

    #Click the "Next" button

    drv.quit()  #The spreadsheet should have opened, so close the browser

#Connect to wifi/Disconnect
def wifi(mode):

    if mode == 'connect':  #Connect to wifi of specified SSID (SSID itself is set in PowerShell)
        os.system('powershell -Command' + ' ' + \
                  'powershell -ExecutionPolicy RemoteSigned .\\wifi_on.ps1')


    elif mode == 'disconnect':  #Disconnect from connected wifi

        os.system('powershell -Command' + ' ' + \
                  'powershell -ExecutionPolicy RemoteSigned .\\wifi_off.ps1')

#Upload the file you want to attach to the outgoing email to Google Drive
def up_file_on_drive():

    #Set the path and file name where the file to be uploaded is located
    tgtfolder = 'The path where the file you want to attach is located'
    tgtfile = 'The name of the file you want to attach'
    dlttgt = 'title = ' + '"' + tgtfile + '"'  #Used to get the file ID

    #Authentication process for using Google Drive API
    gauth = GoogleAuth()
    drive = GoogleDrive(gauth)

    #If a file with the same name is already stored, delete it
    file_id = drive.ListFile({'q': dlttgt}).GetList()[0]['id']
    f = drive.CreateFile({'id': file_id})


    #Upload file
    folder_id = 'Google Drive file storage ID'
    f = drive.CreateFile({'title': tgtfile,
                         'mimeType': 'excel/xlsx',
                         'parents': [{'kind': 'drive#fileLink', 'id':folder_id}]})
    f.SetContentFile(tgtfolder + tgtfile)


#Check if the email was sent
def confirm_mail_sent():

    tgtac = imaplib.IMAP4_SSL('imap.gmail.com', 993)  #gmail Incoming mail server (IMAP) host name and port number for receiving mail using SSL
    tgtac.login(address, pw)

    waitsec = 30  #Email transmission confirmation logic timeout time[sec]

    tgtmail = 'Send "Email subject" set on the GAS side' + get_today()

    #Check from the latest email every second to see if the automatically sent email is received
    for i in range(waitsec, 0, -1):

        title = get_latest_mail_title(tgtac)  #Get the subject of the latest email

        time.sleep(1)  #1 loop 1 second

        if title == tgtmail:  #If the subject of the latest email is that of an automatically sent email
            print('\n Automatic mail transmission is complete.\n')

    #If the confirmation time times out
    print('\n Failed to send mail automatically.\n')

#Get the subject of the latest email
def get_latest_mail_title(mail):

    mail.select('inbox')  #Mailbox selection
    data = mail.search(None, 'ALL')[1]  #Get all the data in your mailbox
    tgt = data[0].split()[-1]  #Get the latest mail order
    x = mail.fetch(tgt, 'RFC822')[1]  #Get email information (specify a standard that can be read by Gmail)
    ms = email.message_from_string(x[0][1].decode('iso-2022-jp'))  #Perspective and get

    sb = email.header.decode_header(ms.get('Subject'))
    ms_code = sb[0][1]  #Character code acquisition

    #Get only the subject of the latest email
    if ms_code != None:
        mtitle = sb[0][0].decode(ms_code)
        mtitle = sb[0][0]

    return mtitle

#Get today's date
def get_today():

    now = datetime.date.today()
    tdy = str(now.year) + '/' + str(now.month) + '/' + str(now.day)  #Display by date
    wknum = now.weekday()  #Get today's day number (0):Month... 6:Day)
    wk = get_now_weekday(wknum)  #Get today's day

    return tdy + '(' + wk + ')'

#Get today's day
def get_now_weekday(key):

    wkdict = {0: 'Month', 1: 'fire', 2: 'water', 3: 'wood', 4: 'Money', 5: 'soil', 6: 'Day'}

if __name__ == '__main__':

    wifi('connect')        # 1.Wi of the specified SSID-Connect Fi
    up_file_on_drive()     # 2.Upload the file you want to attach to Google Drive once
    generate_gas_script()  # 3.Open the spreadsheet and launch GAS
    confirm_mail_sent()    # 4.Check if the email sent by GAS has arrived
    wifi('disconnect')     # 5.Disconnect wifi

    os.system('PAUSE')     #Stop the console

  1. GAS: Email sending part


function mail_send() {
  //Set email information
  var recip = get_recipient(); //Get the recipient's email address (listed in the spreadsheet)
  var subject  = 'Email subject:' + get_nowdate(); 
  var yourname = 'To all of you';
  var myname   = '○○ charge';
  var body = yourname + '\n\n' + myname + 'is.\n\n' + 'I will send you today's ○○.\n\Thank you for n or more.'
  var filename = 'The name of the file you want to attach';
  var foldername = 'test'
  var tgtfile = DriveApp.getFilesByName(filename).next();
  const options = {name: '○○ charge', attachments:[tgtfile]};
  //send e-mail
  GmailApp.sendEmail(recip, subject, body, options);

//Get the destination email address in the spreadsheet
function get_recipient(){
  //Set the target sheet of the spreadsheet
  var spdsht = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spdsht.getActiveSheet();
  //Get the address listed on the first line
  var tgtcell = sheet.getRange('A1');
  var rcps = tgtcell.getValue();
  var tgtval = rcps;
  //Get the address described in the second and subsequent lines
  for (let i = 2; tgtval != ''; i++)
    tgtcell = sheet.getRange('A' + i);
    tgtval = tgtcell.getValue();
    rcps = rcps + ', ' + tgtval;
  return rcps;
//  console.log(rcps) //debug

//Get today's date (including day of the week)
function get_nowdate()
  var da = new Date();

  //Get today's date
  var y = da.getFullYear();
  var m = da.getMonth() + 1;  //The moon-To get with a value of 1
  var d = da.getDate();
  //Get today's day
  var downum = da.getDay(); //Get the day number
  var downow = ['Day', 'Month', 'fire', 'water', 'wood', 'Money', 'soil'][downum]; // 曜Day番号に対応する曜Dayを選択

  return y + '/' + m + '/' + d + '(' + downow + ')';
    1. PowerShell: For Wi-Fi connection / disconnection

3-1. When connecting


netsh wlan connect name="Wi-fi you want to connect to-Fi SSID"

3-2. At the time of disconnection


netsh wlan disconnect

Remarks: If you want to call .py from MATLAB

Write the following in .m using the system function.

system('Want to run.py path')

Other / thought

-The process of opening the browser to open the spreadsheet in Selenium is done headlessly, For some reason, when I started it headless, a different screen was displayed compared to when it wasn't, and I suffered a little. (The fact that the screen is different was discovered by screen capture with save_screenshot)   After all, the above is the result of dividing on different screens.

Reference: https://teratail.com/questions/276976

・ I wonder if GAS does not have a function equivalent to Python's interactive mode. .. I think it would be very convenient if there was one.

Information that was used as a reference

In particular, I was indebted to the following sites. Thank you very much.

Contents Link destination
Send email by GAS https://tonari-it.com/gas-gmail-sendemail/
GAS execution trigger setting https://takakisan.com/gas-trigger-introduction/
Get dates and days of the week with GAS http://www.googleappsscript.info/2017-07-27/get_now.html
Access spreadsheets with GAS https://qiita.com/negito6/items/c64a7a8589faaffcfdcf
Attach a file when sending an email with GAS https://qiita.com/tanaike/items/94c263d3906ee23ad522 https://news.mynavi.jp/article/gas-11/
GAS+Upload file to Google Drive with html https://officeforest.org/wp/2018/12/30/google-apps-script%E3%81%A7%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%A2%E3%83%83%E3%83%97%E3%83%AD%E3%83%BC%E3%83%80%E3%82%92%E4%BD%9C%E3%82%8B/ https://tonari-it.com/gas-web-app-google-script-run/ https://www.pre-practice.net/2018/01/google-drive.html
Upload files to Google Drive with PyDrive https://note.nkmk.me/python-pydrive-download-upload-delete/ https://qiita.com/akabei/items/f25e4f79dd7c2f754f0e
PowerShell script from Python (.ps1) call https://tkstock.site/2019/10/07/python-powershell-shellscript-activate/
Connecting / disconnecting to wifi by PowerShell https://qiita.com/mindwood/items/22e0895473578c4e0c7e http://wgg.hatenablog.jp/entry/20161111/1478846489
Delete files on Google Drive https://note.nkmk.me/python-pydrive-download-upload-delete/
Get Gmail incoming email information with imaplib https://py.minitukuc.com/2017/11/07/gmailhonbun/
Hide root window when using Tkinter dialog https://stackoverflow.com/questions/1406145/how-do-i-get-rid-of-python-tkinter-root-window
How to start Selenium headless https://watlab-blog.com/2019/08/18/selenium-chrome-background/ https://qiita.com/memakura/items/20a02161fa7e18d8a693
Screen capture for debugging Selenium headless boots https://qiita.com/orangain/items/6a166a65f5546df72a9d
Error handling when starting Selenium headless https://qiita.com/yukanashi/items/c7e954130029fe708b79


We welcome your suggestions, suggestions for improvement, and suggestions for mistakes. Please do not hesitate. I am delighted to win the lottery 100,000 yen.

Recommended Posts

I tried to make it possible to automatically send an email just by double-clicking the [GAS / Python] icon
I tried to make it possible to automatically send an email just by double-clicking the [Python] icon
Output the report as PDF from DB with Python and automatically attach it to an email and send it
To automatically send an email with an attachment using the Gmail API in Python
I tried to automatically send the literature of the new coronavirus to LINE with Python
It's getting cold, so I tried to make it possible to turn on / off the AC heater automatically with Raspberry Pi!
[Python] Simple Japanese ⇒ I tried to make an English translation tool
I tried to make an image similarity function with Python + OpenCV
Python: I tried to make a flat / flat_map just right with a generator
[Python] I tried to summarize the set type (set) in an easy-to-understand manner.
When I tried to run Python, it was skipped to the Microsoft Store
[Zaif] I tried to make it easy to trade virtual currencies with Python
I tried to analyze the New Year's card by myself using python
I tried to make a system to automatically acquire the program guide → register it in the calendar in one day
I tried to get an image by scraping
Send an email to Spushi's address with python
I tried using the Datetime module by Python
I tried sending an email with SendGrid + Python
I tried my best to make an optimization function, but it didn't work.
[Introduction] I tried to implement it by myself while explaining the binary search tree.
I tried to make the phone ring when it was posted at the IoT post
A python beginner tried to intern at an IT company [Day 3: Going to the clouds ...]
A Python beginner made a chat bot, so I tried to summarize how to make it.
[Introduction] I tried to implement it by myself while explaining to understand the binary tree
I tried to make it easy to change the setting of authenticated Proxy on Jupyter
I tried to graph the packages installed in Python
I tried to touch the CSV file with Python
I tried to solve the soma cube with python
Continuation ・ I tried to make Slackbot after studying Python3
I tried to implement an artificial perceptron with python
[Python] I tried to graph the top 10 eyeshadow rankings
I tried to automatically generate a password with Python3
I tried to solve the problem with Python Vol.1
I want to send a business start email automatically
I tried to make an OCR application with PySimpleGUI
I made an action to automatically format python code
I tried to summarize the string operations of Python
A super introduction to Django by Python beginners! Part 6 I tried to implement the login function
[Python] I tried to make an application that calculates salary according to working hours with tkinter
I tried to create a RESTful API by connecting the explosive Python framework FastAPI to MySQL.
I tried to make the political broadcast video like IPPON Grand Prix (OpenCV: Python version)
I tried to find the entropy of the image with python
I tried to simulate how the infection spreads with Python
I tried to make various "dummy data" with Python faker
I tried various methods to send Japanese mail with Python
I tried sending an email from Amazon SES with Python
Code to send an email based on the Excel email list
[Python] I tried to visualize the follow relationship of Twitter
I tried to implement the mail sending function in Python
I tried to enumerate the differences between java and python
I tried to make a stopwatch using tkinter in python
I tried to make GUI tic-tac-toe with Python and Tkinter
I tried changing the python script from 2.7.11 to 3.6.0 on windows10
A python beginner tried to intern at an IT company
I tried to divide the file into folders with Python
I tried to make a site that makes it easy to see the update information of Azure
I tried to make an image classification BOT by combining TensorFlow Lite and LINE Messaging API
I tried to verify the speaker identification by the Speaker Recognition API of Azure Cognitive Services with Python. # 1
I tried to verify the speaker identification by the Speaker Recognition API of Azure Cognitive Services with Python. # 2
I tried to find out the difference between A + = B and A = A + B in Python, so make a note
I tried to summarize the contents of each package saved by Python pip in one line