Cara menggunakan ezsheets github

You could use an already-built Google Sheets Add-On

But if you’re adding third party software to your spreadsheet anyway, there’s a better way

The reason you want to push Github data to Google Sheets is probably that you want to make this data accessible to team members who are comfortable in a spreadsheet

If that's the case, you should try out Actiondesk. Actiondesk is the easiest way for startup teams to make better decisions with their data. You can access your company key data without code or writing SQL, you'll just need basic spreadsheet skills.

Actiondesk looks like a spreadsheet and works like a spreadsheet. Users used to Google Sheets and Excel will be able to learn Actiondesk very quickly.

While writing a script or using a Google Sheets Add-On can be a good one-off solution, they're usually not durable. The overwhelming feedback we got from the market is that these solutions are not reliable and you always have to debug them yourself.

If you're looking for a more solid solution, check out Actiondesk, you can try it for free.

You can even easily get started with one of our many Actiondesk Github templates.

Table of Contents

• Sample Google Sheet
• Step 1: Create a new project on the Google Cloud Console
• Step2: Add the API's to your project
• Step 3: Create a Service Account
• Step4: Get the Credentials
• Step5: Add the Google Sheets API
• Step6: Install the Gspread Library
• Step7: Add the service account email to your Google Sheet
• Step8: Write the Python script
• How do I use Google Sheets API in Python?
• Can Python automate Google Sheets?
• Can I use Google Sheets as a API?
• Can Python pull data from Google Sheets?

Welcome!

In this blog, I will be showing you how to read and write data in Google Sheets using Python and the Google Sheets API.

If you prefer to watch a video instead of reading, I’ve got you covered:

Sample Google Sheet

The Google sheet that I will be using for this blog contains the following details:

1. Name of the Student
2. Roll number of the student
3. Score in math
4. Score in Spanish

We will interact with this data in the Google Sheet using python and the Google Sheets API. If you want to work with the Google Sheet, click here.

Step 1: Create a new project on the Google Cloud Console

To interact with the Google Sheet using python and the Google Sheet API, we have to create a new project on the Google Cloud Console. We have to invoke the Google Sheets API and use the credentials received to interact with the Google Sheet using python to create the project.

To go to the Google Cloud Console, all you have to do is open a new tab in your browser and type in Google Cloud Console or you can click here.

On reaching the Google Cloud Console, you will see a screen shown below.

If you have already created projects on the Google Cloud console, you will see something like this. To create a new project, you have to click on the name of the current project you are in at the top.

You will see a list of projects you have created and the new project button on clicking the button.

On clicking the new project button, you will see the following.

Here at the top, you can see the number of projects left in your quote. On creating a new project, you have to name your project and then select an organization (option). After adding the required project details, go ahead and click on CREATE.

You will be taken to the project dashboard to create the project, which will look like this.

Step2: Add the API's to your project

Now that we have successfully created the project, we can add the API. We will be adding the Google Drive API and the Google Sheets API for this blog.

To add APIs to your project, click on Go to API's overview. On clicking Go to APS's overview, you will be taken to the API's dashboard. To add APIs to the project, you must click on the library button to show all available APIs.

This is the API library to search and add API to your project. The first API we will add to this project is the Google Drive API. After searching for the API, go ahead and enable the API.

Step 3: Create a Service Account

Now that we have enabled the API, it's time to get some credentials that we will be using to interact with the Google Sheet. To get the credentials, follow these steps:

1. Go to the credentials section by clicking on Credentials.

2. At the top, click on Create Credentials.

3. On clicking Create Credentials, you will be provided with various options credentials, but we will use a service account to interact with the Google Sheet for this blog.

So go ahead and click on Service account.

4. On creating a service account, you will have to fill in some details for the service account.

Here I have added the service account's name. After entering all the details, go ahead and click CREATE AND CONTINUE.

5. Now that the account is created, it's time to assign a role to the account.

Since our primary goal is to use this account to interact with the Google Sheet, we will give it an Editor role. The editor role allows you to read and write data to the sheet, whereas the viewer role will only allow you to view the data in the Google Sheet.

After assigning a role to the service account, click on continue.

After putting in all the details, go ahead and click on Done.

Step4: Get the Credentials

Now that we have successfully set up the service account, it's time to get the credentials. To do that, follow these steps:

1. Click on the Service account you just created.

2. At the top, click on KEYS.

3. Click on ADD KEY and click on Create a new key.

4. On creating the key, you will be provided with options for the key type. Just go ahead and click on JSON.

After clicking JSON, go ahead and create the key.

On clicking CREATE, a JSON file will get downloaded to your computer. Be careful with the JSON file, as it contains some important details that we will need to interact with the sheet. We will be using that JSON files credentials to work with the Google Sheet.

Step5: Add the Google Sheets API

This is the last step in the setup process. We have to follow the steps we did previously to add the API.

1. Go to the API and overview.
2. Go to Enable API and services.
3. Please search for the Google Sheets API and enable it.

Step6: Install the Gspread Library

Now that we have everything ready, it's time to install the library that we will use. For this blog, we are going to be using the gspread library. To do that, follow these steps:

1. Go to your Command Prompt or Terminal.
2. Type in the following command.

pip install gspread oauth2client

3. The library will be installed and ready for use in a few seconds.

Step7: Add the service account email to your Google Sheet

