파이썬 스프레드 시트와 구글 뉴스 연동 - 내 일을 바꾸는 업무 자동화 8장

 

 

 

8.6-1

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.open_by_url('https://....')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# B1 셀의 데이터를 출력
print(worksheet.acell('B1').value)

 

8.6-2

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.open_by_url('https://....')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# 1행의 데이터를 가져옴
row = ws.row_values('1')
for cell in row:
    print(cell)

 

8.6-3

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.open_by_url('https://....')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# A열의 데이터를 가져옴
col = ws.col_values('A')
for cell in col:
    print(cell)

 

8.6-4

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.open_by_url('https://....')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# A열의 데이터를 가져옴
cells = ws.range('A1:B2')
for cell in cells:
    print(cell.value)

 

8.6-5

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.create('새로운 문서')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# A1셀에 데이터를 씀
ws.update_acell('A1', '데이터입니다')

8.6-6

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.create('새로운 문서')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# 한 행에 데이터를 씀
ws.append_row(('데이터1', '데이터2'))

 

8.6-7

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('alghost-auto-...json', scope)
gs = gspread.authorize(credentials)
doc = gs.create('새로운 문서')
# 첫번째 시트 가져오기
ws = doc.get_worksheet(0)
# 한 행에 데이터를 씀
ws.insert_row(('새로운 데이터1', '새로운 데이터2'), 2)

 

auto_gspread

import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from openpyxl import load_workbook
from openpyxl.utils.cell import get_column_letter
scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name('cred.json', scope)
gs = gspread.authorize(credentials)
spread = gs.create('자동화로 생성된 문서')
spread.share('alghost.lee@gmail.com', perm_type='user', role='owner')

#data_path = 'C:\\python\\examples\\2.4\\data'
data_path = './'
file_list = os.listdir(data_path)

for fname in file_list:
    if fname[-4:] != 'xlsx':
        continue
    file_path = os.path.join(data_path, fname)
    wb = load_workbook(file_path, read_only=True)
    ws = wb.active
    row_count = ws.max_row
    col_count = ws.max_column

    worksheet = spread.add_worksheet(fname, row_count, col_count)
    cells = worksheet.range('A1:' + get_column_letter(col_count) + str(row_count))

    idx = 0
    for row in ws.iter_rows():
        for cell in row:
            cells[idx].value = str(cell.value)
            idx = idx + 1

    worksheet.update_cells(cells)

#spread.share('직원0@gmail.com', perm_type='user', role='reader')
#spread.share('직원1@gmail.com', perm_type='user', role='writer')
#spread.share('직원2@gmail.com', perm_type='user', role='reader')

 

auto_news.py

import feedparser
from urllib.parse import quote
from my_email import send_mail
from openpyxl import Workbook
import ssl
base_rss_url = 'https://news.google.com/news/rss/headlines/section/topic'
business_rss = base_rss_url+quote('/BUSINESS.ko_kr/경제?ned=kr&hl=ko')
tech_rss = base_rss_url+quote('/SCITECH.ko_kr/과학기술?ned=kr&hl=ko')
health_rss = base_rss_url+quote('/HEALTH.ko_kr/건강?ned=kr&hl=ko')

xlsx = Workbook()
business_sheet = xlsx.create_sheet('경제')
business_sheet.append(['기사 제목', '링크', '날짜'])
ssl._create_default_https_context = ssl._create_unverified_context
news_list = feedparser.parse(business_rss)
for news in news_list['items']:
    business_sheet.append([news['title'], news['link'], news['published']])

tech_sheet = xlsx.create_sheet('과학기술')
tech_sheet.append(['기사 제목', '링크', '날짜'])
news_list = feedparser.parse(tech_rss)
for news in news_list['items']:
    tech_sheet.append([news['title'], news['link'], news['published']])

health_sheet = xlsx.create_sheet('건강')
health_sheet.append(['기사 제목', '링크', '날짜'])
news_list = feedparser.parse(health_rss)
for news in news_list['items']:
    health_sheet.append([news['title'], news['link'], news['published']])

file_name = 'news_list.xlsx'
xlsx.save(file_name)
send_mail('이태화', 'alghost.lee@gmail.com', '뉴스 수집 결과입니다.', file_name)

 

my_email.py

from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from smtplib import SMTP_SSL

SMTP_SERVER = 'smtp.naver.com'
SMTP_PORT = 465
SMTP_USER = 'lthlovelee'
# 실제 비밀번호를 입력해야 합니다.
SMTP_PASSWORD = ''


def send_mail(name, addr, contents, attachment=False):
    msg = MIMEMultipart('alternative')

    if attachment:
        msg = MIMEMultipart('mixed')

    msg['From'] = SMTP_USER
    msg['To'] = addr
    msg['Subject'] = name + '님, 메일이 도착했습니다.'

    text = MIMEText(contents)
    msg.attach(text)

    if attachment:
        from email.mime.base import MIMEBase
        from email import encoders

        file_data = MIMEBase('application', 'octet-stream')
        f = open(attachment, 'rb')
        file_contents = f.read()
        file_data.set_payload(file_contents)
        encoders.encode_base64(file_data)

        from os.path import basename
        filename = basename(attachment)
        file_data.add_header('Content-Disposition', 'attachment', filename=filename)
        msg.attach(file_data)

    smtp = SMTP_SSL(SMTP_SERVER, SMTP_PORT)
    smtp.login(SMTP_USER, SMTP_PASSWORD)
    smtp.sendmail('lthlovelee@naver.com', addr, msg.as_string())
    smtp.close()

 

수강생_결제정보.xlsx

0.07MB