Método de "atualização" do Google Sheets API Erro HTTP 400
Estou tentando criar um script python que lê e grava em uma planilha do google. Basicamente, copiei o script de início rápido do python emhttps://developers.google.com/sheets/quickstart/python e modificado usando a referência emhttps://developers.google.com/resources/api-libraries/documentation/sheets/v4/python/latest/.
Tudo funciona bem com o método "get" mostrado no script de início rápido. Eu posso ler a folha sem erros. Para usar "update" em vez de "get" (escreva na folha em vez de lê-la), removi o.readonly
parte do URL do escopo. Eu também substituí oget()
método comupdate()
e incluiubody
como argumento naupdate()
método com um objeto json que contém os valores codificados comjson.dumps
. Isso foi tudo de acordo com a segunda referência acima.
Eu sempre recebo um HttpError 400 por "dados inválidos".
Código:
import httplib2
import os
import json
from apiclient import discovery
import oauth2client
from oauth2client import client
from oauth2client import tools
try:
import argparse
flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
flags = None
# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'
def get_credentials():
"""Gets valid user credentials from storage.
If nothing has been stored, or if the stored credentials are invalid,
the OAuth2 flow is completed to obtain the new credentials.
Returns:
Credentials, the obtained credential.
"""
home_dir = os.path.expanduser('~')
credential_dir = os.path.join(home_dir, '.credentials')
if not os.path.exists(credential_dir):
os.makedirs(credential_dir)
credential_path = os.path.join(credential_dir,
'sheets.googleapis.com-python-quickstart.json')
store = oauth2client.file.Storage(credential_path)
credentials = store.get()
if not credentials or credentials.invalid:
flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
flow.user_agent = APPLICATION_NAME
if flags:
credentials = tools.run_flow(flow, store, flags)
else: # Needed only for compatibility with Python 2.6
credentials = tools.run(flow, store)
print('Storing credentials to ' + credential_path)
return credentials
def main():
"""Shows basic usage of the Sheets API.
Creates a Sheets API service object and prints the names and majors of
students in a sample spreadsheet:
https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
"""
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
'version=v4')
service = discovery.build('sheets', 'v4', http=http,
discoveryServiceUrl=discoveryUrl)
spreadsheetId = '1Wbo5ilhw68IMUTSvnj_2yyRmWJ87NP-lHdJdaPBmTGA'
rangeName = 'Class Data!A2:E'
body = json.dumps({'values': [[0,0,0,0,0]]})
result = service.spreadsheets().values().update(
spreadsheetId=spreadsheetId, range=rangeName, body=body).execute()
if __name__ == '__main__':
main()
Erros do console:
Traceback (most recent call last):
File "/Users/user/Dropbox/python/jobs/test.py", line 73, in <module>
main()
File "/Users/user/Dropbox/python/jobs/test.py", line 69, in main
spreadsheetId=spreadsheetId, range=rangeName, body=body).execute()
File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/oauth2client/util.py", line 135, in positional_wrapper
return wrapped(*args, **kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/googleapiclient/http.py", line 832, in execute
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1Wbo5ilhw68IMUTSvnj_2yyRmWJ87NP-lHdJdaPBmTGA/values/Class%20Data%21A2%3AE?alt=json returned "Invalid value at 'data' (type.googleapis.com/google.apps.sheets.v4.ValueRange), "{"values": [[0, 0, 0, 0, 0]]}"">