Q&A
・現時点のコード（1件ずつ貼り付け？）を、省略せずすべて記載してください。スプレッドシートIDなど隠ぺいする必要があるものはダミーとわかるものに置き換えて構いません。
・実行環境・OSと gspread , python のバージョンを記載してください。たとえばJupyter NotebookやGoogle collaborate 等での実行と、ローカルにインストールした python 下で実行するのとでは、挙動や実現性が異なる場合があります。
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という計算式が入っていて、２行目のコードで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件
0
ベストアンサー
batch_update を使うと高速化できます。
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate
たとえば下記は、セルA1に入力されている数字を「lastrow」とした場合に、[lastrow - 1]行目の4列目～21列目、125列目、240列目、274列目～276列目の数式をコピーし、
lastrow～[lastrow+99]行目までの100行にペーストするものです。
※ batch_update 含め、gspread 全般は実行すると内容を元に戻せなくなります。したがって、意図しない挙動で数式を含めたシートの内容を壊したくない場合は、実行の都度オリジナルのスプレッドシートをコピーしてテストしてください。
python
1import gspread 2import json 3from oauth2client.service_account import ServiceAccountCredentials 4from gspread_formatting import * 5 6def batch_copy_paste(spreadsheet, worksheet, 7 source_start_row, # コピー元開始行 8 source_end_row, # コピー元終了行 9 source_start_column, # コピー元開始列 10 source_end_column, # コピー元終了列 11 dest_start_row, # ペースト先開始行 12 dest_end_row, # ペースト先終了行 13 dest_start_column, # ペースト先開始列 14 dest_end_column): # ペースト先終了列 15 16 sheetId = worksheet._properties['sheetId'] 17 body = { 18 "requests": [ 19 { 20 "copyPaste": { 21 "source": { 22 "sheetId": sheetId, 23 "startRowIndex": source_start_row - 1, 24 "endRowIndex": source_end_row, 25 "startColumnIndex": source_start_column - 1, 26 "endColumnIndex": source_end_column 27 }, 28 "destination": { 29 "sheetId": sheetId, 30 "startRowIndex": dest_start_row - 1, 31 "endRowIndex": dest_end_row, 32 "startColumnIndex": dest_start_column - 1, 33 "endColumnIndex": dest_end_column 34 }, 35 "pasteType": "PASTE_NORMAL" 36 } 37 } 38 ] 39 } 40 spreadsheet.batch_update(body) 41 42 43scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] 44credentials = ServiceAccountCredentials.from_json_keyfile_name('/Users/xxx/xxx', scope) 45gc = gspread.authorize(credentials) 46SPREADSHEET_KEY = 'xxx' 47worksheet = gc.open_by_key(SPREADSHEET_KEY).worksheet('xxx') 48 49 50batch_copy_paste(ss, worksheet, lastrow-1, lastrow-1, 4, 21, lastrow, lastrow + 99, 4, 21) 51batch_copy_paste(ss, worksheet, lastrow-1, lastrow-1, 125, 125, lastrow, lastrow + 99, 125, 125) 52batch_copy_paste(ss, worksheet, lastrow-1, lastrow-1, 240, 240, lastrow, lastrow + 99, 240, 240) 53batch_copy_paste(ss, worksheet, lastrow-1, lastrow-1, 274, 276, lastrow, lastrow + 99, 274, 276) 54 55
投稿2022/10/24 12:34
編集2022/10/24 13:07退会済みユーザー
総合スコア0
ご回答ありがとうございます。
試してみたいと思います。
一点質問させていただきますが、どのようにして回答までたどり着いたのでしょうか？もしよろしければ、調べ方を教えていただけないでしょうか？
「gspread python copy formula」等のキーワードで検索しました。
それで topにあったページを参考にしました。
ありがとうございました。
大変勉強になりました（m_ _m）
あなたの回答
tips
プレビュー
