背景
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
左上の認証からサインインして
右上のフォームに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形式にする