Read and Write data from google sheets using Python for Generate API Test Automation Report
Published: 2022-12-05 09:54:19

For all type of configuration, Project setup, and Codebase
1. https://dev.to/devsk1207/api-testing-using-python-introduction-configuration-and-installation-of-the-python-3b45
2. https://github.com/DevSK1207/API-Testing-Automation-using-Python
**<u>Read and Write data from google sheets</u>**
01. Go to [Google Cloud](https://console.cloud.google.com/getting-started?project=pythonapireadandwrite&supportedpurview=project)
02. Create Project If you haven't any project yet. Set project name and create project
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mqfk3r8e5izkurqeill9.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/14xt39n5e6shaic19n37.png)


03. After that go to [Library](https://console.cloud.google.com/apis/library?project=pythonapireadandwrite&supportedpurview=project). Search for [Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com?project=pythonapireadandwrite&supportedpurview=project)
and [Google Drive API](https://console.cloud.google.com/apis/library/drive.googleapis.com?project=pythonapireadandwrite&supportedpurview=project)
and enable this two APIs.

04. Go to [Credentials](https://console.cloud.google.com/apis/credentials?project=pythonapireadandwrite&supportedpurview=project)
and create Service account. Input Service account name and click on Create And Continue button. Your account is been created. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wy91sg83a9yxx6ff92xi.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7wtbi5j4cc8ubqk5lv1k.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fefvyx0kb9qye421e652.png)

04. Click on edit icon it will redirect into [testProject](https://console.cloud.google.com/iam-admin/serviceaccounts/details/101841553790448634301;edit=true/keys?project=pythonapireadandwrite&supportedpurview=project)
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tznxm43vrcngzz91loux.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w4qvthe4ca3mwyl6bwto.png)

05. Drag the JSON file and paste it into your project. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/epagr5x6dipiyuvt8jgd.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0f84if3fh6l6ckg0u41h.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/muq0eukgp1accxehsuxd.png)

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

```
pip3 install gspread
```

```
pip3 install oauth2client
```
08. 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

**<u>UPDATE TESTCASE EXCEL ACCORDING TO RESULT</u>**

```
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")
```