Financial Python

Studies in Finance and Python

Update to Google Apps Script

with one comment

The Google guys finally updated the python client to reflect the addition of Groups to Google Apps. Groups allow admins to set permissions on distribution lists (for example, only list owners can send email to the list, etc.) Anyway, they basically added another “groups” service that allows the user to access Group objects. The group feed objects, however, are translated into regular python dictionaries for you and are not XML feeds. I’ve attached an updated python script (see this post for the original) that reflects the change.

The following script reads users and group lists from your GA domain and writes
all the info to a google spreadsheet.

Used on Mac OS X.

from os import remove
from time import strftime
import gdata.apps.service
import gdata.apps.groups.service
import gdata.spreadsheet.service
import xlwt
import xlrd

#authenticate credentials and login to Google Apps

address = raw_input('Enter your email address: ' )
URL = address.split('@')[-1]
key = raw_input('Enter your password: ')

P_service = gdata.apps.service.AppsService(email = address,
                                           domain = URL, password = key)
G_service = gdata.apps.groups.service.GroupsService(email = address,
                                                    domain = URL, password = key)
S_service = gdata.spreadsheet.service.SpreadsheetsService(address, key)
D_service =, key)


#read data

def printFeed(feed, printer):
    Prints out the contents of a feed to the console and puts items into a
    python list []. If printer = True, the function will print results
    to the console.

    if(len(feed.entry) == 0):
        print 'No entries in feed.\n'

    feedItems = []
    for i, entry in enumerate(feed.entry):
        if printer == True:
            print '%s %s' % (i+1, entry.title.text.encode('UTF-8'))


    return feedItems

def getAllUsers(printer=False):
    Prints list of user accounts and places their names in a python list
    via printFeed().

    userFeed = P_service.RetrieveAllUsers()
    items = printFeed(userFeed, printer)
    userDict = {'domainUsers':items}

    return userDict

def getAllGroups(printer=False):
    Prints list of Groups and places names in a python list via printFeed().

    groupFeed = G_service.RetrieveAllGroups()
    groups = [groupFeed[i]['groupName'] for i,v in enumerate(groupFeed)]

    if printer == True:
        print groups

    return groups

def getGroupMembers(groupName, printer=False):
    '''Returns a python dictionary, {groupName:[members]}.'''
    recipientFeed = G_service.RetrieveAllMembers(groupName)
    members = [recipientFeed[i]['memberId'] for i,v in enumerate(recipientFeed)]
    d = {groupName:members}

    return d

def getAllDocs(printer=False):
    '''Prints list of documents in your account and puts titles into a list.'''
    docFeed = D_service.GetDocumentListFeed()
    docs = printFeed(docFeed, printer)

    return docs

def makeGroupDict(printer=False): #includes domain users
    '''Create a dictionary of Group:MemberList for use with spreadsheet api.'''

    groups = getAllGroups(printer)
    users = getAllUsers(printer)

    groupsDict = {}
    for group in groups:
        groupsDict.update(getGroupMembers(group, printer))


    return groupsDict

def writeXLS():
    '''Use xlwt and xlrd to write data to an excel file
    that can be uploaded to Google Docs.'''

    print 'Getting data from Google Apps...'

    groupData = makeGroupDict()
    groupNames = sorted([name for name in groupData.iterkeys()])
    timestamp = strftime('_%a%b%d_%I%M%p')
    path = '/Users/username/folder/' #Change this to your desired path!!
    target = 'GroupLists%s.xls' % timestamp

    wt = xlwt.Workbook()

    #Add sheets
    print 'Building spreadsheet...'
    sum_sheet = wt.add_sheet('Group Summary')
    sum_sheet.col(0).width = 4000

    for names in groupNames:
        sh = wt.add_sheet(names)
        sh.col(0).width = 8000

    print 'Creating temp file...''tempGroups.xls')

    #create dictionary of sheet positions
    rd = xlrd.open_workbook('tempGroups.xls')
    sheetIndex = [rd.sheet_by_name(group).number for group in groupNames]
    sheetConfig = dict(zip(groupNames, sheetIndex))

    #write data to appropriate sheets
    print 'Writing Group data...'
    for sheet in sheetConfig:

        for name in groupData[sheet]:
                                                   0, name)

    #create summary sheet, list of groups, # of members
    print 'Writing Summary data...'
    heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
    sum_sheet.write(0,0,'Groups', heading_xf)
    sum_sheet.write(0,1,'Size', heading_xf)

    for index, name in enumerate(groupNames):
        sum_sheet.write(index+1, 0, name)
        sum_sheet.write(index+1, 1, len(groupData[name]))

    print 'Saving file...'
    print 'Removing temp file...'

    return path+target

def uploadDoc(filepath):
    '''Upload spreadsheet to Google Apps account.'''

    print 'Uploading document to Google Apps...'
    ms = gdata.MediaSource(file_path=filepath, content_type='application/')
    filename = filepath.split('/')[-1]
    entry = D_service.UploadSpreadsheet(ms,filename)
    print 'Spreadsheet Link:', entry.GetAlternateLink().href

def main():

    filepath = writeXLS()

# enable as script 

if (__name__ == '__main__'):


Written by DK

May 9, 2009 at 10:08 pm

Posted in Python

Tagged with , ,

One Response

Subscribe to comments with RSS.

  1. […] better ways to code this, but what the heck, it worked for me. But I make no guarantees! (UPDATE, see post with updated code.) Tagged with: code, google apps no comments yet Delta, Synthetic Tranches, and Mark […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: