Help us understand the problem. What is going on with this article?

Python / Google Spreadsheetを内包表記でparseする

More than 1 year has passed since last update.

Google Spreadsheetをpythonで処理するときに深いところをいちいち解析するのが面倒臭かったので,pythonの内包表記で一発処理してみました

OAuthの設定やスプレッドシートへのアクセスについては頑張ってください

更新

  • dict.getを使えばちょっと楽だったので修正

前提条件

  • 全てのシートに矩形に値が全て入っている
  • 全てのセルは文字列で値を取る(数字のセルも文字列で取ってくる)

コード

parse.py
SERVICE_ACCOUNT_FILE='/path/to/serviceaccount.file'
USER='gsuite_user@gmail.com'
sheetId='spreadsheetId'

SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets'
]
credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = googleapiclient.discovery.build('sheets', 'v4', credentials=credentials)

fields = 'sheets(properties.title,data(rowData(values(formattedValue))))'
result = service.spreadsheets().get(spreadsheetId=sheetId, includeGridData=True, fields=fields).execute()

titles = [
    x['properties']['title']
    for x in result['sheets']
]

values = [
    [
        [
            z.get('formattedValue')
            for z in y['values']
        ]
        for y in x
    ] for x in [
        x['rowData']
        for x in [
            y
            for x in result['sheets']
            for y in x['data']
        ]
    ]
]

tables = dict(zip(titles, values))

結果

tablesは以下のようになります

{u'sheetA': [[u'ID', u'VAL1', u'VAL2', u'VAL3'],
             [u'a', u'A101', u'A201', u'A301'],
             [u'b', u'A102', u'A202', u'A302'],
             [u'c', u'A103', u'A203', u'A303']],
 u'sheetB': [[u'ID', u'VAL1', u'VAL2', u'VAL3'],
             [u'A', u'B111', u'B211', u'B311'],
             [u'B', u'B112', u'B212', u'B312'],
             [u'C', u'B113', u'B213', u'B313']],
 u'sheetC': [[u'ID', u'VAL1', u'VAL2', u'VAL3'],
             [u'A', u'C121', u'C221', u'C321'],
             [u'B', u'C122', u'C222', u'C322'],
             [u'C', u'C123', u'C223', u'C323']],
 u'sheetD': [[u'ID', u'VAL1', u'VAL2', u'VAL3'],
             [u'A', u'D131', u'D231', u'D331'],
             [u'B', u'D132', u'D232', u'D332'],
             [u'C', u'D133', u'D233', u'D333'],
             [u'1111', u'1112', u'1113', u'1114']]}

感想

シートごとにspreadsheet.values.get を呼んでいたのが,spreadsheet.get 一発で終わります.ハラショー!

付記

  • fieldsを指定しないでincludeGridDataを呼んでいるとデータ量が爆発します.ちゃんと必要なものを取りましょう
  • 文字列,数字,真偽値を分けて取りたいときは,内包表記の条件式を使うと切り分けできます
fields = 'sheets(properties.title,data(rowData(values(effectiveValue))))'
result = service.spreadsheets().get(spreadsheetId=sheetId, includeGridData=True, fields=fields).execute()
values = [
[
        [
            z['effectiveValue']['numberValue'] if 'effectiveValue' in z and 'numberValue' in z['effectiveValue'] else
            z['effectiveValue']['stringValue'] if 'effectiveValue' in z and 'stringValue' in z['effectiveValue'] else
            z['effectiveValue']['boolValue'] if 'effectiveValue' in z and 'boolValue' in z['effectiveValue'] else
            None
            for z in y['values']
        ]
        for y in x
    ] for x in [
        x['rowData']
        for x in [
            y
            for x in result['sheets']
            for y in x['data']
        ]
    ]
]

でも,エラー対策すればするほど内包表記の簡素さが薄れていくのが悲しい

  • 最後に,元データは以下のように取れてます(effectiveValueの場合).ご参考まで
{u'sheets': [{u'data': [{u'rowData': [{u'values': [{u'effectiveValue': {u'stringValue': u'ID'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL1'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL2'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL3'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'a'}},
                                                   {u'effectiveValue': {u'stringValue': u'A101'}},
                                                   {u'effectiveValue': {u'stringValue': u'A201'}},
                                                   {u'effectiveValue': {u'stringValue': u'A301'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'b'}},
                                                   {u'effectiveValue': {u'stringValue': u'A102'}},
                                                   {u'effectiveValue': {u'stringValue': u'A202'}},
                                                   {u'effectiveValue': {u'stringValue': u'A302'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'c'}},
                                                   {u'effectiveValue': {u'stringValue': u'A103'}},
                                                   {u'effectiveValue': {u'stringValue': u'A203'}},
                                                   {u'effectiveValue': {u'stringValue': u'A303'}}]}]}],
              u'properties': {u'title': u'sheetA'}},
             {u'data': [{u'rowData': [{u'values': [{u'effectiveValue': {u'stringValue': u'ID'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL1'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL2'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL3'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'A'}},
                                                   {u'effectiveValue': {u'stringValue': u'B111'}},
                                                   {u'effectiveValue': {u'stringValue': u'B211'}},
                                                   {u'effectiveValue': {u'stringValue': u'B311'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'B'}},
                                                   {u'effectiveValue': {u'stringValue': u'B112'}},
                                                   {u'effectiveValue': {u'stringValue': u'B212'}},
                                                   {u'effectiveValue': {u'stringValue': u'B312'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'C'}},
                                                   {u'effectiveValue': {u'stringValue': u'B113'}},
                                                   {u'effectiveValue': {u'stringValue': u'B213'}},
                                                   {u'effectiveValue': {u'stringValue': u'B313'}}]}]}],
              u'properties': {u'title': u'sheetB'}},
             {u'data': [{u'rowData': [{u'values': [{u'effectiveValue': {u'stringValue': u'ID'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL1'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL2'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL3'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'A'}},
                                                   {u'effectiveValue': {u'stringValue': u'C121'}},
                                                   {u'effectiveValue': {u'stringValue': u'C221'}},
                                                   {u'effectiveValue': {u'stringValue': u'C321'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'B'}},
                                                   {u'effectiveValue': {u'stringValue': u'C122'}},
                                                   {u'effectiveValue': {u'stringValue': u'C222'}},
                                                   {u'effectiveValue': {u'stringValue': u'C322'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'C'}},
                                                   {u'effectiveValue': {u'stringValue': u'C123'}},
                                                   {u'effectiveValue': {u'stringValue': u'C223'}},
                                                   {u'effectiveValue': {u'stringValue': u'C323'}}]}]}],
              u'properties': {u'title': u'sheetC'}},
             {u'data': [{u'rowData': [{u'values': [{u'effectiveValue': {u'stringValue': u'ID'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL1'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL2'}},
                                                   {u'effectiveValue': {u'stringValue': u'VAL3'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'A'}},
                                                   {u'effectiveValue': {u'stringValue': u'D131'}},
                                                   {u'effectiveValue': {u'stringValue': u'D231'}},
                                                   {u'effectiveValue': {u'stringValue': u'D331'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'B'}},
                                                   {u'effectiveValue': {u'stringValue': u'D132'}},
                                                   {u'effectiveValue': {u'stringValue': u'D232'}},
                                                   {u'effectiveValue': {u'stringValue': u'D332'}}]},
                                      {u'values': [{u'effectiveValue': {u'stringValue': u'C'}},
                                                   {u'effectiveValue': {u'stringValue': u'D133'}},
                                                   {u'effectiveValue': {u'stringValue': u'D233'}},
                                                   {u'effectiveValue': {u'stringValue': u'D333'}}]},
                                      {u'values': [{},
                                                   {},
                                                   {},
                                                   {u'effectiveValue': {u'numberValue': 1114}}]}]}],
              u'properties': {u'title': u'sheetD'}}]}
Why do not you register as a user and use Qiita more conveniently?
  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
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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