#!/usr/bin/python3
import sys
import codecs
import traceback
import redis
import requests
import re
import pandas as pd
import random
import urllib
import json
import gspread
import datetime
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import os
from itertools import islice

def save_sheet(df,filename,tabname,startpos='A1'):

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

    credentials = ServiceAccountCredentials.from_json_keyfile_name('/Users/zooeytsai/kw_tools/INNNews/innnews-d27b01472ba2.json', scope)
    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)

    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)

def get_sheets(filename):
    result=[]
    scope = ['https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive']

    credentials = ServiceAccountCredentials.from_json_keyfile_name('/Users/zooeytsai/kw_tools/INNNews/innnews-d27b01472ba2.json', scope)
    gc = gspread.authorize(credentials)
#    files=gc.list_spreadsheet_files()
#    print(files)
    spread = Spread(filename,creds=credentials)

    df=spread.sheet_to_df(sheet='2022')
    for idx, row in islice(df.iterrows(), 1, None):
        r1=row[1].strip()
        if len(r1)>1:
            r2=row[2].strip()
            r3=row[3].strip()
            if "病假" not in r1:
                result.append({'kw':r1+" "+r2+" "+r3})
            else:
                pass
    return result

result=get_sheets('引新聞INNEWS稿單xSEO優化')
js=json.dumps(result, ensure_ascii=False)

print(js)
# r = redis.Redis(host='db.ptt.cx', port=6379, db=1,password='choozmo9')
# r.set('innews_sheet',js)

#    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)
#