[Python] Excel OnlineをAPI経由で更新する方法

More than 1 year has passed since last update.


背景

PythonのPandasなどを使ってサーバー上で処理をしたCSVを手作業を介さずにExcel Onlineのシートに貼り付けたい

->Excel OnlineをAPIでごにょごにょすれば出来そう

->ググってもあまり情報が出てこない…

->公式のドキュメントがあった

->普段は他記事のソースのコピペ・組み合わせで動かしているようなレベルなので辛い

->なんとか動いたのでまとめておきます。

公式のドキュメントはここです。


前提条件

1.OAuth認証済み

2.アクセストークンとリフレッシュトークンを取得する仕組みは完成

3.ブックは作成済み

4.シートも作成済み

1.2に関してはこちらの記事にまとめましたのでご覧ください。

Microsoft GraphでOAuthのアクセストークンを更新する仕組みを作る


事前に必要な作業


ブックIDを調べる

https://developer.microsoft.com/ja-jp/graph/graph-explorer

スクリーンショット 2017-12-04 17.41.43.png

左上の認証からサインインして

右上のフォームにhttps://graph.microsoft.com/v1.0/me/drive/root/search(q='.xlsx')?select=name,id,webUrlを入力すると自分のOne Driveに入っているファイルの一覧が出てきます。

表示されたファイル一覧から編集したいファイルのidをメモしておきます。


実装


まずは完成形から


excelWriter.py

# coding:utf-8

import os
import boto3
import json
from boto3.dynamodb.conditions import Key, Attr
import requests

client_id = 'アプリケーションID'
client_secret = 'アプリケーションシークレット'
username = 'Dynamoで記入したユーザーネーム'

s3 = boto3.client('s3')

#アクセストークンを取得&更新するための関数
def get_access_token(username):
# dynamodbからリフレッシュトークンを取得
dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('excelOnlineToken')
response = table.query(
KeyConditionExpression=Key('usename').eq(username), Limit=1)
refresh_token = response['Items'][0]['refresh_token']

# 取得したリフレッシュトークンを使って、アクセストークンを更新・取得
headers = { 'Accept' : 'application/json',
'Content-Type' : 'application/x-www-form-urlencoded'
}
payload = {'client_id': client_id, 'scope': 'User.Read Mail.Send offline_access Files.ReadWrite.All','refresh_token':refresh_token,'redirect_uri':'http://localhost:5000/login/authorized','grant_type':'refresh_token','client_secret':client_secret}
response = requests.post(
'https://login.microsoftonline.com/common/oauth2/v2.0/token',
headers=headers,data=payload)
jsonObj = json.loads(response.text)

# 取得したアクセストークンとリフレッシュトークンをDynamoに保存
table.update_item(
Key={
'usename': username
},
UpdateExpression='SET refresh_token = :val1,access_token = :val2',
ExpressionAttributeValues={
':val1': jsonObj["refresh_token"],
':val2': jsonObj["access_token"]
}
)

return jsonObj["access_token"]

# エクセルオンラインを更新するための関数
def updateWorkSheets(access_token,book_id,SheetName,values):
headers = { 'Content-Type' : 'application/json',
'authorization' : 'Bearer '+access_token,
}
# utf-8にエンコードしておく。不要かも
values = [map(lambda x:x.encode("utf8"), i) for i in values]
payload = { 'values': values }

# 最後の行数。ヘッダーが1行目に記載されている場合はこれ。ヘッダー行が無い場合は1を除外。
lastRow = len(values) + 1

# 範囲(今回は3列のデータだと仮定)
colrow = "C"+str(lastRow)

# PATCHメソッドでリクエストを投げる
response = requests.patch('https://graph.microsoft.com/v1.0/me/drive/items/' + book_id + "/workbook/worksheets('"+SheetName+"')/range(address='"+SheetName+"!A2:"+colrow+"')",headers=headers,data=json.dumps(payload))
jsonObj = json.loads(response.text)

# 確認用
# print response.text

# 今回はAWSのLambdaを利用しているのでメインの関数
def lambda_handler(event, context):
access_token = get_access_token(username)
bucket = "bucketの名前を"
key = "bucket内のファイルパスを"

# 今回はs3にあるCSVを処理するのでまずはダウンロード
s3.download_file(bucket, key, '/tmp/sample.csv')

with open('/tmp/sample.csv', 'rb') as f:
reader = csv.reader(f)
csv_list = list(reader)

# ヘッダー行を除外する(ヘッダーをそのまま貼り付けるのであれば[1:]は不要)
values = csv_list[1:]

# ブックのID・シートネーム
book_id="book_id"
SheetName = "Sheet1"

updateWorkSheets(access_token,book_id,SheetName,values)



注意点


ヘッダーにアクセストークンを


header

headers = { 'Content-Type' : 'application/json',

'authorization' : 'Bearer '+access_token,
}


更新時

・投げる時はPATCHメソッドを使う

・範囲の指定を忘れると更新できない(今回はヘッダー行を飛ばすためにA2からスタート)


request

response = requests.patch('https://graph.microsoft.com/v1.0/me/drive/items/'

+ book_id + "/workbook/worksheets('"+SheetName+"')/range(address='"
+ +SheetName+"!A2:"+colrow+"')",headers=headers,data=json.dumps(payload))


渡すのはList形式

エクセルオンラインに渡すのは2次元配列

CSVを渡せる形にするには↓これでList形式にする

https://stackoverflow.com/questions/24662571/python-import-csv-to-list


参考にしたサイト

Microsoft Graph での Excel の操作