从现有的Google电子表格获取响应，追加行并更新

Question

我可以连接到谷歌电子表格，并像下面展示的那样添加新行（从列表中实际值），它可以正常工作：

import gspread
from oauth2client.service_account import ServiceAccountCredentials

from pprint import pprint
from googleapiclient import discovery

scope = ['https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/spreadsheets.currentonly',
    'https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
gc = gspread.authorize(credentials)

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

#Name of Spreadsheet
Sheet_Title = "New Spreadsheet Final"

# The ID of the spreadsheet 
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'  # TODO: Update placeholder value.
#Get sheet ID

#Open the wanted Tab
sheet = gc.open(Sheet_Title).worksheet("Tab One")  

#Get the wanted range of the sheet
range_name = 'A1:W1000'  # TODO: Update placeholder value.

#Add new rows in the Sheet. Values are hardcoded below
values = [
    ['09/01/2021', 'IT', '8%'],
    ['08/02/2021', 'NL', '1%']
    # Additional rows ...
]
body = {
    'values': values
}

#Append the rows in the Google Spreadsheet
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption='USER_ENTERED', body=body).execute()

我现在想做的是添加新行，但对于每个单元格（或通常是特定列），都要添加数据验证。因此，我向下滚动并检查了响应。然后，我附加了一个包括格式和数据验证的新行值：

#Values to be added in the original query
value_to_be_added = [
              {
                "formattedValue": "05/05/2019",
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "DATE",
                    "pattern": "dd/mm/yyyy"
                  }
                },
                "dataValidation": {
                  "condition": {
                    "type": "DATE_IS_VALID"
                  },
                  "strict": "True"
                }
              },
              {
                "formattedValue": "DE",
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "NUMBER",
                    "pattern": "#,##0.00"
                  },
                  "verticalAlignment": "BOTTOM"
                },
                "dataValidation": {
                  "condition": {
                    "type": "ONE_OF_LIST",
                    "values": [
                      {
                        "userEnteredValue": "UK"
                      },
                      {
                        "userEnteredValue": "ES"
                      },
                      {
                        "userEnteredValue": "IT"
                      },
                      {
                        "userEnteredValue": "DE"
                      },
                      {
                        "userEnteredValue": "AT"
                      },
                      {
                        "userEnteredValue": "NL"
                      }
                    ]
                  },
                  "showCustomUi": "True"
                }
              },
              {
                "formattedValue": "4%",
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "PERCENT",
                    "pattern": "0%"
                  }
                }
              }
            ]

换句话说，我已经尝试操纵原始响应的JSON，唯一需要的就是更新整个文件，但我找不到正确的更新函数：

#Get request to get the full Google Spreadsheet in a JSON
request = service.spreadsheets().get(spreadsheetId=spreadsheet_id, ranges=range_name, includeGridData=True)
response = request.execute()

#dictionary that will be appended in the values of the response
mydict = {}

#Assign dictionary with the new value. We have one value in this case.
mydict[row_count+1] = value_to_be_added

#print(mydict[row_count+1])

#Add new row in the response.
response['sheets'][0]['data'][0]['rowData'][x]['values'].append(mydict[row_count+1])

#updated response that has to be sent/updated in the API.
new_response = response

有没有一种方法可以完全更新/替换原始响应与更新的响应？

原始电子表格：

我的更新请求后的最终电子表格（我想要它是这样的）：

您可以看到一行新数据应用了数据验证（可以是X行数据）。

- Datacrawler

---

为了正确理解您的情况，您能提供一个包含您想要的输入和输出的样本电子表格吗？通过这个，我可以考虑解决方案。当然，请删除您的个人信息。 - Tanaike

感谢您的回复和添加信息。根据您提供的额外信息，我已经给出了一个示例脚本作为答案。请您确认一下是否正确。如果我误解了您的问题，这不是您想要的结果，我向您道歉。 - Tanaike

Answer 1

