Cara menggunakan pygsheets write to sheet

Pygsheets is a simple python library that can be used to automate Google Sheets through the Google Sheets API. An example use of this library would be to automate the plotting of graphs based on some data in CSV files that we can export to Google Sheets. So, let’s proceed further.

How do pygsheets work with Google Sheets?

• The Client creates and accesses spreadsheets.
• The spreadsheet is the class used to represent a Google Sheet.
• Each spreadsheet has multiple ‘Worksheets’.
• The data inside the worksheets can be accessed through ‘Cell’ objects which supports formatting, formulas, etc.

Step 1: Enable APIs for Google Sheets and Google Drive

A.  Go to Google Developers Console and create a new project. 

B. Enter a name for the project. You may leave ‘Location’ as ‘No Organization’. Press Create.

C. Now, we will enable the Google Sheets and Google Drive APIs. To do so, click on ‘ENABLE APIS AND SERVICES’. 

D. Now enter ‘Google Sheets API’ in the search bar, click on the ‘Google Sheets API’ option, and press ‘ENABLE’.

E. Similarly, search for and enable the ‘Google Drive API’.

Step 2: Create a Service Account and fetch credentials

The Service Account is what we will be using to make our API calls to edit the spreadsheets. It is associated with the email id of the Google Developers Account that we used to create our project with.

A. To create one, click on ‘CREATE CREDENTIALS’.

B. Select ‘Google Sheets API’ in the ‘Select an API’ section, ‘Application Data’ and ‘No, I’m not using them’ in the following questions. Click ‘NEXT’.

C. Enter the display name and name for the service account.

D. Now we will enter the access level for the service account. Click on ‘Select a Role’ and select ‘Editor’ under the ‘basic’ section. Press ‘CONTINUE’.

E. You may leave the ‘Grant users access to this service account’ section as blank and now, press ‘DONE’.

F. Now click on the service account that was just created.

G. Go to the ‘KEYS’ section and click on ‘ADD KEY’. Select ‘Create new key’.

H. Select ‘JSON’ to download the keys in the JSON file format. Click on ‘CREATE’.

I. The above file will be downloaded into your system. Do not share the keys with anyone and keep them safe. Place the file in the folder in which you want to create your python project.

Step 3: Add Service Account as an editor

Create a new Google Sheet in your Google account, or you can even use an existing one. Simply add the service account as one of the editors in that sheet. You will find the Service Account email in your keys file as “client_email”.

Step 4: Authorize pygsheets 

A. Now create a main.py file and place it in the same folder as your Keys file.

B. Install Required Library.

pip install pygsheets

C. Now, write the following code in the main.py file to verify the successful authorization of pygsheets.

Python3

# Importing required library

import pygsheets

# Create the Client

# Enter the name of the downloaded KEYS 

# file in service_account_file

client= # Importing required library0=# Importing required library2# Importing required library3

# Importing required library5

# Importing required library6

# Importing required library7

# Importing required library8

# Importing required library9

import0import1

The above code should print the name of the spreadsheet that we shared with the service account in step 3. Hence, we have successfully linked our Google Sheet with the Service Account and now, we will use the Client to edit the spreadsheet.

Step 5: Editing the spreadsheet

A. Now, we will look at some commands to edit our spreadsheet through Python code.

Python3

# Importing required library

import pygsheets

# Create the Client

client= # Importing required library0=# Importing required library2# Importing required library3

pygsheets4

pygsheets5= pygsheets7pygsheets8pygsheets9 0# Importing required library3

 3

 4=  6 7 8 9# Importing required library3

# Create the Client2

# Create the Client4

# Create the Client5# Create the Client6# Create the Client7# Create the Client8 8# Enter the name of the downloaded KEYS 0# Enter the name of the downloaded KEYS 1= # Enter the name of the downloaded KEYS 3

# Enter the name of the downloaded KEYS 5

# Enter the name of the downloaded KEYS 6

# Enter the name of the downloaded KEYS 7# Enter the name of the downloaded KEYS 8# Enter the name of the downloaded KEYS 9# file in service_account_file0# file in service_account_file1# file in service_account_file2# file in service_account_file3

# file in service_account_file4# file in service_account_file5# file in service_account_file6# file in service_account_file1# file in service_account_file8# file in service_account_file3

# file in service_account_file4# file in service_account_file5client2client3client4

client6

# Create the Client5client8# Create the Client7# Create the Client8 8# Enter the name of the downloaded KEYS 0# Enter the name of the downloaded KEYS 1= =5

# Enter the name of the downloaded KEYS 7=7# Enter the name of the downloaded KEYS 9=9# file in service_account_file1# Importing required library01# file in service_account_file1=9# file in service_account_file1# Importing required library05# file in service_account_file1# Importing required library07# Importing required library08

# Importing required library10

# Importing required library11# Importing required library12 8# Importing required library14# Importing required library15# Importing required library16 8# Importing required library18# Importing required library19# Importing required library20# Importing required library3