pandas, gspreadについて

Question

実現したいこと

スプレッドシートのデータをexcelに貼りつけたい

初め、スプレッドシートをdataframeに格納してからエクセルに貼り付けのコードを書いてたのですがget_as_dataframeは取得してくる範囲設定が必須と書いてあり
私の場合データが増えていくので範囲指定ができず、シートごと取得する方法を探してのですがgspreadのcopyは新しいシートにコピーまたduplicate_sheet（）もスプレッド自体にしかできなさそうで解決策ご存じの方ご教授お願いします。

スプレッドシートをdataframeに格納してからエクセルに貼り付けのコード

import os
import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import json
import win32com.client
import openpyxl as px
import xlsxwriter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.utils import get_column_letter
import clipboard as clp
import datetime
now = datetime.datetime.now()
path1 = "xxxxx.xlsm"
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']


credentials = ServiceAccountCredentials.from_json_keyfile_name('XXXXXXXXXXXX', scope)
gc = gspread.authorize(credentials)
#エクセルファイル読み込み
wb = px.load_workbook(filename=path1, keep_vba=True)
ws = wb['シート名']

wks = gc.open('xxxx').worksheet('xxxx1')
#スプレッドシートデータをdataframeに取り込む
spread = get_as_dataframe(wks,sheet_name='xxxx1',usecols=[3,4,5,6,7,8,9],skiprows=2, skipcol=3,evaluate_formulas=True)

print(spread)

rows = dataframe_to_rows(spread, index=False, header=False)

#セルに値貼り付け
for r_idx, row in enumerate(rows, start=5):
       for c_idx, value in enumerate(row, start=2):
            ws.cell(row=r_idx, column=c_idx, value=value)

Comments

[nto]

厳密には原則としてget_as_dataframeに限らず、範囲を指定しないと限り任意のセルへの書き込みは出来ません。
例えば「データが入力された最終行の次の行」←こちらも範囲の指定となります。
一体どういった容量のどういったデータを、スプレッドシート内のどこに貼り付けしたいのかを明示的にする必要があると思います。

[退会済みユーザー]

お返事ありがとうございます。
なるほど！
copy、duplicate_sheet（）はスプレッドシートからスプレッドシートしか無理という認識で間違いなでしょうか？？

[nto]

はい。いずれのメソッドもシートキーやシートIdなどを使用するものであり、スプレッドシートtoスプレッドシートが対象となります。
例えば日毎に数件、スプレッドシートへとデータが追加され、追加されたデータだけをExcelに追加していきたいという事であれば、スプレッドシート上で同時にいつ追加したかも管理する事で新規追加された日にちを指定してExcelに追加する事もできるでしょうし
スプレッドシートへのデータ追加自体が手動ではなくpythonなどで行われているのならば、その際に同時にExcelにもデータを追加すれば良いでしょう。

[退会済みユーザー]

とても丁寧な説明ありがとうございます。

スプレッドシートへのデータ追加自体が手動ではなくpythonなどで行われているのならば、その際に同時にExcelにもデータを追加すれば良いでしょう。
↓
追加というのはget_as_dataframeのようにdataframeに格納しないと難しいですか？？

[nto]

そうなると、単なるExcelへのデータの追加となるのでまずはpythonによるExcelへのデータの追加の手法等をお調べしてはいかがでしょうか。
データフレームに限らず、Excelへの書き込みの手法は様々あると思います。
どういった形式でExcelに保存されているのかわからない為具体的な解答が出来ませんが、例えばExcel内の最終行を取得し、最終行+1を指定して書き込んでいく等。

[退会済みユーザー]

excelのsheet1（すべて空白）にスプレッドシートの202011というシートに書いてある全ての値を取得し貼り付けたいでのです！

[nto]

質問する際にかなり大事な情報だと思います。
一番初めにコメント差し上げた際にも明示的にする様にとコメント差し上げましたが事前の質問内容の投稿時にそういった内容は記載すべきです。

Answer 1

既に.get_as_dataframe()にてスプレッドシート内のデータをデータフレーム化する事が出来ているので
あとはpandasによる.to_excel()にてデータフレームをExcelファイルに変換する事が可能です。

from gspread_dataframe import get_as_dataframe
import pandas

df_sps = get_as_dataframe('任意のシート',usecols=['任意'],skiprows='任意', skipcol='任意',evaluate_formulas='任意')
df_sps.to_excel('ファイルパス', index=False, header=False)

