python csv spreadsheetの連携について

Question

前提・実現したいこと

実現したいこと
・csvファイルをスプレッドシートへシート名とセルの位置を指定して貼り付けたいです。

前提
・既存のスプレッドシートを使用
・参考サイトはhttps://qiita.com/d_m/items/29392a491643d374cde3です。

発生している問題・エラーメッセージ

File "C:\Users\admin\AppData\Python38\lib\site-packages\googleapiclient\http.py", line 907, in execute      
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 404 when requesting https://www.googleapis.com/drive/v2/files?q=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx=json returned "File not found:">

該当のソースコード

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import csv


scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

json_keyfile_path = 'C:\Users\admin\desktop\xxxxxx.json'

credentials = ServiceAccountCredentials.from_json_keyfile_name(json_keyfile_path, scope)

gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)

csv_path = 'C:\Users\admin\Downloads\xxxx.csv'

folder_id = 'xxxxxxxxxxxxxxxxxxxx'
file_list = drive.ListFile({'q': "'%s' in parents and trashed=false" % folder_id}).GetList()

gc = gspread.authorize(credentials)
book_name = 'サンプル'
sheet_id = [file['xxxxxxxxxxxxxxxxxxxxxxxxxxxxx'] for file in file_list if file['サンプル'] == book_name]
sheet_id = sheet_id[0]
workbook = gc.open_by_key(sheet_id)

workbook.values_update(
    sheet_name,
    params={'valueInputOption': 'USER_ENTERED'},
    body={'values': list(csv.reader(open(csv_path, encoding='utf_8_sig')))}
)

補足情報（FW/ツールのバージョンなど）

下記は使用ライブライ等になります。
appdirs 1.4.4
asgiref 3.3.0
astroid 2.4.2
cachetools 4.1.1
certifi 2020.6.20
chardet 3.0.4
colorama 0.4.4
comtypes 1.1.7
distlib 0.3.1
Django 3.1.2
filelock 3.0.12
google-api-core 1.22.4
google-api-python-client 1.12.3
google-auth 1.22.1
google-auth-httplib2 0.0.4
google-auth-oauthlib 0.4.1
googleapis-common-protos 1.52.0
gspread 3.6.0
httplib2 0.18.1
idna 2.10
inv 0.1.0
isort 5.6.4
lazy-object-proxy 1.5.1
mccabe 0.6.1
numpy 1.19.2
oauth2client 4.1.3
oauthlib 3.1.0
pandas 1.1.3
Pillow 8.0.0
pip 20.2.4
pipenv 2020.8.13
protobuf 3.13.0
pyasn1 0.4.8
pyasn1-modules 0.2.8
PyDrive 1.3.1
pylint 2.6.0
python-dateutil 2.8.1
pytz 2020.1
pywin32 228
PyYAML 5.3.1
requests 2.24.0
requests-oauthlib 1.3.0
rsa 4.6
setuptools 50.3.2
six 1.15.0
sqlparse 0.4.1
toml 0.10.1
uritemplate 3.0.1
urllib3 1.25.10
virtualenv 20.0.35
virtualenv-clone 0.5.4
wrapt 1.12.1
xlwings 0.20.8

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。

[Orlofsky]

コードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

Answer 1

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import get_as_dataframe, set_with_dataframe
import pandas as pd


