fireHDで家計簿作成⑤ GoogleSpreadSheetをpythonで動かす
取り急ぎコードだけ
import gspread from oauth2client.service_account import ServiceAccountCredentials import datetime import json class GSP_OPE(): def __init__(self,\ JsonPath = '/storage/emulated/0/Documents/python/project1/gsp_kakeibo.json',\ SheetName = 'kakeibo',\ DataLabel = ['購入日', '購入店', '商品名', 'カテゴリ', '重要度', '支払い方法', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', '登録日'],\ LastUpdateLogPath = '/storage/emulated/0/Documents/python/project1/lastlog.txt',\ SPREADSHEET_KEY = '10aZg3j9CaW_tLHEe3CsvJ8u64LXHDx6RRQ6z4aZWO9Y'): scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name(JsonPath, scope) gc = gspread.authorize(credentials) self.wks = gc.open_by_key(SPREADSHEET_KEY) #gc = gspread.service_account(filename = JsonPath) #self.wks = gc.open(SheetName) self.actv_sht = self.wks.worksheet('data') self.wks_data = self.wks.worksheet('data') self.wks_ddl = self.wks.worksheet('dropdownlist') self.logfile = LastUpdateLogPath self.data_lb = DataLabel def target_sheet(self, target_sheet_str = None, target_sheet_num = None): if target_sheet == None and target_sheet_num == None: return 'error 0000' if target_sheet_str != None and target_sheet_num == None: try: self.actv_sht = self.wks.worksheet(target_sheet_str) return 'change' except: return 'error0002' if target_sheet_str == None and target_sheet_num != None: try: self.actv_sht = self.wks.get_worksheet(target_sheet_str) return 'change' except: return 'error 0004' if target_sheet_str != None and target_sheet_num != None: return 'error 0006' def serch_row(self, target_word = None, target_column = None, serch_last_row_flag = False, last_update_row = 1): #return the number of row if target_word == None and target_column == None and serch_last_row_flag == False: return 'error 0020' elif target_word != None and target_column == None: return 'error 0040' elif target_word != True and serch_last_row_flag == True: return 'error 0060' """ unimprement if target_word <> None and target_column == None: #WS.cell(row, col).value row,col >= 1 row = 1 break_flag = False while(1): max_col = self.serch_column(target_row = row, last_update_column = True) if mac_col == 0: return [row, 0] for col in range(1, max_col + 1): if self.actv_sht.cell(row, col).value == target_word: return [row, col] row = row + 1 """ row = last_update_row if target_word != None and target_column != None: while(1): if self.actv_sht.cell(row, target_column).value == target_word: return [row, target_column] if self.actv_sht.cell(row, target_column).value == None: return [0, 0] row = row + 1 if serch_last_row_flag == True and target_column != None: while(2): if self.actv_sht.cell(row, target_column).value == None: return [row, target_column] row = row + 1 if serch_last_row_flag == True: while(3): if self.actv_sht.cell(row, len(self.data_lb)).value == None: return [row, 0] row = row + 1 return 'error 0080' def serch_column(self, target_word = None, target_row = None, serch_last_column_flag = False, last_update_column = 1): #return the number of row if target_word == None and target_row == None and serch_last_column_flag == False: return 'error 0200' elif target_word != None and target_row == None: return 'error 0400' elif target_word != True and serch_last_column_flag == True: return 'error 0600' col = last_update_column if target_word != None and target_row != None: while(1): if self.actv_sht.cell(target_row, col).value == target_word: return [target_row, col] if self.actv_sht.cell(target_row, col).value == None: return [0, 0] col = col + 1 if serch_last_column_flag == True and target_row != None: while(2): if self.actv_sht.cell(target_row, col).value == None: return [target_row, col] col = col + 1 return 'error 0800' def write_data(self, row = 1, column = 1, data = "write"): self.actv_sht.update_cell(row, column, data) def read_data(self, row = 1, column = 1, data = "write"): return self.actv_sht.cell(target_row, col).value def write_array(self, row = 1, column = 1, arr = [['w','r'],['i','g'],['t','!']]): for n in range(len(arr)): #row for m in range(len(arr[n])): #column self.actv_sht.update_cell(row + n, column + m, arr[n][m]) def read_array(self, row1 = 1, row2 = 5, column1 = 1, column2 = 5): buf = [[]] for n in range(row1, row2 + 1): buf2 = [] for m in range(column1, column2 + 1): buf2.append(self.actv_sht.cell(n, m).value) buf.append(buf2) buf.pop(0) return buf def save_log(self, arr): with open(self.logfile, "a") as f: writer = f.write(datetime.datetime.now(JST).strftime('%Y/%m/%d') + '::' + str(arr) + '\n') def serch_word(self, serch_word = '1'): return self.actv_sht.find(serch_word) def get_all_val_row(self, row= 1): return self.actv_sht.row_values(row) def get_all_val_col(self, col= 1): return self.actv_sht.col_values(col) def main(): gsp = GSP_OPE() gsp.write_data(row = 2, column = 1, data = "write") print('ok') gsp.write_array(row = 2, column = 2, arr = [['w','r'],['i','g'],['t','!']]) print('ok2') print(gsp.read_array(row1 = 2, row2 = 6, column1 = 1, column2 = 6)) if __name__ == "__main__": main()
