-
Notifications
You must be signed in to change notification settings - Fork 0
/
Spreadsheet.py
128 lines (86 loc) · 3.46 KB
/
Spreadsheet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
import gspread
from google.oauth2.service_account import Credentials
import wrapt_timeout_decorator
# @wrapt_timeout_decorator.timeout(dec_timeout=10)
def init(key):
global workbook
# お決まりの文句
# 2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
scope = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive']
#ダウンロードしたjsonファイル名をクレデンシャル変数に設定。
credentials = Credentials.from_service_account_file("data.json", scopes=scope)
#OAuth2の資格情報を使用してGoogle APIにログイン。
gc = gspread.authorize(credentials)
#スプレッドシートIDを変数に格納する。
KEY = key
workbook = gc.open_by_key(KEY)
print(workbook)
return workbook
def get_interval_time():
worksheet = workbook.worksheet('設定')
tmp1 = worksheet.find("インターバル時間[分]")
return worksheet.cell((tmp1.row+1), tmp1.col).value
def get_notice_time():
worksheet = workbook.worksheet('設定')
tmp1 = worksheet.find("通知時間")
length = len(worksheet.col_values(tmp1.col)) - 1
value = [[] for i in range(2)]
for i in range(length+1):
if i != 0:
tmp = worksheet.cell(tmp1.row + i, tmp1.col).value
value[0].append(tmp)
value[1].append("False")
return value
def get_slack_id(id):
# シートを開く
worksheet = workbook.worksheet('Slack')
tmp1 = worksheet.find(str(id))
tmp2 = worksheet.find("Slack_ID")
if(str(tmp1).startswith('None')): return "None"
# print(tmp1)
# print(tmp2)
# print(str(tmp1.row) + " " + str(tmp2.col))
return worksheet.cell(tmp1.row, tmp2.col).value
def get_max_id():
worksheet = workbook.worksheet('ユーザー情報')
tmp1 = worksheet.find("ユーザーID")
data = worksheet.col_values(tmp1.col)
data.pop(0)
data = [int(i) for i in data]
max_id = max(data)
return max_id
def get_name(id):
worksheet = workbook.worksheet('ユーザー情報')
tmp1 = worksheet.find(str(id))
tmp2 = worksheet.find("氏名")
return worksheet.cell(tmp1.row, tmp2.col).value
def get_tool_name(id):
worksheet = workbook.worksheet('工具情報')
tmp1 = worksheet.find(str(id+1))
tmp2 = worksheet.find("工具名")
return worksheet.cell(tmp1.row, tmp2.col).value
def write_return(user_id, tool_id, time):
worksheet = workbook.worksheet('貸し出し情報')
tmp1 = worksheet.find("工具ID")
write_row = (len(worksheet.col_values(tmp1.col)) + 1)
# print("return_write_row" + str(write_row))
worksheet.update_cell(write_row, 1, str(user_id))
worksheet.update_cell(write_row, 2, str(tool_id+1))
worksheet.update_cell(write_row, 3, str(time))
worksheet.update_cell(write_row, 4, str(0))
def write_lend(user_id, tool_id, time):
worksheet = workbook.worksheet('貸し出し情報')
tmp1 = worksheet.find("工具ID")
write_row = (len(worksheet.col_values(tmp1.col)) + 1)
# print("lend_write_row" + str(write_row))
worksheet.update_cell(write_row, 1, str(user_id))
worksheet.update_cell(write_row, 2, str(tool_id+1))
worksheet.update_cell(write_row, 3, str(time))
worksheet.update_cell(write_row, 4, str(1))
def get_lend_info():
worksheet = workbook.worksheet('貸し出し情報')
tmp = worksheet.find("工具ID")
id_data = worksheet.col_values(tmp.col)
tmp = worksheet.find("貸出フラグ")
flag_data = worksheet.col_values(tmp.col)
return id_data, flag_data