scope = ['https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('jsonファイルパス', scope)
gc = gspread.authorize(credentials)
SPREADSHEET_KEY = 'スプレッドシートキー'
# スプレッドシートの指定
wks = gc.open_by_key(SPREADSHEET_KEY).sheet1
# csvをデータフレームとして読み込む
df = pd.read_csv('参照したいcsvパス')
# データフレームをスプレッドシートに書き込み
set_with_dataframe(wks, df, include_column_header=False)

追記

今回使用されるCSVのデータがどの様なデータなのかわからなかった為
恐らくは3列100行分のデータであると仮定して仮のデータを作成して掲示しておりますが
CSVファイルを使用する場合には、以下の形にご自身で整形して頂く必要があります。
[[1列目1行目, 2列目1行目, 3列目1行目], [1列目2行目, 2列目2行目, 3列目2行目], [..], ...]

そしたらfor文で1行ずつ処理を行っていきます。
何行目から開始し、現在何行目かをカウントする為の変数を用意します。(以下ではcolumと定義)
1行ずつ処理を行っていき、指定のセルにデータを1件ずつ挿入していきます。
また、API側でリクエストを送る(データの挿入等の事)回数に上限が設定されています。

• 100秒あたりに100件まで
• 1回の実行で可能な最大値は1000件まで
• 1秒10件まで

従って、上限に達しない様に実行速度を調整してあげながら追加する必要があります。
今回300件のデータがある為、300秒以上の時間をかけて追加する必要があります。
従って3件(1行分)追加したらtime.sleep(3)で3秒休み...という流れで100行分のデータを処理していきます。

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import get_as_dataframe, set_with_dataframe
import pandas as pd
import numpy as np
import time

scope = ['https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('jsonファイル名', scope)
gc = gspread.authorize(credentials)
SPREADSHEET_KEY = 'スプレッドシートキー'
# スプレッドシートの指定
wks = gc.open_by_key(SPREADSHEET_KEY).sheet1

# 3列100行分の仮のデータ
data = [['{}{:05d}'.format(n, i) for i in range(1, 101)] for n in range(1,4)]
data = np.array(data).T	# 転置

colum = 5	# 5行目から開始する為
# 各行ずつB列C列D列の何行目かをformat(colum)で指定してデータを挿入
for row in data:
	B, C, D = row
	wks.update_acell('B{}'.format(colum), B)
	wks.update_acell('C{}'.format(colum), C)
	wks.update_acell('D{}'.format(colum), D)

	colum += 1
	time.sleep(3)

※解説としてわかりやすい様に余計な代入や省略できる部分を省略せずに記載しております。

Comments

[退会済みユーザー]

レスポンスが遅れて申し訳ございません。
回答頂いたコードでほぼ問題なく作動いたしました。
ありがとうございます。

セルの位置を指定しようとするとどういったコードにすべきでしょうか。
ご教授お願い致します。

[nto]

ワークシートを指定して(私の回答例の場合ではwks)
wks.update_acell('A1', '更新したい値') # 直接セル番号を文字列で指定
または
wks.update_cell(1, 1, '更新したい値') #行と列番号を指定(左記では1行目1列目)
などでセルを指定してコードを更新する事が可能です。

[退会済みユーザー]

ご返信ありがとうございます。

"更新したい値"ということは空白セルを指定することはできないのでしょうか。

幾分プログラミング初心者でして…
初歩的な質問で申し訳ないです。

[nto]

たとえば’B5’のセルに[あいうえお]と入力したい場合には
wks.update_acell('B5', 'あいうえお')
または
wks.update_cell(5, 2, 'あいうえお')
と実行する事で、空白セルまたは元々違う値が入っていた場合でも上書きされ値が更新されます。

[退会済みユーザー]

ご返信ありがとうございます。

となると、csvのA1からC100のセルをスプレッドシートのB5からD105に一気にコピーするのは難しくて
セル一つ一つを指定しながらコピーをしていく、という形になるのでしょうか。

質問ばかりしてすいません…

[nto]

前提としては、予めスプレッドシート上のA1:D1,A5:A100までの範囲には何か既に入力された項目が有るという事でしょうか。

いずれも手法は2つあります。
1つ目はそもそものデータの追加の仕方を変える
2つ目はデータ追加後に、スプレッドシートに必要数の行列の追加を行い整形する
恐らくは現状のコードからの修正であれば2つ目の方が早いかとは思います。

[退会済みユーザー]

ご返信ありがとうございます。

スプレッドシートに関してですが、今回指定したいセル範囲は何も入力されておりません。
但し、その他まわりのセルには何かしらが入力されているセルが散らばっている状態になります。

ご提示頂いた2つ目の方法となりますと上記のまわりのセルには何らかの影響が出ますよねぇ。。。

[nto]

それでは1つ目のデータの追加の仕方を変えてしまったほうが良さそうですね。
B5:D105の範囲への書き込みという事であれば、データは3列100行という認識であっていますか？

その場合であれば昨日の回答に追記を致しましたのでお確かめください。

[退会済みユーザー]

返信ありがとうございます。

追記して頂いた内容、大変お役にたちました。
一度私なりに噛み砕いて理解しようとしたのですが、for row in date:の箇所でエラー「Non-iterable value data is used in an iterating context」がでてしまいました。

上記のエラーが調べても、いまいち理解できずにいまして…。
ご迷惑でなければ、解説して頂きたく思います。

宜しくお願い致します。

[nto]

エラーの解説というよりかは、その前にCSVデータはどの様に整形されましたか？
また一度目的の形に整形が出来ているのかprint(data)として出力して確認してみましょう。

[退会済みユーザー]

返事が遅くなって申し訳ございません。

整形に関しましては
date = pd.read_csv("hoge.csv",header=None).values.tolist()
でprintで出力したところ、整形できておりましたので、こちらのやり方でコードを書きました。

ただ、やはりNon-iterable value data is used in an iterating contextのエラーが
出てしまいます。。。

[nto]

宜しければ修正したコードをご提示頂けますか？

[退会済みユーザー]

こちらに直接提示いたします。

```python
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import get_as_dataframe, set_with_dataframe
import pandas as pd
import numpy as np
import time
import json

date = pd.read_csv("hoge.csv",header=None).values.tolist()

scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('xxxxxxxxxxxxxxxxxxxxxxxxxxxx.json', scope)
gc = gspread.authorize(credentials)
SPREADSHEET_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
# スプレッドシートの指定
wks = gc.open_by_key(SPREADSHEET_KEY).sheet1

# 3列100行分の仮のデータ
data = [['{}{:05d}'.format(n, i) for i in range(1, 101)] for n in range(1,4)]
data = np.array(data).T # 転置

colum = 5 # 5行目から開始する為
# 各行ずつB列C列D列の何行目かをformat(colum)で指定してデータを挿入
for row in data:
B, C, D = row
wks.update_acell('B{}'.format(colum), B)
wks.update_acell('C{}'.format(colum), C)
wks.update_acell('D{}'.format(colum), D)

colum += 1
time.sleep(3)
```

宜しくお願い致します。

[nto]

まず、20～21行目のdata変数の部分については、どういったデータを用意しているのかがわからなかった為こちらで用意した仮のデータの為不要です。
そしてfor文では、用意した自前のCSVデータ(9行目のdate変数)を使用してあげましょう。
for B, C, D in date:
wks.update_acell('B{}'.format(colum), B)
wks.update_acell('C{}'.format(colum), C)
wks.update_acell('D{}'.format(colum), D)

colum += 1
time.sleep(3)

また、コード自体は特に問題がなさそうに見えるのですが
となると、CSVデータの方が少し気になります。

[退会済みユーザー]

返信遅くなり申し訳ありません。

CSVの方ですが、確認したところA1からAM206までの膨大なデータでした。。。

[nto]

A1:AM206という範囲という事であればデータ量としては8151個という事でしょうかね？
元々その量のデータをどの様にしてB5:D105という300個分の範囲に入れようとしていたのでしょうか。

[退会済みユーザー]

返信ありがとうございます。

B5:D105というのは練習のつもりでした。

[nto]

であれば、練習用としてCSVのデータも300件分として同じ形に整えましょう。

[退会済みユーザー]

返信ありがとうございます。

無事完了できました。
拙い質問等にも紳士にお答えいただき大変感謝しております。

本当にありがとうございました。

[nto]

とんでもないです。
今回はその練習用という事で、3列100行分のデータ用の処理にしていた為、そのフォーマットに沿わないデータを使用した為エラーが生じていたという事でしたが、
(AMまで)39列206行文の処理をfor文の中で用意してあげる必要があります。
言葉にすると難しそうに聞こえてしまいますが、実際にはそこまで難しい処理ではない為、まずはコードの書き方や使用方法にもう少し慣れてきたら実践してみましょう。