質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.31%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

Q&A

解決済

1回答

1226閲覧

pythonでスプレッドシートの関数を相関参照で列ごとにコピペする方法を探しています

fuku-chann

総合スコア82

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

0グッド

0クリップ

投稿2022/10/23 09:58

編集2022/10/23 11:26

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,"スポンジ","不明")))')

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2022/10/23 10:44

・現時点のコード(1件ずつ貼り付け?)を、省略せずすべて記載してください。スプレッドシートIDなど隠ぺいする必要があるものはダミーとわかるものに置き換えて構いません。 ・実行環境・OSと gspread , python のバージョンを記載してください。たとえばJupyter NotebookやGoogle collaborate 等での実行と、ローカルにインストールした python 下で実行するのとでは、挙動や実現性が異なる場合があります。
fuku-chann

2022/10/23 11:27

バージョンとコードを追記いたしました。 お手隙の際にご確認お願いいたします。
guest

回答1

0

ベストアンサー

batch_update を使うと高速化できます。
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

https://docs.gspread.org/en/v5.6.1/api/models/spreadsheet.html?highlight=batch#gspread.spreadsheet.Spreadsheet.batch_update

たとえば下記は、セル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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

fuku-chann

2022/10/27 22:16

ご回答ありがとうございます。 試してみたいと思います。 一点質問させていただきますが、どのようにして回答までたどり着いたのでしょうか?もしよろしければ、調べ方を教えていただけないでしょうか?
退会済みユーザー

退会済みユーザー

2022/10/31 10:41 編集

「gspread python copy formula」等のキーワードで検索しました。 それで topにあったページを参考にしました。
fuku-chann

2022/11/03 12:17

ありがとうございました。 大変勉強になりました(m_ _m)
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.31%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問