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

Question

试图使用python API在google驱动器上的特定位置创建电子表格，但关于如何做到这一点的文档忽略了请求主体的具体示例。

最终，拥有一个带有标题和单行值的pandas数据帧，并尝试在谷歌驱动器中创建一个看起来像。。。

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

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

在查看此类请求的JSON主体的文档时，我遇到的一些具体问题(到目前为止，这些问题使我无法进行初始测试(是。。。

1. 什么可以用作spreadsheetId？命名冲突会发生什么？文档似乎没有任何关于这是什么或可能是什么的信息/示例
2. 我怎么知道要使用什么spreadsheetUrl？是否存在违约？这到底是什么意思/看起来像什么
3. 请求主体中的某些子部分似乎想要冗余信息(例如sheetId(。这有必要吗/我可以留白吗
4. 我如何知道/指定在谷歌驱动器中的何处创建此工作表(我有一个特定的目录路径，我想在其中创建这些电子表格(

这是我迄今为止构建的请求体(注意，这里我只是试图将数据以传统的表格格式row1=header，row2=values(。。。

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,
}

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

有没有其他地方可以看到文档上链接的更多exmaples/info？

Answer 1

根据对此处和此处问题的回答，这就是我目前在Drive中创建电子表格并添加值和受保护范围以保护元数据值的方式(通过python API客户端(。

基本上，由于spreadsheets.create()方法无法指定驱动器位置，我必须使用驱动器api在指定位置创建电子表格(通过驱动器文件夹ID(您在驱动器文件夹的URL中看到的ID字符串((，从请求响应中获取分配的电子表格ID，构建一系列Sheetsapi请求，然后通过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#exampleshttps://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequesthttps://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addprotectedrangerequest