• 您想将一行附加到电子表格的最后一行的下一行。
  • 您想添加一行，例如dd/mm/yyyy格式的日期值、数据验证、0%格式的数字以及数字。
  • 您想将05/05/2019、英国、8%、9的值作为显示值。
• 您希望使用Python中的gspread实现此目标。
• 您已经能够使用Sheets API获取和放置电子表格的值。

如果我的理解是正确的，那么这个示例脚本怎么样？在这个示例脚本中，我使用了gspread的batch_update方法。gspread的batch_update方法是Sheets API的spreadsheets.batchUpdate方法。并且使用appendCells请求附加行。

示例脚本：

示例脚本如下所示。在运行脚本之前，请设置电子表格ID和工作表名称。在此脚本中，使用Tab One作为工作表名称。

gc = gspread.authorize(credentials)
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
sheetName = "Tab One"
spreadsheet = gc.open_by_key(spreadsheet_id)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']
body = {'requests':
        [
            {
                'appendCells':
                {
                    'rows':
                    [
                        {
                            'values':
                            [
                                {
                                    'userEnteredFormat':
                                    {
                                        'numberFormat':
                                        {
                                            'type': 'DATE',
                                            'pattern': 'dd/mm/yyyy'
                                        }
                                    },
                                    'dataValidation':
                                    {
                                        'condition':
                                        {
                                            'type': 'DATE_IS_VALID'
                                        },
                                        'strict': True
                                    },
                                    'userEnteredValue':
                                    {
                                        'numberValue': 43590  # This is the serial number of "05/05/2019".
                                    }
                                },
                                {
                                    'userEnteredFormat':
                                    {
                                        'numberFormat':
                                        {
                                            'type': 'NUMBER',
                                            'pattern': '#,##0.00'
                                        },
                                        'verticalAlignment': 'BOTTOM'
                                    },
                                    'dataValidation':
                                    {
                                        'condition':
                                        {
                                            'type': 'ONE_OF_LIST',
                                            'values':
                                            [
                                                {
                                                    'userEnteredValue': 'UK'
                                                },
                                                {
                                                    'userEnteredValue': 'ES'
                                                },
                                                {
                                                    'userEnteredValue': 'IT'
                                                },
                                                {
                                                    'userEnteredValue': 'DE'
                                                },
                                                {
                                                    'userEnteredValue': 'AT'
                                                },
                                                {
                                                    'userEnteredValue': 'NL'
                                                }
                                            ]
                                        },
                                        'showCustomUi': True
                                    },
                                    'userEnteredValue':
                                    {
                                        'stringValue': 'UK'
                                    }
                                },
                                {
                                    'userEnteredFormat':
                                    {
                                        'numberFormat':
                                        {
                                            'type': 'PERCENT',
                                            'pattern': '0%'
                                        }
                                    },
                                    'userEnteredValue':
                                    {
                                        'numberValue': 0.08
                                    }
                                },
                                {
                                    'userEnteredValue':
                                    {
                                        'numberValue': 9
                                    }
                                }
                            ]
                        }
                    ],
                    'sheetId': sheetId,
                    'fields': '*'
                }
            }
        ]
        }
res = spreadsheet.batch_update(body)
print(res)

注意：

• 当将 05/05/2019 作为dd/mm/yyyy格式的日期时，请将序号43590输入。

参考资料：

• batch_update(body)
• Method: spreadsheets.batchUpdate
• AppendCellsRequest

补充：

如果您想在for循环中使用请求主体，可以使用以下脚本。 batchUpdate方法可以与数组一起使用。

