Stacks:

Tools:

---

About the application   |    Creating your application   |    Gspread   |    Stacks   |    References   |   

About the application

Using the Google spreadsheet and the 'gspread' API, an automation was made using the Python language for reading, analyzing and inserting data in a specific data spreadsheet available here. I will take the opportunity to leave the entire documented process and examples so that anyone can do it. Use your creativity and experience. Come on!

How to run the program?

• If the system does not have python 3, install python 3 via the link: Download Python 3

  

• Open CMD, type or copy and paste " pip install gspread " and press Enter. [Obs: The execution has to be as administrator.]

  

• Open the file folder CRUDGoogleSheets and in the address bar type " powershell ", in powershell window type " python main.py " and press Enter. DONE ! Link: Access to spreadsheets

  

  Run_Code.mp4

---

Creating your application:

The file available here can be reused as long as you change the SpreadSheets access addresses. We'll see that in the topics below.

Here we will show you step by step from creating a project on Google Cloud Platform to use the APIs to running the Python program interacting with GoogleSheets.

| Google Cloud Platform |

• 1st Step: Creating a project on Google Cloud Platform

  Link access: Access Google Cloud Platform

  1st.Step_.Creating.a.project.on.Google.Cloud.Platform.mp4

• 2nd Step: Enable the APIs

  2nd.Step_.Enable.the.APIs.mp4

• 3rd Step: Creation of credentials to generate the spreadsheet access certificate using a key.

  3rd.Step_.Creation.of.credentials.to.generate.the.spreadsheet.access.certificate.using.a.key.mp4

• 4th Step: Create a key to access the spreadsheet.

  4th.Step_.Create.a.key.to.access.the.spreadsheet.mp4

| GoogleSheets |

• 5th Step: Create GoogleSheets and configure the communication between the spreadsheet and the Python code using an email contained in the JSON file. Let's see:

  5th.Step_.Create.GoogleSheets.and.configure.the.communication.between.the.spreadsheet.and.the.Python.mp4

| PyCharm |

• 6th Step: Download PyCharm

  Download

  

• 7th Step: Open PyCharm, New Project and Choose virtual environment venv

  7th.Step_.Open.PyCharm.New.Project.and.Choose.virtual.environment.venv.mp4

• 8th Step: Install Gspread API in the virtual environment of the project so that it is possible to import into Python. Wait for installation ...

  8th.Step_.Install.Gspread.API.mp4

• 9th Step: Rename JSON file of the 4th step and insert in the project that is in PyCharm.

  9th.Step_.Rename.JSON.file.of.the.4th.step.and.insert.in.the.project.that.is.in.PyCharm.mp4

• 10th Step: Start writing Python code. Import Gspread, configure it to access the spreadsheet using the JSON file and the spreadsheet's URL key. Come on:

  #importing gspread library
  import gspread
  
  #here, access to the credentials file, which contains all the data for the connection. Python <->GoogleCloudPlatform<->Spreadsheet
  gc = gspread.service_account(filename='credentials.json') 
  
  #specifies the spreadsheet using the URL key.
  sh= gc.open_by_key('1TDlSR0yofAQsg7hpYjWUPbCXihbJAfIeySQdOYAet8I') 

  10thStep_.Start.writing.Python.code.Import.Gspread.config.JSON.file.and.the.spreadsheet.s.URL.key.mp4

• 11th Step: Proceeding with the same code. Create an object directed to the sheet you wanted to work with. We will start by accessing sheet1, soon we will see how to create and access other sheets.

  #....
  worksheet = sh.sheet1   #Access by sheet name.
  
  #OR
  
  worksheet = sh.get_worksheet(0) # Access by the index of each sheet.

  

• Done ! We can now play with all the CRUD functions in the worksheet through the created object called here as "worksheet."

GSPREAD

Gspread is an API that allows access to Google Spreadsheet, being for.

Following the steps of previous configurations, the starting point for using the Gspread functions is from this code:

import gspread

gc = gspread.service_account(filename='credentials.json')

sh= gc.open_by_key('1TDlSR0yofAQsg7hpYjWUPbCXihbJAfIeySQdOYAet8I')

worksheet = sh.get_worksheet(0)
 

• Creation

...
                                           
worksheet = sh.add_worksheet(title="A worksheet", rows="25", cols="7")

#tittle = Spreadsheet name
#rows = Number of rows
#cols = Number of columns 

• Read

...

worksheet = sh.get_worksheet(0)
  
val = worksheet.acell('B1').value
                                                

OR

 ...

worksheet = sh.get_worksheet(0)
   
val = worksheet.cell(1, 2).value
                                                 

• Update

...

worksheet = sh.get_worksheet(0)
  
val = worksheet.cell(1, 2).value
                                                

• Delete

...

worksheet = sh.get_worksheet(0)
 
worksheet.delete_row(10)
                                               

Stacks

Linguagem

• Language chosen to build this challenge.

API

Gspread

• API used to access Google spreadsheets. Enabling actions of a CRUD through the Python language.

Sheets

• Spreadsheet used to manipulate the information

Versionameto

• Tool used for code versioning.

IDE

• Tool used to build the project.

BIBLIOGRAPHY:

1. PYTHON ENGINEER. Google Sheets and Python - Tutorial, 2020. Available in: <https://youtu.be/T1vqS1NL89E> . Access in: 29 de jan. de 2021.

2. GSPREAD. Read the Docs, 2021. Home page. Available in: <https://gspread.readthedocs.io/en/latest/> . Access in: 29 de jan. de 2021.

---

I thank Tunts for providing me this challenge!

I put myself in this duty, and I did it!