質問編集履歴
1
バージョンとコードを追記いたします。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -11,4 +11,47 @@
|
|
|
11
11
|
|
|
12
12
|
解決策をご存知の方いらっしゃいましたらご教示お願いいたします。
|
|
13
13
|
|
|
14
|
+
バージョンとコードを追記いたします。
|
|
15
|
+
macOS Moneterey 12.3.1
|
|
16
|
+
Python 3.6.0
|
|
17
|
+
gspread 3.6.0
|
|
14
18
|
|
|
19
|
+
```Python
|
|
20
|
+
import gspread
|
|
21
|
+
import json
|
|
22
|
+
from oauth2client.service_account import ServiceAccountCredentials
|
|
23
|
+
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
|
|
24
|
+
credentials = ServiceAccountCredentials.from_json_keyfile_name('/Users/xxx/xxx', scope)
|
|
25
|
+
gc = gspread.authorize(credentials)
|
|
26
|
+
SPREADSHEET_KEY = 'xxx'
|
|
27
|
+
worksheet = gc.open_by_key(SPREADSHEET_KEY).worksheet('xxx')
|
|
28
|
+
from gspread_formatting import *
|
|
29
|
+
|
|
30
|
+
values_list = worksheet.get_all_values()
|
|
31
|
+
lastrow = int(worksheet.cell(1, 1).value)
|
|
32
|
+
copy_row = worksheet.row_values(lastrow-1, value_render_option="FORMULA")
|
|
33
|
+
worksheet.insert_row(copy_row, lastrow, value_input_option='USER_ENTERED')
|
|
34
|
+
# worksheet.update_cell(lastrow, 4, '=substitute(CLEAN(asc(C'+str(lastrow)+')),",","")')
|
|
35
|
+
# worksheet.update_cell(lastrow, 5, '=iferror(mid(D'+str(lastrow)+', max(iferror(ARRAYFORMULA(find(DB!$M$2:$M$124,D'+str(lastrow)+',1)))),4),"")')
|
|
36
|
+
# worksheet.update_cell(lastrow, 6, '=substitute(D'+str(lastrow)+',E'+str(lastrow)+',"")')
|
|
37
|
+
# worksheet.update_cell(lastrow, 7, '=iferror(mid(F'+str(lastrow)+', max(iferror(ARRAYFORMULA(find(DB!$S$2:$S$52,F'+str(lastrow)+',1)))),4),"")')
|
|
38
|
+
# worksheet.update_cell(lastrow, 8, '=substitute(F'+str(lastrow)+',G'+str(lastrow)+',"")')
|
|
39
|
+
# worksheet.update_cell(lastrow, 9, '=iferror(mid(H'+str(lastrow)+', max(iferror(ARRAYFORMULA(find(DB!$Y$2:$Y$52,H'+str(lastrow)+',1)))),5),"")')
|
|
40
|
+
# worksheet.update_cell(lastrow, 10, '=substitute(H'+str(lastrow)+',I'+str(lastrow)+',"")')
|
|
41
|
+
# worksheet.update_cell(lastrow, 11, '=iferror(mid(J'+str(lastrow)+', MIN(iferror(ARRAYFORMULA(find(DB!$AD$2:$AD$1000,J'+str(lastrow)+',1)))),5),"")')
|
|
42
|
+
# worksheet.update_cell(lastrow, 12, '=substitute(J'+str(lastrow)+',K'+str(lastrow)+',"")')
|
|
43
|
+
# worksheet.update_cell(lastrow, 13, '=min(iferror(arrayformula(find(DB!$AI$2:$AI$2000,lower(asc($L'+str(lastrow)+')),1))))')
|
|
44
|
+
# worksheet.update_cell(lastrow, 14, '=iferror(mid(L'+str(lastrow)+',M'+str(lastrow)+',7),)')
|
|
45
|
+
# worksheet.update_cell(lastrow, 15, '=max(iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',0,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',0,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',1,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',1,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',2,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',2,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',3,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',3,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',4,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',4,"")))),0),,iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',5,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',5,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',6,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',6,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',7,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',7,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',8,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',8,"")))),0),iferror(FIND("★",SUBSTITUTE(N'+str(lastrow)+',9,"★",LEN(N'+str(lastrow)+')-LEN(SUBSTITUTE(N'+str(lastrow)+',9,"")))),0))')
|
|
46
|
+
# worksheet.update_cell(lastrow, 16, '=if(N'+str(lastrow)+'="","",mid(N'+str(lastrow)+',MAX(iferror(arrayformula(find(DB!$AI$2:$AI$2000,lower(asc(N'+str(lastrow)+')))))),O'+str(lastrow)+'))')
|
|
47
|
+
# worksheet.update_cell(lastrow, 17, '=substitute(L'+str(lastrow)+',P'+str(lastrow)+',"")')
|
|
48
|
+
# worksheet.update_cell(lastrow, 18, '=min(iferror(arrayformula(find(DB!$AS$2:$AS$28,lower(asc($Q'+str(lastrow)+')),1))))')
|
|
49
|
+
# worksheet.update_cell(lastrow, 19, '=iferror(mid(Q'+str(lastrow)+',R'+str(lastrow)+'-5,10),0)')
|
|
50
|
+
# worksheet.update_cell(lastrow, 20, '=if(iferror(find(".",S'+str(lastrow)+',1),0)=0, mid(S'+str(lastrow)+',MAX(iferror(arrayformula(find(DB!$AS$2:$AS$28,lower(asc(S'+str(lastrow)+'))))))-1,3),iferror(mid(S'+str(lastrow)+',find(".",S'+str(lastrow)+',1)-2,MAX(iferror(arrayformula(find(DB!$AS$2:$AS$28,lower(asc(S'+str(lastrow)+')),1))))+2-(find(".",S'+str(lastrow)+',1)-2)),""))')
|
|
51
|
+
# worksheet.update_cell(lastrow, 21, '=if(iferror(find(".",S'+str(lastrow)+',1),0)=0, mid(S'+str(lastrow)+',MAX(iferror(arrayformula(find(DB!$AS$2:$AS$28,lower(asc(S'+str(lastrow)+'))))))-1,2),iferror(mid(S'+str(lastrow)+',find(".",S'+str(lastrow)+',1)-2,MAX(iferror(arrayformula(find(DB!$AS$2:$AS$28,lower(asc(S'+str(lastrow)+')),1))))+1-(find(".",S'+str(lastrow)+',1)-2)),"不明"))')
|
|
52
|
+
# worksheet.update_cell(lastrow, 125, '=if($DL'+str(lastrow)+'=0,"不明", iferror(hlookup(value(mid($DL'+str(lastrow)+',len($DL'+str(lastrow)+')-5,3)),$AQ$2:$DK$17,16,0),""))')
|
|
53
|
+
# worksheet.update_cell(lastrow, 240, '=if($HX'+str(lastrow)+'=0,"不明", iferror(hlookup(value(mid($HX'+str(lastrow)+',len($HX'+str(lastrow)+')-3,4)),$EA$2:$HW$18,17,0)))')
|
|
54
|
+
# worksheet.update_cell(lastrow, 274, '=if(JM'+str(lastrow)+'=0,"不明", iferror(hlookup(value(mid(JM'+str(lastrow)+',len(JM'+str(lastrow)+')-3,4)),$IH$16:$JL$18,3,0)))')
|
|
55
|
+
# worksheet.update_cell(lastrow, 275, '=if(COUNTIF($D'+str(lastrow)+',"*" & $JO$14 & "*")+COUNTIF($D'+str(lastrow)+',"*" & $JO$15 & "*")+COUNTIF($D'+str(lastrow)+',"*" & $JO$16 & "*")>0,"Future",if(COUNTIF($D'+str(lastrow)+',"*" & $JO$6 & "*")>0,"FCS2", if(COUNTIF($D'+str(lastrow)+',"*" & $JO$7 & "*")>0,"FCS",if(COUNTIF($D'+str(lastrow)+',"*" & $JO$8 & "*")>0,"FCS2",if(COUNTIF($D'+str(lastrow)+',"*" & $JO$9 & "*")>0,"FCS1","不明")))))')
|
|
56
|
+
# worksheet.update_cell(lastrow, 276, '=if(COUNTIF($D'+str(lastrow)+',"*" & $JP$15 & "*")>0,"EPS",if(COUNTIF($D'+str(lastrow)+',"*" & $JP$16 & "*")>0,"PU",if(COUNTIF($D'+str(lastrow)+',"*" & $JP$14 & "*")>0,"スポンジ","不明")))')
|
|
57
|
+
```
|