gc = gspread.authorize(credentials)
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
sheetName = "Tab One"
spreadsheet = gc.open_by_key(spreadsheet_id)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']
requests = []
for x in range(len(new_values)):  # Please set "new_values".
    body =                 {
                    'appendCells':
                    {
                        'rows':
                        [
                            {
                                'values':
                                [
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'DATE',
                                                'pattern': 'dd/mm/yyyy'
                                            }
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'DATE_IS_VALID'
                                            },
                                            'strict': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][0]
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'NUMBER',
                                                'pattern': '#,##0.00'
                                            },
                                            'verticalAlignment': 'BOTTOM'
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'ONE_OF_LIST',
                                                'values':
                                                [
                                                    {
                                                        'userEnteredValue': 'UK'
                                                    },
                                                    {
                                                        'userEnteredValue': 'ES'
                                                    },
                                                    {
                                                        'userEnteredValue': 'IT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'DE'
                                                    },
                                                    {
                                                        'userEnteredValue': 'AT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'NL'
                                                    }
                                                ]
                                            },
                                            'showCustomUi': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'stringValue': new_values[x][1]
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'PERCENT',
                                                'pattern': '0%'
                                            }
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][2]
                                        }
                                    },
                                    {
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][2]
                                        }
                                    }
                                ]
                            }
                        ],
                        'sheetId': sheetId,
                        'fields': '*'
                    }
                }
    requests.append(body)

res = spreadsheet.batch_update({'requests':requests})
print(res)

- Tanaike

---

这个方案可行，我打算接受这个答案。我还会根据你的版本发布我的版本。如果有更简单的方法，请你提出建议。 - Datacrawler

@Apolo Radomer 谢谢你的回复。很高兴你的问题得到解决。我为你添加了一个额外的脚本样例。你能确认一下吗？ - Tanaike

是的，我看到你添加了那部分。在我的原始脚本中，我还将日期转换为一个5位数的序列码，并将所有内容添加到函数中（尽管这并不会使脚本更快，但会更清晰）。 - Datacrawler

Answer 2

基于Tanaike的答案，这是我的版本：

#List of rows with values
new_values = [[43590,'UK',0.08,9],[43590,'DE',0.07,8]]

#connect to the google sheet and tab
sheetName = "Tab One"
spreadsheet = gc.open_by_key(spreadsheet_id)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']

#Add each value/row
for x in range(len(new_values)):
    #print(new_values[x])
    body = {'requests':
            [
                {
                    'appendCells':
                    {
                        'rows':
                        [
                            {
                                'values':
                                [
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'DATE',
                                                'pattern': 'dd/mm/yyyy'
                                            }
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'DATE_IS_VALID'
                                            },
                                            'strict': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][0]  # This is the serial number of "05/05/2019".
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'NUMBER',
                                                'pattern': '#,##0.00'
                                            },
                                            'verticalAlignment': 'BOTTOM'
                                        },
                                        'dataValidation':
                                        {
                                            'condition':
                                            {
                                                'type': 'ONE_OF_LIST',
                                                'values':
                                                [
                                                    {
                                                        'userEnteredValue': 'UK'
                                                    },
                                                    {
                                                        'userEnteredValue': 'ES'
                                                    },
                                                    {
                                                        'userEnteredValue': 'IT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'DE'
                                                    },
                                                    {
                                                        'userEnteredValue': 'AT'
                                                    },
                                                    {
                                                        'userEnteredValue': 'NL'
                                                    }
                                                ]
                                            },
                                            'showCustomUi': True
                                        },
                                        'userEnteredValue':
                                        {
                                            'stringValue': new_values[x][1]
                                        }
                                    },
                                    {
                                        'userEnteredFormat':
                                        {
                                            'numberFormat':
                                            {
                                                'type': 'PERCENT',
                                                'pattern': '0%'
                                            }
                                        },
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][2]
                                        }
                                    },
                                    {
                                        'userEnteredValue':
                                        {
                                            'numberValue': new_values[x][3]
                                        }
                                    }
                                ]
                            }

                        ],
                        'sheetId': sheetId,
                        'fields': '*'
                    }
                }
            ]
            }
    res = spreadsheet.batch_update(body)

在每个循环中，都会拉取主体。如果您认为这不是最佳方式（循环主体），请提供任何其他选择。在主体对象中添加循环并追加values数组可能更容易。

- Datacrawler

---

关于你删除的问题，你可能可以利用正则表达式字典树来解决。 - Wiktor Stribiżew