Read and Write data from google sheets using Python for Generate API Test Automation Report

For all type of configuration, Project setup, and Codebase

https://dev.to/devsk1207/api-testing-using-python-introduction-configuration-and-installation-of-the-python-3b45
https://github.com/DevSK1207/API-Testing-Automation-using-Python Read and Write data from google sheets
Go to Google Cloud
Create Project If you haven't any project yet. Set project name and create project

After that go to Library. Search for Google Sheets API
and Google Drive API
and enable this two APIs.
Go to Credentials
and create Service account. Input Service account name and click on Create And Continue button. Your account is been created.

Click on edit icon it will redirect into testProject go to KEYS section , Click on ADD KEY and select Create new keys. Select json and click on Create, then JSON file will be downloaded.

Drag the JSON file and paste it into your project.

Copy the email from the json and give permission in to your Google Excel sheet.

Open Your project and go to terminal and paste this two command and run it

pip3 install gspread

pip3 install oauth2client

Create a Python file and Write this block of code

import gspread
from oauth2client.service_account import ServiceAccountCredentials


#Scope - 'https://www.googleapis.com/auth/spreadsheets' - See, edit, create, and delete all your Google Sheets spreadsheets
#Scope - 'https://www.googleapis.com/auth/drive' - See, edit, create, and delete all of your Google Drive files
scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

#create credential
credential = ServiceAccountCredentials.from_json_keyfile_name('/Users/testProject/secret_key/testProject-1cdc8e02ac28.json', scopes=scopes)

#use credential
file = gspread.authorize(credential)
workbook = file.open("Test_Case") #Mention excel file name
sheet =workbook.get_worksheet_by_id(2045268471) #mention excel sheet gid

#Read the excel
for cell in sheet.range('A2:A5'):
    print(cell.value)

print(sheet.acell('A3').value)
print(sheet.cell(4, 3).value)
print(sheet.row_value(2))

#update Excel 
sheet.update_cell(2,2, "Failed")

Youtube video - https://www.youtube.com/watch?v=hyUw-koO2DA

UPDATE TESTCASE EXCEL ACCORDING TO RESULT

class TestLoginUser:
    @pytest.mark.positive
    def test_login_user(self):
        url =f'{BASE_URI}/login'
        headers = {'Content-Type': 'application/json'}
        payload = {
            "email": "user@gmail.com",
            "password": "123456",
            "countryId": 1
        }
        response = requests.post(url, headers=headers, data=json.dumps(payload, indent=4))
        json_response = response.json()
        if response.status_code == 200:
            sheet.update_cell(2,3, "PASSED")
        else:
            sheet.update_cell(2,3, "FAILED")