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

Synopsis

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. 1. I want to set the send trigger to click the desktop icon instead of the timer etc.
2. I want to attach a specific file automatically
3. 3. 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

• Trigger is set as follows from the GAS editor Edit-> Trigger for current project-> Add trigger

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.

• See page below http://googleappsscript.hatenablog.com/entry/2017/08/30/120000 https://centerwave-callout.com/%E3%83%AD%E3%83%BC%E3%82%AB%E3%83%AB%E3%81%AE%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92gas%E3%81%A7%E6%89%B1%E3%81%86/

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.

• It is assumed that the recipient of the email will include you.

1. 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
2. If it arrives, the "Send completed" dialog is displayed on the Python side.
3. 3. 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.

Source

1. 1. Python: main part

auto_mail_tool.py

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
    option.add_argument('--lang=ja-JP')

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

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

    #Enter the address in the address bar
    drv.find_element_by_id('Email').send_keys(address)
    time.sleep(3)

    #Click the "Next" button
    drv.find_element_by_id('next').click()
    time.sleep(3)

    #Enter password
    drv.find_element_by_id('password').send_keys(pw)
    time.sleep(3)

    #Click the "Next" button
    drv.find_element_by_id('submit').click()
    time.sleep(5)

    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')

        time.sleep(5)

    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})
    f.Delete()

    time.sleep(3)

    #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)
    f.Upload()

    time.sleep(3)


#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')
            return

    #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)
    else:
        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'}
    return(wkdict[key])


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

2. GAS: Email sending part

sending_email.gs

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 + ')';
}

3. 3. PowerShell: For Wi-Fi connection / disconnection

3-1. When connecting

wifi_on.ps1

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

3-2. At the time of disconnection

wifi_off.ps1

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.

last

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.