21
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

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

背景

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形式にする

参考にしたサイト

Microsoft Graph での Excel の操作

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
21
Help us understand the problem. What are the problem?