💎 Program is used to COUNT YOUR SUBSCRIPTIONS SPENDING💰 by using GMAIL and GOOGLE SHEETS APIs.
💲 To begin with, open config file with all values⚙️:
# Open Config file with all configuration
with open(os.path.join('configs', 'config.json')) as file:
config = json.load(file)💲 Then, we should create services of our Google and Spreadsheets APIs. Before that create variables with values for creation services. Where CLIENT_SECRET_FILE is credentials of program in our project in the Google Cloud. And SCOPES is list of string values to interact with APIs.
# Values to create services
creds = None
CLIENT_SECRET_FILE = os.path.join('configs', 'client_secret.json') # get file path
SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://mail.google.com/']💲 After that, create services by using function create_service():
# Create gmail and sheet services
sheet_service = create_service(CLIENT_SECRET_FILE, "sheets", "v4", SCOPES, config["gmail"], logger)
gmail_service = create_service(CLIENT_SECRET_FILE, "gmail", "v1", SCOPES, config["gmail"], logger)💲 Then store all values in variables:
# All values to interact with program
table_id = config["spreadsheetID"]
sheet_name = config["sheetName"]
column_music = config["columnAppleMusic"]
column_cloud = config["column_iCloud"]
column_others = config["other_column"]
dollar_column = config["dollar_column"]
uah_column = config["UAH_column"]
min_index = config["minIndex"]
spreadsheet = sheet_service.spreadsheets()💲 Further, get all mail's ids we want (we passing the key-word to the function get_mails_list()):mailbox::
# Get all id mails with key-word q.
mails = get_mails_list(gmail_service, config["q"])💲 As soon as we get all mail's ids, let's parse each letter📧 to get all values to our table:
# Get list of app's name and values of money
parsed_gmails = parsing_letters(gmail_service, mails, logger)💲 Moreover, with key-word "subject: Apple" we get unneeded letters, like this:
💲 To avoid these mails, we check subject in parsing_letters()✉️ function:
if subject == "Квитанция от Apple":
...code💲 As we get all data, let's update our sheet. To make this, we should separate values in 3 groups(Apple Music, iCloud+ and Additional app):
# separate values in each variables
data_apple_music = []
data_icloud = []
data_others = []
for data in parsed_gmails:
if data[0] == 'Apple Music':
data_apple_music.append(data[1])
elif data[0] == 'iCloud+':
data_icloud.append(data[1])
elif data[0] == 'Additional app':
data_others.append(data[1])💲 One more important thing is getting last index in a column to update the column from this index:
# get last indexes of each column
columns = [column_music, column_cloud, column_others]
last_index = get_last_index(spreadsheet, table_id, sheet_name, columns, min_index)💲 Before updating, we should prepare values for each column.
# Preparing values to update the columns Apple Music, iCloud, Additional app.
table_data = [
get_columns_update(data_apple_music, sheet_name, column_music, last_index[0], len(data_apple_music) + 1),
get_columns_update(data_icloud, sheet_name, column_cloud, last_index[1], len(data_apple_music) + 1),
get_columns_update(data_others, sheet_name, column_others, last_index[2], len(data_apple_music) + 1)]💲 After that, let's update the empty table:
# Update columns Apple Music, iCloud, Additional app
spreadsheet_chunks_update(spreadsheet, table_data, table_id, logger)💲 And the final peace of code count sum of all colum's values:
# Get list of all money values from table
list_money = get_part_of_table(spreadsheet, table_id, sheet_name, column_music, column_others, min_index, 18)
# Count sum of all values
summa = 0
for i in list_money:
summa += float(i.replace(",", "."))
# Create dollar and UAH variables
summa_d = f"{summa:.2f}"
summa_h = f"{summa*40:.2f}"💲 At the end, prepare values in the columns Sum($) and Sum(₴) and update the table again:
# Preparing values to update the columns Sum($) and Sum(₴).
table_sum_data = [
get_rows_update([summa_d, summa_h], sheet_name, dollar_column, uah_column, min_index)]
# Update columns Sum($) and Sum(₴)
spreadsheet_chunks_update(spreadsheet, table_sum_data, table_id, logger)✅ As a result, we will get the filled table:
💲 And delete these letters from our email address:
# Delete mails from email address
delete_emails(gmail_service, mails, logger)