Our application works with Google Spreadsheet API to update spreadsheets. The application now needed to send mail with an attachment of a google spreadsheet. We posted our query on google docs community here and on Google app engine here and there were no clear directions of implementing it.
Let’s see how we went about implementing this feature.
Google provides its spreadsheet API and it allows you to create and retrieve data in Google Spreadsheets, but it does not allow us to either create or manage their permissions. Google also has Google Documents List API which can be used to create and retrieve a list of Google Spreadsheets. For our case Google Documents List API provides exporting functionality such that we can export Google documents in common formats like pdf, rtf, xls and others. Google also provides java mail api and with its Multi-Part Messages feature we can send attachment as well.
For implementing this, we used spreadsheet API to retrieve spreadsheet entry. Using the spreadsheet entry we used Google Documents List API to export it in the specified format and we used java mail api to send exported data as an attachment.
We used play framework for our implementation but it can also be applied on other java frameworks as well. The class EmailSender first creates a google spreadsheet service and document service.
For creating these two services we need the application name which is used for logging purposes and the user credentials to work with the google document. The next method call loadSpreadSheet(spreadSheetService, spreadSheetName) actually loads the spreadsheet entry given it’s spreadsheet name. The code listing is given below.
Next we use the Google Documents List API to export the spreadsheet in Excel sheet format. The code listing for method getSpreadSheetData is given below.
The important point to note in this method is that we substitute the spreadsheets token for the docs token and change it back to the original in the finally block. Since on Google App Engine we cannot work with the File system therefore we get data in ByteArrayOutputStream instead. You can read more on using the tokens and exporting spreadsheets here. This method returns a byte array of the exported spreadsheet data which we can use to send mail as an attachment.
The next method sendMailWithAttachment(spreadSheetData) uses java mail api Multi-Part Messages to send this spreadSheetData as an attachment. The code listing is given below.
I have uploaded this working project here. You will have to update values for spreadSheetName, yourApplicationName, userEmail, recepientEmail and password accordingly in EmailSender class.
You will also have to download Play-22.214.171.124. Unzip it and add it in the path variable of your operating system.
The play eclipsify command converts the project in eclipse project. Now if you hit the url http://localhost:9000/EmailSender/emailSpreadsheetAsAttachment you should see the email sent successfully in the logs. You will have to deploy this application on Google App Engine to send a mail though. The example shown here mails a google spreadsheet but it will work fine for google documents in general.