代码之家 › 专栏 › 技术社区 › lampShadesDrifter

通过python API在特定google驱动器位置创建google电子表格的请求主体示例?

google-sheets-api google-sheets python

0

lampShadesDrifter · 技术社区 · 6 年前

试图使用python API在google drive上的特定位置创建电子表格,但是 the docs 关于如何做到这一点,请省略任何具体例子,说明 request body 看起来像。

------------------
header1 | value1 |
------------------
header2 | value2 |
------------------
header3 | value3 |
------------------
header4 | value4 |
------------------
header5 | value5 |
------------------

其中第1、2和5行受保护。

spreadsheetId ? 命名冲突会发生什么?这些文档似乎没有任何信息/示例这是或者可以是。
我怎么知道呢 spreadsheetUrl 使用?是否存在违约?这到底是什么意思?
请求主体的某些子部分似乎需要冗余信息(例如。 sheetId ).这有必要吗/我可以留白吗?
我如何知道/说明 谷歌驱动器在哪里 将要创建此工作表(我有一个特定的目录路径,我想在其中创建这些电子表格)?

)...

df = <some dataframe>
HEADER = list(df.columns)
VALUES = df.values.tolist()[0]  # first row of values from dataframe

# building request body for API spreadsheets.create request
request_body = {
  "spreadsheetId": VALUES[0],  # this is a UUID for the data row in the pandas dataframe
  "properties": {
    "title": f"{DATANAME}__{VALUES[0]}",
  },
  "sheets": [
    {
        "properties": {
            "sheetId": 1,
            "title": DATANAME
        },
        "data": [
            {
              "startRow": 1,
              "startColumn": 1,
              "rowData": [
                {
                    "values": [
                        HEADER, # list if values
                        VALUES  # list of values
                    ]
                }
              ],
            }
        ],
        "protectedRanges": [
            {
              "protectedRangeId": integer, # what even is this?
              "range": {
                  {
                      "sheetId": 1, # do I really need to specify the sheetId again here?
                      "startRowIndex": 1,
                      "endRowIndex": 3,
                      "startColumnIndex": 1,
                      "endColumnIndex": 3
                  }
              },
              "description": "This is a metadata field and should not be edited",
              "warningOnly": False,
            },
            {
              "protectedRangeId": integer, # what even is this?
              "range": {
                  {
                      "sheetId": 1,
                      "startRowIndex": 5,
                      "endRowIndex": 6,
                      "startColumnIndex": 5,
                      "endColumnIndex": 6
                  }
              },
              "description": "This is a metadata field and should not be edited",
              "warningOnly": False,
            }
        ]
    }
  ],
  "spreadsheetUrl": string,
}

有更多经验的人能给我举个例子,说明在这种情况下实际工作的请求主体是什么样子的(以及我迄今为止构建的内容有什么问题吗)?以及回答上面一些更具体的问题?

还有其他地方可以看到更多链接到文档上的Exmaple/信息吗?

0 回复 | 直到 6 年前

1

0

lampShadesDrifter 6 年前

从对问题的回答 here here ,这就是我目前在驱动器中创建电子表格的方式,并添加值和受保护范围以保护元数据值(通过python API客户端)。

基本上,自从 spreadsheets.create() spreadsheets.batchUpdate (参考创建的电子表格的ID)

# getting data from pandas dataframe for writing
df = df.fillna("")
HEADER = [str(c) for c in df.columns]
VALUES = [str(v) for v in df.values.tolist()[0]]
data = [HEADER, VALUES]
print(HEADER, VALUES)

# using Drive API to create the Spreadsheet in specified Drive location
drive = build('drive', 'v3', credentials=creds)
file_metadata = {
    'name': 'sampleName',
    'parents': ['### folderId ###'],
    'mimeType': 'application/vnd.google-apps.spreadsheet',
}
res = drive.files().create(body=file_metadata).execute()
print(res)
SPREADSHEET_ID = res['id']

# building request object
requests = [
    {  
        "appendCells":
        {
            "sheetId": 0,
            "rows": [
                {   # need to build the individual cell value objects for the request
                    "values": [{"userEnteredValue": {"stringValue": v}} for v in HEADER],
                },
                {
                    "values": [{"userEnteredValue": {"stringValue": v}} for v in VALUES],
                }
            ],
            "fields": "*"
         },
    },
    {  
        "addProtectedRange": {
            # protecting header fields
            "protectedRange": {
                "range": {
                    "sheetId": 0,  # sheets are indexed from 0
                    "startRowIndex": 0,
                    "endRowIndex": 1,  # pretty sure the endIndexes are exclusive
                    "startColumnIndex": 0,
                    "endColumnIndex": len(HEADER)
                },
                "description": "Do not edit this header data",
                "warningOnly": False,
            }
        }
    },
    {
        "addProtectedRange": {
            # protecting metadata values
            "protectedRange": {
                "range": {
                    "sheetId": 0,  # sheets are indexed from 0
                    "startRowIndex": 0,
                    "endRowIndex": 2,  # pretty sure the endIndexes are exclusive
                    "startColumnIndex": 0,
                    "endColumnIndex": 2
                },
                "description": "This is a metadata field and should not be edited",
                "warningOnly": False,
            }
        }
    },
    {
        "addProtectedRange": {
            # protecting metadata values
            "protectedRange": {
                "range": {
                    "sheetId": 0,  # sheets are indexed from 0
                    "startRowIndex": 0,
                    "endRowIndex": 2,  # pretty sure the endIndexes are exclusive
                    "startColumnIndex": 8,
                    "endColumnIndex": 10
                },
                "description": "This is a metadata field and should not be edited",
                "warningOnly": False,
            }
        }
    }
]

# do the batch updates on the Spreadsheet
request_body = {"requests": requests}
assert "requests" in request_body.keys()
assert isinstance(request_body["requests"], typing.List)
sheets = discovery.build('sheets', 'v4', credentials=credentials)
request = sheets.spreadsheets() \
            .batchUpdate(spreadsheetId=SPREADSHEET_ID, body=request_body)
request.execute()
print(request)

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate#examples https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addprotectedrangerequest