$ pip install gspread oauth2client

#E-Mail senden Beziehungen
from email.mime.text import MIMEText
from email.utils import formatdate
import smtplib
#Google API-Zusammenarbeit
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)

#Ich bekomme das erste Blatt mit dem Titel 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') 

#E-Mail-Inhalt
body = sheet2.cell(2,2).value
title = sheet2.cell(2,1).value

#Alle Werte werden einer Variablen namens data zugewiesen.
data = sheet1.get_all_records()

#Holen Sie sich die Anzahl der Daten
last_number = len(data)

for row in range(last_number):
    #Extrahieren Sie die Informationen, die zum Senden einer E-Mail erforderlich sind
    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 Adresse"]  # Mail Adresse
    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