Google Sheets API v4方法:spreadsheets.values.append仅更新一个单元格

Question

由于某些原因，尽管指定了我的范围，但我的列表会更新D2单元格，而不是更新它下面的后续单元格。例如，当我运行代码时，D2单元格将适当地更新为"A"。然后，当它进行另一个循环时，它不再移动到D3，而是用"B"再次更新D2。重复此操作，直到我只剩下从for循环生成的最后一个值。

附件是我的代码，很抱歉可读性我是新来的。

import requests
from googleapiclient.discovery import build
from google.oauth2 import service_account
consumer_key = 'XXXXXXXXXXXXXXXXXXXX'
SERVICE_ACCOUNT_FILE = 'keys.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds = None
creds = service_account.Credentials.from_service_account_file(
SERVICE_ACCOUNT_FILE, scopes=SCOPES)
SAMPLE_SPREADSHEET_ID = 'XXXXXXXXXXXXXXXXXXX'
service = build('sheets', 'v4', credentials=creds)

tickerlist= ['CUE', 'FB', 'AMD']


# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range = 'Sheet1!D2:D').execute()
values = result.get('values',[])
#open payload (this is where I'm getting my list from)
payload = {'apikey':consumer_key,
'periodType':'day',
'frequencyType':'minute',
'frequency':'30',
'period':'1',
'needExtendedHoursData':'false',
'startDate': '1620619200000'}
#for OPN loop
for i in tickerlist:
#definitions
endpoint = r'https://api.tdameritrade.com/v1/marketdata/{}/pricehistory'.format(i)
content = requests.get(url=endpoint, params = payload)
emptlist = []

#turn data into OPN JSON
data = content.json()
del data['symbol']
del data['empty']
for key in data:
for nested_dict in data[key]:
if 'volume' in nested_dict:
del nested_dict['volume']
if 'close' in nested_dict:
del nested_dict['close']
if 'high' in nested_dict:
del nested_dict['high']
if 'low' in nested_dict:
del nested_dict['low']
if 'datetime' in nested_dict:
del nested_dict['datetime']
emptlist += nested_dict.values()
llist = [emptlist]
#isolating first element
fopenlist = llist[0]
openlist = fopenlist[0]
trueopenlist = [[openlist]]

#plugging OPN into sheets

request = service.spreadsheets().values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range='Sheet1!D2:D', valueInputOption='USER_ENTERED', body={'values': actualopenlist}).execute()

非常感谢您的帮助。

Answer 1

答案

使用方法：spreadsheets.values.append将值附加到电子表格

输入范围用于搜索现有数据；表"；在该范围内。值将附加到表的下一行，从表的第一列开始。