$ pip install gspread oauth2client

#Email sending relations
from email.mime.text import MIMEText
from email.utils import formatdate
import smtplib
#Google API cooperation
import gspread 
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint 

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)

client = gspread.authorize(creds)

#I'm getting the first sheet titled python.
# sheet1 = client.open("python").sheet1

SPREADSHEET_KEY = '18j7KiKC1C0VhqZV_CQ7tx5n7mZfR6teKENvEDcuyRe0'
sheet1= client.open_by_key(SPREADSHEET_KEY).worksheet('sheet1') 
sheet2= client.open_by_key(SPREADSHEET_KEY).worksheet('sheet2') 

#Email content
body = sheet2.cell(2,2).value
title = sheet2.cell(2,1).value

#All values are assigned to a variable called data.
data = sheet1.get_all_records()

#Get the number of data
last_number = len(data)

for row in range(last_number):
    #Extract the information required to send an email
    full_name = data[row]["name"]  #name
    msg = MIMEText(full_name + body)
    pprint(msg)
    
    msg['Subject'] = sheet2.cell(2,1).value
    msg['From'] = '**************@gmail.com'
    msg['To'] = data[row]["mail address"]  # mail address
    msg['Date'] = formatdate()

    smtp = smtplib.SMTP('smtp.gmail.com', 587)
    smtp.ehlo()
    smtp.starttls()
    smtp.ehlo()
    smtp.login('**************@gmail.com', '**************')
    smtp.send_message(msg)
    smtp.close()

Recommended Posts