我可以连接到谷歌电子表格,并像下面展示的那样添加新行(从列表中实际值),它可以正常工作:
我现在想做的是添加新行,但对于每个单元格(或通常是特定列),都要添加数据验证。因此,我向下滚动并检查了响应。然后,我附加了一个包括格式和数据验证的新行值:
换句话说,我已经尝试操纵原始响应的JSON,唯一需要的就是更新整个文件,但我找不到正确的更新函数:
有没有一种方法可以完全更新/替换原始响应与更新的响应?
原始电子表格: 我的更新请求后的最终电子表格(我想要它是这样的): 您可以看到一行新数据应用了数据验证(可以是X行数据)。
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行数据)。