PythonでSalesforceレポートからGoogleスプレッドシートにデータを自動更新する

Salesforceからレポートをダウンロードし、Googleスプレッドシートにデータを更新してみましょう。

コードの前半でSalesforceの認証からデータのダウンロード、後半はgspreadを使い、Googleスプレッドシートの認証、書き込みを行っています。

手順を順番に説明していきます。

simple salesforceを使ってSalesforceの認証

simple-salesforceを読み込むことで、PythonからSalesforceへの認証を行います。

認証方法はいくつかありますが、ここではID、パスワードに加えて、セキュリティトークンを利用したログインを行っています。


from simple_salesforce import Salesforce

ファイル内に直接記述してもOKですが、dotenvを使い、

envファイルを別途用意して、そちらに書き込み、後ほど読み込む形としています。

SF_orgのところは、ログインに使うURLを記入します。

私のドメインを使っている場合は、＊＊＊＊のところがドメイン名になります。

https://＊＊＊＊.my.salesforce.com/


path = "**.env" load_dotenv(path)

sf = Salesforce(username = os.environ["SF_Username"],

password = os.environ["SF_Password"],

security_token = os.environ["SF_Security_Token"])

sf_org = os.environ["SF_org"]

Salesforceレポートのダウンロード

Salesforceへログインができたら、レポートを指定します。

レポートのIDを指定する際に、パラメータも指定しておきましょう。エンコードはUTF-8、CSV形式でダウンロードします。

ダウンロードが終わったCSVをpandasで加工する指示も出しておきます。


# パラメータ指定

export_params = '?isdtp=p1&export=1&enc=UTF-8&xf=csv'
# レポートのIDを指定

report_id_1 = '00O0K00000*****'

# レポートのダウンロードを指示

sf_report_url_1 = sf_org + report_id_1 + export_params

response_1 = requests.get(sf_report_url_1, headers=sf.headers, cookies={'sid': sf.session_id})

new_report_1 = response_1.content.decode('utf-8')

&amp;amp;amp;amp;nbsp;

# pandasでCSVとして読み込みし、整形の準備

report_df_1 = pd.read_csv(StringIO(new_report_1))

pandasを使ってデータの整形をする

レポートがダウンロードできたら、データを整形しましょう。pandasを使って整えていきます。

Googleスプレッドシートにアップ後のデータを使いやすくするために週毎に並び替えしておきます。

すると、Googleスプレッドシート上で、ピボットテーブル等を利用しやすくなると思います。

今回は週毎に並び替えしていますが、名前順や、クライアント順など指定し、最終的なアウトプットをしやすい状態に仕上げます。


# ダウンロードしたデータから、ピボットテーブルを作成します。縦に（行に）商談の所有者、横に（列に）週を指定し、商談金額の合計値のピボットを作ります。
report_1 = report_df_1.pivot_table(index="商談 所有者", columns= "週", values="金額", aggfunc='sum', margins=True, margins_name='合計')

# 週毎で並べ替えを行いたいですが、そのままでは並び方がおかしくなるので、下記のように直接並び順を指定しています。
report_1 = report_1.reindex(['1W','2W','3W','4W','5W','6W','7W','8W','9W','10W','11W','12W','13W','合計'], axis=1)

gspreadを使ってGoogleスプレッドシートへアップロード

データの整形が終わったらいよいよアップロードです。アップロードの前に、認証を通過しないといけません。

事前にGCPでAPIを使う準備をしておきます。

Googleの認証においても、いくつか方法がありますが、ここではOAuth Client IDを使って認証するこの方法を使いました。

credentials.jsonを指定の場所に置かないと、なかなか認証されないためハマりかけましたが、、、

指示通り、下記にフォルダを作成し、JSONファイルの名前を変更して置いたところ無事に認証されました。（環境はWindows10です。）

%APPDATA%\gspread\credentials.json

この方法を使えば、スプレッドシートごとにサービスアカウントへの共有をひとつずつせずに、ドメイン全体が認証されるため、便利です。

ただ、セキュリティは甘くなるため、注意する必要はありそうです。

※最初は別の方法で、サービスアカウントをJSONファイルとともに認証してログインを試しましたが、スプレッドシートがGsuiteの契約であり、サービスアカウントへの共有が無効化されてしまっていたため、この方法は使えませんでした。

# credentials.jsonをダウンロードして下記の指示どおりの場所に置きます。
gc = gspread.oauth()

# GoogleスプレッドシートのKeyを入力して開きます。初回のみブラウザが開き、認証を要求されるはずです。
sh = gc.open_by_key("シートKeyを入力します")

# Googleスプレッドシートのシート名を指定します。
ws_1 = sh.worksheet('sheet1')

# Googleスプレッドシートへレポートからダウンロードしたデータをdataframeとして貼り付けます。
set_with_dataframe(ws_1, report_1, row=3, col=1, include_index=True, include_column_header=True)

全体のコード：Python

# 各機能を使えるように読み込みます
from base64 import encode
from simple_salesforce import Salesforce
from gspread_dataframe import set_with_dataframe
import requests
import gspread
import datetime
import pandas as pd
from io import StringIO
import os
from dotenv import load_dotenv

# 環境変数利用開始、パスワード等の保護のために、別途ファイル内に記述します。
# ファイルの場所は　path = "**.env"　で指定します。

path = "**.env"
load_dotenv(path)

# 【共通】Salesforceにログインします。上記で設定したファイルからパスワード等を読み込みます。
sf = Salesforce(username = os.environ["SF_Username"], 
password = os.environ["SF_Password"],
security_token = os.environ["SF_Security_Token"])

