How to automate your Google Spreadsheet using Python

In the previous article, you learned how to generate a credential file to access Google Spreadsheet and share a Google Spreadsheet with your Google Cloud service account. This article will provide a simple Python script for downloading Google Spreadsheet to your local drive and updating it to the latest version.

The Google Spreadsheet used in this article will be a simple user authentication database for our Telegram Bot. It will also be used in our Discord Bot.

Prerequisite: You have put your credential JSON file into your Python project directory. You must install oauth2client and gspread library on your Python environment. You may install these libraries by

pip install oauth2client
pip install gspread

Create a Google Spreadsheet like the image below using the previous example as a reference.

Step 1: Include the libraries by

from oauth2client.service_account import ServiceAccountCredentials
import gspread

Step 2: Define variables at the beginning. Replace your_credential_json_file with your credential JSON file downloaded before and your_google_spreadsheet_docid with your Google Spreadsheet ID. The Document ID (docid) can be found on the URL of your Google Spreadsheet. In my case, it is 1BVg-WgsDEjembne02KQ7F7U3EQLq33BG7uuybmFAZXs.

scope = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"] #https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('your_credential_json_file', scope)
gs_client = gspread.authorize(credentials)

result_txt_location = 'whitelisted_user.tsv'
docid = "your_google_spreadsheet_docid"

In my case, it is

scope = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"] #https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('genuine-amulet-386508-e126616283cf.json', scope)
gs_client = gspread.authorize(credentials)

result_txt_location = 'whitelisted_user.tsv'
docid = "1BVg-WgsDEjembne02KQ7F7U3EQLq33BG7uuybmFAZXs"

Step 3: Try to write it to a file called whitelisted_user.tsv by

spreadsheet = gs_client.open_by_key(docid)

export_file = spreadsheet.export(format=gspread.utils.ExportFormat.TSV)

f = open(result_txt_location, 'wb')
f.write(export_file)
f.close()

Step 4: Try to read the file whitelisted_user.tsv from Google Cloud by

uid=[]

with open(result_txt_location, 'r', encoding='utf8' ) as f:
    for line in f:
        filtered_data = line.replace("\n","").split('\t')
        uid.append(filtered_data[0])  
print(uid)    

Step 5: Try to add a user id by

uid.append('12345678')  
print(uid) 

Step 6: Try to write the latest whitelisted user lsit to whitelisted_user.tsv by

count = 0
with open(result_txt_location, 'w', encoding="utf8") as f:
    while count < len(uid):
        data = "".join([uid[count],'\n'])
        #print(data)
        f.write(data)
        count += 1  

Step 7: Write the latest whitelisted user list to Google Spreadsheet by

content = open(result_txt_location, 'rb').read()
gs_client.import_csv(docid, content)

Step 8: Go to Google Cloud and find your Google Spreadsheet is updated to the latest version.

Congratulation, you have learned some preliminary skills to read and update Google Spreadsheets using Python. You may add this Python code to automate your program.

In the following articles, you will learn how to use your Telegram Bot and Discord Bot to update this Google Spreadsheet.

The complete Python script

from oauth2client.service_account import ServiceAccountCredentials
import gspread


scope = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"] #https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('genuine-amulet-386508-e126616283cf.json', scope)
gs_client = gspread.authorize(credentials)

result_txt_location = 'whitelisted_user.tsv'
docid = "1BVg-WgsDEjembne02KQ7F7U3EQLq33BG7uuybmFAZXs"

spreadsheet = gs_client.open_by_key(docid)

export_file = spreadsheet.export(format=gspread.utils.ExportFormat.TSV)

f = open(result_txt_location, 'wb')
f.write(export_file)
f.close()

uid=[]

with open(result_txt_location, 'r', encoding='utf8' ) as f:
    for line in f:
        filtered_data = line.replace("\n","").split('\t')
        uid.append(filtered_data[0])  
print(uid)    

uid.append('12345678')  
print(uid)  

count = 0
with open(result_txt_location, 'w', encoding="utf8") as f:
    while count < len(uid):
        data = "".join([uid[count],'\n'])
        #print(data)
        f.write(data)
        count += 1
                               
content = open(result_txt_location, 'rb').read()
gs_client.import_csv(docid, content)