thanks to another user I have managed to connect an API through Python that serves me data and google sheets, the way to format the Json that this user taught me, for which I am very grateful he is not writing me data, I understand that the format of data is not designed for it, could someone tell me how to change the work that is done to json so that it is interpreted by the sheets API?
from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2 import service_account
import requests
import json
#-------------------------------------------------------------------
BASE_URL = "https://apiserver.com/api"
token = '`xxxxxxxxxxx'
#headers = {'Content-Type':'application/x-www-form-urlencoded','Authorization': "Bearer {}".format(token)}
PARAMS = {#"employee_ids":"xxxx",
#"employee_ids":"xxxx",
"business_unit_ids":"1",
"start_date":"09/01/2022",
"end_date":"09/14/2022"
}
headers = {"Content-Type": "application/json",'Authorization': "Bearer {}".format(token), "Api-version": "1.0"}
response = requests.get(BASE_URL, params = PARAMS, headers = headers )
obj = response.json()
result = [response.content]
keys = obj[0].keys()
ar = []
for o in obj:
temp = []
for k in keys:
temp.append(o[k] if k in o else "")
ar.append(temp)
#print(response.content)
#--------------------------------------------------------------------------
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)
#--------------------------------------------------------------------------
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
SAMPLE_SPREADSHEET_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range="them!A1:J20").execute()
print(result)
values = result.get('values', [])
request = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range="sheet!a1:xa", valueInputOption="USER_ENTERED", body={"values": [list(keys), *ar]}).execute()
print(request)
I leave a sample of the answer with which it works and the answer with which it does not work
this yes
[{"shift_id":2400298,"business_unit_id":10,"business_unit":"XXXXXXXX","employee_id":XXXXXXXX,"employee_code":"XXXXXXXX","entry":"2022-08-07T11:30:00","exit":"2022-08-07T15:30:00"},{"shift_id":2400299,"business_unit_id":10,"business_unit":"XXXXXXXX","employee_id":XXXXXXXX,"employee_code":"XXXXXXXX","entry":"2022-08-07T19:00:00","exit":"2022-08-07T23:00:00"},{"shift_id":2402414,"business_unit_id":10,"business_unit":"XXXXXXXX","employee_id":XXXXXXXX,"employee_code":"XXXXXXXX","entry":"2022-08-08T11:30:00","exit":"2022-08-08T16:00:00"},{"shift_id":2402415,"business_unit_id":10,"business_unit":"XXXXXXXX","employee_id":XXXXXXXX,"employee_code":"XXXXXXXX","entry":"2022-08-08T19:00:00","exit":"2022-08-08T23:30:00"},}]
not this one
[{"employee_id":XXXXXX,"employee_code":"XXXXXX","name":"XXXXXX","last_name":"XXXXXX","gender":"Undefined","marital_status":"Not_Defined","document_id":"XXXXXX","document_type":"Select From Below","authorization_type":"Select From Below","registration_time":"2022-09-14T00:00:00","address_road_type":"Select From Below","email":"XXXXXX","telephone1":"XXXXXX","bank_account":"XXXXXX","disability_level":"Select From Below","academic_education":"0","status":"Select From Below","level":"Select From Below","scale":"Select From Below","temporary_contract_reason":"Select From Below","additional_information":"","custom_field_collection":[{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":"UNDEFINED","value_id":"0"},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"Undefined","value":""},{"name":"","value":""},{"name":"","value":""},{"name":"","value":""},{"name":"","value":""},{"name":"","value":""},{"name":"","value":""},{"name":"","value":"UNDEFINED","value_id":"0"},{"name":"","value":"UNDEFINED","value_id":"0"},{"name":"","value":"UNDEFINED","value_id":"0"},{"name":"","value":"UNDEFINED","value_id":"0"},{"name":"","value":"UNDEFINED","value_id":"0"},{"name":"","value":"UNDEFINED","value_id":"0"}]}]
I would appreciate the help
the terminal response is like this and it only lets me see up to a certain point, it does not update the sheet, I understand that when the Json response is formatted to send it to the Google Sheet API it does not pass it correctly so that be interpreted
instead of getting this output, committing the write to the sheet
{'spreadsheetId': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 'updatedRange': 'Empleados!A1:O528', 'updatedRows': 528, 'updatedColumns': 15, 'updatedCells': 7920}
i get this other error
Traceback (most recent call last):
File "C:\Proyectos Python\Empleados-detalle-fconnect.py", line 74, in <module>
range="Empleados-detalle!a1:xa", valueInputOption="USER_ENTERED", body={"values": [list(keys), *ar]}).execute()
File "C:\Users\xxxxx\AppData\Local\Programs\Python\Python310\lib\site-packages\googleapiclient\_helpers.py", line 130, in positional_wrapper
return wrapped(*args, **kwargs)
File "C:\Users\fabra\AppData\Local\Programs\Python\Python310\lib\site-packages\googleapiclient\http.py", line 938, in execute
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/xxxxxxxxxxxxxxxxxxxxxxxx/values/Empleados-detalle%21a1%3Axa?valueInputOption=USER_ENTERED&alt=json returned "Invalid values[1][21]: list_value { values {
struct_value {
fields {
key: "name"
value {
string_value: "Undefined"
}
}
fields {
key: "value"
value {
string_value: "UNDEFINED"
}
}
fields {
key: "value_id"
value {
string_value: "0"
}
}
Of course, I leave 3 images of how the written answer should look on the sheet, thanks again for your patience and help