Before writing our Python script to read and write data in the Google Sheet, we will first add the service account to the Google Sheet. To do that, follow these steps:

1. Go to the JSON file you created and get the service account email address.
2. After getting the email address, go to your Google Sheet and click on the SHARE button.

3. Here, you can paste in the email address you just copied.

Step8: Write the Python script

Now that we have everything ready, go ahead and create a new folder and store the JSON file. Also, please create a new Python file in which we will be writing the scripts to read and write data from the Google Sheet.

After creating your folder, open it in your editor. I use Visual Studio code and recommend you use the same. If you want to download Visual Studio Code, click on this link.

Here I have both my files stored in one folder.

Let's get coding.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint as pp

Here I have started importing the gspread library using the import command followed by the library name (gspread). Next, I am importing the service account credentials from the service account. Next, I have imported the pprint library. This library is also a pretty print used to customize your output.

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

Here we have added some scope that we require to work with the Google Sheet, copy them into your python file.

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json",scope)client = gspread.authorize(creds)

Now we will be getting the credentials from the JSON file using the from_json_keyfile_name function, in which we are going to be passing the name of the JSON file followed by the scopes we just declared. After getting the creds, we will authorize them using the gspread.authorize function.

sheet = client.open("Students Marks ").sheet1   
data = sheet.get_all_records() 
pp(data)

Here we have opened the sheet using the client.open function and passed the name of the sheet as the argument, and specified the sheet we want to open
(.sheet1).

Next, to get all data from the Google Sheet, we use the sheet.get_all_records function and store in a variable. On getting all the data from the Google Sheet, we print it.

Here you can see that all the data from the Google Sheet has been successfully printed.

row = sheet.row_values(3)
pp(row)
col = sheet.col_values(3)
pp(col)

Now that we learned how to get all the data from the sheet and print it out let us get a specific row of the Google Sheet.

To do that, we are going to be using the sheet.row_values function that takes the row number as an argument. Here I have passed the three as a parameter(which means I want to print the 3rd row) and assigned it to a variable. If I go ahead and print it out, you can see that the third row has been printed.

Here, the third row from the Google Sheet has successfully been printed. To print a specific column from the sheet, we follow the same steps but pass the column number in the col_values function.

On successful execution, you can see that the third column of the Google Sheet has successfully been printed.

cell = sheet.cell(1,2).value 
pp(cell)

Next, we will print the value of a particular cell in the Google Sheet. To do that, we will be using the .cell function in which we pass the row and the column number of the cell you want to print. Here I have passed 1,2 in the .cell function followed by .value function that gets the cell's value.I have printed them out on getting the values using pp(cell).

On successful execution, the cell value has been printed.

We have learned how to get data from the Google Sheet. Now we are going to set values in the Google Sheet. We will add a row to the Google Sheet and delete a row in the Google Sheet.

insertRow = ["Zayn","Malik",12,20]sheet.insert_row(insertRow,6)print("The row has been added")

Firstly, I have declared the row I want to add to a Google Sheet using a list. In this list, I have stored the first and last name of the student, followed by the marks scored in maths and Spanish.

Now that we have the row that we want to insert ready, we will be using the .insert_row() function to insert the row into the Google Sheet. Inside this function, we will be passing the data for the row (insert_Row list), followed by the row number where you want to add the list. In the end, I have printed a confirmatory message that tells us that row has been added to the Google Sheet.

This is the sheet before we run the insert_row function.

Here you can see that the message has been printed on successful execution of the script.

If we go back to the sheet, we can see that the row has been added to the Google Sheet.

Now that we have learned how to add rows to the Google Sheet, we will delete rows from the Google Sheet.

sheet.delete_row(2)pp("The row has been deleted")

To delete a row from the sheet, we will be using the .delete_row() function in which we will be passing the row number you want to delete. So here I have passed two, which means using the delete_row function, I want to delete the second row of the Google Sheet. After declaring the delete_row function, I am printing a confirmatory message that tells us that the row has been deleted from the Google Sheet.

This is the Google Sheet before we run the delete_row function.

Here you can see the message has been printed on successful execution of the script.

On successful execution, the row has been deleted from the Google Sheet.

Summary

We saw how you could read and write data in Google Sheets using python and the Google Sheets API. I hope you have understood how to read and write data in Google Sheets.

Feel free to reach out if you have any issues/feedback at .

How do I use Google Sheets API in Python?

Reading and writing to Google Spreadsheets using Python.

Head over to the Google API Console..

Create a new project by selecting My Project -> + button..

Search for 'Google Drive API', enable it..

Head over to 'Credentials' (sidebar), click 'Create Credentials' -> 'Service Account Key'.

Can Python automate Google Sheets?

It's been possible to automate Google Sheets via the Google Sheets API for many years and there are multiple Python packages available that wrap this API: there's gspread, pygsheets, ezsheets, and Google's own google-api-python-client.

Can I use Google Sheets as a API?

The Google Sheets API lets you read, write, and format Google Sheets data with your preferred programming language, including Java, JavaScript, and Python.

Can Python pull data from Google Sheets?

We have successfully Read Data from the Google Sheets using Python. For creating the GUI of the project we used Tkinter Module. And we have done a little setup and using oauth2 library, we have established connection with the google sheet. In this way now we know how to read data from a google sheet using Python.