# Salesforceレポートの詳細を指定していきます。ここでは組織名と、レポートダウンロードの際のパラメータを指定しておきます。
sf_org = os.environ["SF_org"]
export_params = '?isdtp=p1&export=1&enc=UTF-8&xf=csv'
# レポートのIDを記述します。ここでは4つの別々のレポートから一気に読み込むので、それぞれのIDを記述します。
report_id_1 = '00O0K00000*****'
report_id_2 = '00O0K00000*****'
report_id_3 = '00O0K00000*****'
report_id_4 = '00O0K00000*****'

# レポートのダウンロード①
sf_report_url_1 = sf_org + report_id_1 + export_params
response_1 = requests.get(sf_report_url_1, headers=sf.headers, cookies={'sid': sf.session_id})
new_report_1 = response_1.content.decode('utf-8')
report_df_1 = pd.read_csv(StringIO(new_report_1))

# レポートのダウンロード②
sf_report_url_2 = sf_org + report_id_2 + export_params
response_2 = requests.get(sf_report_url_2, headers=sf.headers, cookies={'sid': sf.session_id})
new_report_2 = response_2.content.decode('utf-8')
report_df_2 = pd.read_csv(StringIO(new_report_2))

# レポートのダウンロード③
sf_report_url_3 = sf_org + report_id_3 + export_params
response_3 = requests.get(sf_report_url_3, headers=sf.headers, cookies={'sid': sf.session_id})
new_report_3 = response_3.content.decode('utf-8')
report_df_3 = pd.read_csv(StringIO(new_report_3))

# レポートのダウンロード④
sf_report_url_4 = sf_org + report_id_4 + export_params
response_4 = requests.get(sf_report_url_4, headers=sf.headers, cookies={'sid': sf.session_id})
new_report_4 = response_4.content.decode('utf-8')
report_df_4 = pd.read_csv(StringIO(new_report_4))

# ダウンロードしたデータから、ピボットテーブルを作成します。
report_1 = report_df_1.pivot_table(index="商談 所有者", columns= "週", values="金額", aggfunc='sum', margins=True, margins_name='合計')
report_2 = report_df_2.pivot_table(index="商談 所有者", columns= "週", values="金額", aggfunc='sum', margins=True, margins_name='合計')
report_3 = report_df_3.pivot_table(index="商談 所有者", columns= "週", values="金額", aggfunc='sum', margins=True, margins_name='合計')
report_4 = report_df_4.pivot_table(index="商談 所有者", columns= "週", values="金額", aggfunc='sum', margins=True, margins_name='合計')

# 週毎で並べ替えを行いたいですが、そのままでは並び方がおかしくなるので、下記のように直接並び順を指定しています。
report_1 = report_1.reindex(['1W','2W','3W','4W','5W','6W','7W','8W','9W','10W','11W','12W','13W','合計'], axis=1)
report_2 = report_2.reindex(['1W','2W','3W','4W','5W','6W','7W','8W','9W','10W','11W','12W','13W','合計'], axis=1)
report_3 = report_3.reindex(['1W','2W','3W','4W','5W','6W','7W','8W','9W','10W','11W','12W','13W','合計'], axis=1)
report_4 = report_4.reindex(['1W','2W','3W','4W','5W','6W','7W','8W','9W','10W','11W','12W','13W','合計'], axis=1)

# Googleスプレッドシートにログインします。
# https://gspread.readthedocs.io/en/latest/oauth2.html#for-end-users-using-oauth-client-id
gc = gspread.oauth()

# GoogleスプレッドシートのKeyを入力して開きます。初回のみブラウザが開き、認証を要求されるはずです。
sh = gc.open_by_key("シートKeyを入力します") 

# Googleスプレッドシートのシート名をそれぞれ指定します。
ws_1 = sh.worksheet('sheet1') 
ws_2 = sh.worksheet('sheet2') 
ws_3 = sh.worksheet('sheet3') 
ws_4 = sh.worksheet('sheet4') 
ws_5 = sh.worksheet('sheet5') 

# Googleスプレッドシートへレポートからダウンロードしたデータを貼り付けます。こちらで完了です。
set_with_dataframe(ws_1, report_1, row=3, col=1, include_index=True, include_column_header=True)
set_with_dataframe(ws_2, report_2, row=3, col=1, include_index=True, include_column_header=True)
set_with_dataframe(ws_3, report_3, row=3, col=1, include_index=True, include_column_header=True)
set_with_dataframe(ws_4, report_4, row=3, col=1, include_index=True, include_column_header=True)

# おまけ：いつ編集したかわかるように編集日した日付を入力します。
now = datetime.datetime.now()

now_date = now.strftime('%m/%d')
now_time = now.strftime('%H:%M')

ws_5.update_acell('C1',now_date)
ws_5.update_acell('D1',now_time)

# おまけ：Google chatへ投稿します。Webhookを使って、更新が完了した旨を投稿します。
webhook_url = 'https://chat.googleapis.com/v1/spaces/AAAAfpcAoEc/messages?key=***'

response = requests.post(
  webhook_url,
  json={"text": "スプレッドシートの更新が完了したよ"}
)

いかがでしたでしょうか？

SalesforceとGoogleスプレッドシートはアドオンで連携させる方法もありますが、更新頻度が限られることや、表示形式に制限があります。

レポート形式で自由度高く連携させる場合には、少し高度になりますが、Pythonを活用すると良いかもしれません。

ぜひお試しください。

simple salesforceを使ってSalesforceの認証

Salesforceレポートのダウンロード

pandasを使ってデータの整形をする

gspreadを使ってGoogleスプレッドシートへアップロード

全体のコード：Python

関連する記事:

SNSでもご購読できます。

よく読まれている記事

カテゴリー

タグ