Connect API to Sheets using Python

Fabrisimo 0
Viewed 50

I have managed to read from the sheets separately and I have managed to write to them, on the other hand I have access to the API, I'm just trying to put it all together and by the way learn Python, could someone help me?

I can't get the API response to be written in the sheet, it doesn't give me any error, it only tells me that it has connected but that it hasn't updated any column or any cell

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

#-------------------------------------------------------------------

def temilla ():

  BASE_URL = "https://company.com"

  token = 'RYE_'

  #headers = {'Content-Type':'application/x-www-form-urlencoded','Authorization': "Bearer {}".format(token)}
  PARAMS = {"employee_ids":"1007",
           #"employee_ids":"1388",
           #"business_unit_ids":"13",
           "start_date":"08/07/2022",
           "end_date":"09/07/2022"
           }
  headers = {"Content-Type": "application/json",'Authorization': "Bearer {}".format(token), "Api-version": "1.0"}
  response = requests.get(BASE_URL, params = PARAMS, headers = headers )

  result = [response.content]

  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 = '146456464646464'

service = build('sheets', 'v4', credentials=creds)

sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                         range="Horarios!A1:J20").execute()

print(result)

values = result.get('values', [])

temilla()

Cosilla = temilla()
#Cosilla = [["DIA",1],["AÑO",2022],["MES",9],["EMPLEADO","MARIANO"],["ACTIVO",True]]

request = sheet.values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                    range="Prueba-Escritura!a1:xa", valueInputOption="USER_ENTERED", body= 
{"values":Cosilla}).execute()

#print(result)
#print(values)
print(request)

The response that the API gives me is similar to the following

b'[{"shift_id":2400298,"business_unit_id":10,"business_unit":"alguno","employee_id":1007,"employee_code":"11111111","entry":"2022-08-07T11:30:00","exit":"2022-08-07T15:30:00"},{"shift_id":2400299,"business_unit_id":10,"business_unit":"alguno","employee_id":1007,"employee_code":"1111111","entry":"2022-08-07T19:00:00","exit":"2022-08-07T23:00:00"}]'

google-sheets-api google-sheets python
1 Answers

I believe your goal is as follows.

  • You want to put the values from response = requests.get(BASE_URL, params = PARAMS, headers = headers ) to Google Spreadsheet.
  • Sample value of it is [{"shift_id":2400298,"business_unit_id":10,"business_unit":"alguno","employee_id":1007,"employee_code":"11111111","entry":"2022-08-07T11:30:00","exit":"2022-08-07T15:30:00"},{"shift_id":2400299,"business_unit_id":10,"business_unit":"alguno","employee_id":1007,"employee_code":"1111111","entry":"2022-08-07T19:00:00","exit":"2022-08-07T23:00:00"}].
  • You want to achieve this using googleapis for python.
  • You have already been able to put and get values to Spreadsheet using Sheets API.

In this case, how about the following modification?

Modified script:

# Retrieve values from requests as JSON.
response = requests.get(BASE_URL, params = PARAMS, headers = headers ) # This is from your script.
obj = response.json()

# Convert JSON to 2 dimensional array.
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)

# Put values to Spreadsheet using googleapis.
SAMPLE_SPREADSHEET_ID = "###" # <--- Please set your Spreadsheet.
request = service.spreadsheets().values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Prueba-Escritura!a1", valueInputOption="USER_ENTERED", body={"values": [list(keys), *ar]}).execute()
print(request)
  • In this modification,
    • Values from response = requests.get(BASE_URL, params = PARAMS, headers = headers ) is converted to JSON. And, using this object, an array for putting into Spreadsheet is created.
    • Values are put from cell "A1" of the sheet of "Prueba-Escritura". If you want to change this, please modify range="Prueba-Escritura!a1".
    • The header values are keys = obj[0].keys(). If you want to change this order, please modify this. If you want to use the specific order, please put the header row here like keys = ['shift_id','business_unit_id','business_unit',,,].
    • This script puts the values including the header row. If you don't want to include the header row, please modify [list(keys), *ar] to ar.

Reference:

Tanaike0
Related