binファイルの抽出とExcelファイルへの適用について

.to_excel()では直接xlsmを生成/上書きする事は出来ません。
既存のマクロ有効ブックを上書きするといった場合には

• 予めバイナリファイルを抽出してく
• 一旦xlsxで保存
• その後バイナリファイルを適用
• xlsmにリネーム

という手順を踏む必要があります。バイナリファイルの抽出は以下のコマンドで
vbaProject.binというマクロが格納されたバイナリファイルが抽出可能です。

python C:\Users\ユーザー名\AppData\Local\Programs\Python\Pythonバージョン(適宜)\Scripts\vba_extract.py 抽出したいExcelの絶対パス

binファイルの抽出が出来たら以下の様にして.to_excel()を用いてマクロ有効ブックの保存が可能です。

# 一時的に.xlsxで保存
writer = pd.ExcelWriter('xxxxx.xlsx', engine='xlsxwriter')

df_sps.to_excel(writer, sheet_name='Sheet1')

workbook  = writer.book
# マクロ有効ブック形式にリネーム
workbook.filename = 'xxxxx.xlsm'
# binファイルを適用
workbook.add_vba_project('./vbaProject.bin')

writer.save()

Comments

[退会済みユーザー]

試してみますとターミナルにエラー出ないのですがエクセルに反映されません、、、、、何がダメなのでしょうか、、、

[退会済みユーザー]

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread_dataframe import get_as_dataframe
import win32com.client
import openpyxl as px
import pandas as pd
import glob
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas

scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
#認証情報設定
credentials = ServiceAccountCredentials.from_json_keyfile_name('xxxxx.json', scope)
#OAuth2の資格情報を使用してgoogle APIにログインします
gc = gspread.authorize(credentials)

# print(wks.worksheets())
#ワークブックを読み込む
wb=px.load_workbook(’xxxx.xlsm',keep_vba=True,read_only=False)
#シートにアクセスする
ws=wb["シフト｜スプレッド取得"]
wks = gc.open('gspreadサンプル').worksheet('シート1')

df_sps = get_as_dataframe(wks,sheet_name='シート1',usecols=[3,4,5,6,7],skiprows=2, skipcol=3,evaluate_formulas=True)

df_sps.to_excel(’xxxxx.xlsm', index=False, header=False)

print(df_sps)

[nto]

'gspreadサンプル'というのはシートキーでも入れているのでしょうか？
その場合にはgc.open()ではなくgc.open_by_key(シートキー)です。
また.to_excel()はマクロ有効ブックは対応しておりません。

- Excelファイルを新規で作成しようとしているという場合→素直にxlsxで保存する
- マクロは組まれておらず、単に拡張子がxlsmの場合→xlsxで保存する
- 既存のマクロを持ったExcelファイルに上書きをしようとしている場合→予めbinファイルを抽出し、xlsxで保存、後にリネームとbinファイルを適用
- これからマクロを設定する場合→まずはxlsxで保存の後にマクロファイルに変換しましょう。

bin抽出参考:https://stackoverflow.com/questions/40913896/python-vba-extract-to-get-bin-of-macro

[退会済みユーザー]

とても丁寧な説明ありがとうございます。
'gspreadサンプル'はシートキーとは別です！

既存のマクロが入っているので.xlsmを使用しています
質問なのですがbinファイルを抽出し、xlsxで保存、の後にマクロファイルに変換するのことは可能なのでしょうか？？

あとfrom openpyxl.utils.dataframe import dataframe_to_rowsのやり方もしてみたのですがこちらも上手くいかず
おそらくの原因がとってくる値の中に空白の部分がNanと取得されてしまっているのがダメだと考えているのですが関係ありませんか？？
図々しく質問すみません。
お手すきの際にご回答いただけると幸いです。

[nto]

>>> 質問なのですがbinファイルを抽出し、xlsxで保存、の後にマクロファイルに変換するのことは可能なのでしょうか？？
A.既に先日の回答に追記で掲載しております。

>>> Nanと取得されてしまっているのがダメだと考えているのですが関係ありませんか？？
A.Nan値について一度ご自身で調べたり等はされましたか？
Nanというのは欠損値の事を言います。特にそれが影響しているといったことはないかと思われます。

先日の回答はご確認頂けていらっしゃいますでしょうか。
dataframe_to_rows()は使用しておりません。使用する必要がありません。
dfにした時点で、そのdfをそのままto_excel()でExcel形式に変換するだけで問題ありません。