
Pythonを使ってGoogleスプレッドシートにある関数を一列まるごと一つ下の行にコピペしたいのですが、関数が相対参照にならなくて困っています。関数は多くのセルに入っていて、一つずつ貼り付けを行うとすぐにgspreadの読み書きの上限に到達してしまいますので、それ以外の方法で検討しています。(gspread.exceptions.APIError: {'code': 400, 'message': "Invalid value at 'data.values[0]')
下記に簡単なテストコードを示しますので、まず再現性があるかご確認いただけましたら幸いです。
Python
1copy_row = worksheet.row_values(90, value_render_option="FORMULA") 2worksheet.insert_row(copy_row, 91, value_input_option='USER_ENTERED')
スプレッドシートのC90セルに=B90+1という計算式が入っていて、2行目のコードでC91にinsertすると、同じ=B90+1という計算式が入ってしまいます。C91に入る計算式を=B91+1にしたいです。
解決策をご存知の方いらっしゃいましたらご教示お願いいたします。
バージョンとコードを追記いたします。
macOS Moneterey 12.3.1
Python 3.6.0
gspread 3.6.0
Python
1import gspread 2import json 3from oauth2client.service_account import ServiceAccountCredentials 4scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] 5credentials = ServiceAccountCredentials.from_json_keyfile_name('/Users/xxx/xxx', scope) 6gc = gspread.authorize(credentials) 7SPREADSHEET_KEY = 'xxx' 8worksheet = gc.open_by_key(SPREADSHEET_KEY).worksheet('xxx') 9from gspread_formatting import * 10 11values_list = worksheet.get_all_values() 12lastrow = int(worksheet.cell(1, 1).value) 13copy_row = worksheet.row_values(lastrow-1, value_render_option="FORMULA") 14worksheet.insert_row(copy_row, lastrow, value_input_option='USER_ENTERED') 15# worksheet.update_cell(lastrow, 4, '=substitute(CLEAN(asc(C'+str(lastrow)+')),",","")') 16# worksheet.update_cell(lastrow, 5, '=iferror(mid(D'+str(lastrow)+', max(iferror(ARRAYFORMULA(find(DB!$M$2:$M$124,D'+str(lastrow)+',1)))),4),"")') 17# worksheet.update_cell(lastrow, 6, '=substitute(D'+str(lastrow)+',E'+str(lastrow)+',"")') 18# worksheet.update_cell(lastrow, 7, '=iferror(mid(F'+str(lastrow)+', max(iferror(ARRAYFORMULA(find(DB!$S$2:$S$52,F'+str(lastrow)+',1)))),4),"")') 19# worksheet.update_cell(lastrow, 8, '=substitute(F'+str(lastrow)+',G'+str(lastrow)+',"")') 20# worksheet.update_cell(lastrow, 9, '=iferror(mid(H'+str(lastrow)+', max(iferror(ARRAYFORMULA(find(DB!$Y$2:$Y$52,H'+str(lastrow)+',1)))),5),"")') 21# worksheet.update_cell(lastrow, 10, '=substitute(H'+str(lastrow)+',I'+str(lastrow)+',"")') 22# worksheet.update_cell(lastrow, 11, '=iferror(mid(J'+str(lastrow)+', MIN(iferror(ARRAYFORMULA(find(DB!$AD$2:$AD$1000,J'+str(lastrow)+',1)))),5),"")') 23# worksheet.update_cell(lastrow, 12, '=substitute(J'+str(lastrow)+',K'+str(lastrow)+',"")') 24# worksheet.update_cell(lastrow, 13, '=min(iferror(arrayformula(find(DB!$AI$2:$AI$2000,lower(asc($L'+str(lastrow)+')),1))))') 25# worksheet.update_cell(lastrow, 14, '=iferror(mid(L'+str(lastrow)+',M'+str(lastrow)+',7),)') 26# 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))') 27# 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)+'))') 28# worksheet.update_cell(lastrow, 17, '=substitute(L'+str(lastrow)+',P'+str(lastrow)+',"")') 29# worksheet.update_cell(lastrow, 18, '=min(iferror(arrayformula(find(DB!$AS$2:$AS$28,lower(asc($Q'+str(lastrow)+')),1))))') 30# worksheet.update_cell(lastrow, 19, '=iferror(mid(Q'+str(lastrow)+',R'+str(lastrow)+'-5,10),0)') 31# 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)),""))') 32# 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)),"不明"))') 33# 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),""))') 34# 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)))') 35# 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)))') 36# 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","不明")))))') 37# 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,"スポンジ","不明")))')
回答1件
あなたの回答
tips
プレビュー