mpoggy mpoggy - 2 years ago 67
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])+'' 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 = ['']
credentials = ServiceAccountCredentials.from_json_keyfile_name('obfuscated.json',
gc = gspread.authorize(credentials)

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

row_1 = worksheet.row_values(2)

row_2 = worksheet.row_values(3)

I'm sorry - Here's the .csv

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

Answer Source

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] == ""):

    username = thisRow[3] + ""
    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download