mpoggy mpoggy - 6 months ago 18
Python Question

Pulling Data from a Live Google Sheet and Emailing It

I need to pull both a username and an extension from a Google sheet and then email it to the users once a week. I've written an extremely inelegant solution that I will attach below. I'm certain it can be reduced substantially with a well-written loop and some logic. I'd appreciate any nudge in the right direction. I started working on this two days ago with zero programming knowledge so please excuse any obvious blunders!

EMAIL problem:
I'd need to put in each row_number([3])+'obfuscated.com' in every subject line and then insert row_number([1]) into every body.

EDIT: For clarification, I simply manually wrote in each row variable by hand. The number of users changes weekly, but I wrote it up to forty as it will never go above that.

import gspread
import json
from oauth2client.service_account import ServiceAccountCredentials
json_key = json.load(open('obfuscated'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('obfuscated.json',
scope)
gc = gspread.authorize(credentials)

sh = gc.open_by_key('obfuscated')
worksheet = sh.get_worksheet(0)


row_1 = worksheet.row_values(2)
print(row_1[3]+'@obfuscated')
print(row_1[1])

row_2 = worksheet.row_values(3)
print(row_2[3]+'@obfuscated')
print(row_2[1])


I'm sorry - Here's the .csv
https://www.dropbox.com/s/i5p3m32m2huiffr/example-march.csv?dl=0

@Racialz - I'd need both the username with '@obfuscated.com' appended and personal extension as a variable I can insert into many emails.

Answer

Here you go, it took forever for me to finally get a setup using your data and setting up an oauth to use with gspread but after that it's super simple.

currentRow = 2
while 1:
    thisRow = worksheet.row_values(currentRow)

    if (thisRow[1] == ""):
        break

    username = thisRow[3] + "@obfuscated.com"
    personalExtension = thisRow[1]

    #email sending code goes here
    print(username, personalExtension)
    currentRow += 1

You just get item at index [3] for username and item at index [1] for personal extension. It loops until it hits a row with no personal extension. This will work with any number of emails.