Mailing Google Docs As Attachment On Google App Engine

Table of contents

Reading Time: 4 minutes

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.

public class EmailSender extends Controller {

private static final String META_FEED_URL = "https://spreadsheets.google.com/feeds/spreadsheets/private/full";
private static String spreadSheetName = "spreadsheetname";
private static String yourApplicationName = "emailSender-version1";
private static String userEmail = "yourmail@gmail.com";
private static String recepientEmail = "recepientmail@gmail.com";
private static String password = "password";


public static void emailSpreadsheetAsAttachment() throws AuthenticationException, MessagingException, IOException {
	SpreadsheetService spreadSheetService = createSpreadSheetService();
	DocsService documentService = createDocumentService();
	SpreadsheetEntry spreadsheetEntry = loadSpreadSheet(spreadSheetService, spreadSheetName);
	byte[] spreadSheetData = getSpreadSheetData(spreadSheetService, documentService, spreadsheetEntry);
	sendMailWithAttachment(spreadSheetData);
	}
. . .
. . .
private static SpreadsheetService createSpreadSheetService() throws AuthenticationException {
	SpreadsheetService spreadSheetService = new SpreadsheetService(yourApplicationName);
	spreadSheetService.setUserCredentials(userEmail, password);
	return spreadSheetService;
	}

private static DocsService createDocumentService() throws AuthenticationException {
	DocsService docsService = new DocsService(yourApplicationName);
	docsService.setUserCredentials(userEmail, password);
	return docsService;
       }
. . .
. . .
}

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.

public class EmailSender extends Controller {
. . .

private static SpreadsheetEntry loadSpreadSheet(SpreadsheetService spreadSheetService, String sheetName) {
	SpreadsheetEntry spreadSheetEntry = null;
	try {
		URL metafeedUrl = new URL(META_FEED_URL);
		SpreadsheetFeed feed = spreadSheetService.getFeed(metafeedUrl, SpreadsheetFeed.class);

		List<SpreadsheetEntry> spreadsheets = feed.getEntries();
		for (int i = 0; i < spreadsheets.size(); i++) {
			SpreadsheetEntry entry = spreadsheets.get(i);
			if (entry.getTitle().getPlainText().equals(sheetName)) {
				spreadSheetEntry = entry;
				break;
			}
		}
	} catch (Exception e) {
		Logger.info("Loading Worksheet failed:" + e.getMessage());
	}

	return spreadSheetEntry;
	}

. . .
}

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.

public class EmailSender extends Controller {
. . .

private static byte[] getSpreadSheetData(SpreadsheetService spreadSheetService, DocsService documentService,
			SpreadsheetEntry spreadsheetEntry) throws IOException {
	String spreadSheetkey = spreadsheetEntry.getKey();
	byte[] data = null;
	InputStream inStream = null;
	ByteArrayOutputStream outStream = null;

	// Substitute the spreadsheets token for the docs token
	UserToken docsToken = (UserToken) documentService.getAuthTokenFactory().getAuthToken();
	UserToken spreadsheetsToken = (UserToken) spreadSheetService.getAuthTokenFactory().getAuthToken();
	documentService.setUserToken(spreadsheetsToken.getValue());

	String exportUrl = "https://spreadsheets.google.com/feeds/download/spreadsheets" + "/Export?key="
				+ spreadSheetkey + "&fmcmd=" + "xls";

	Logger.info("Exporting document from " + exportUrl);

	MediaContent mc = new MediaContent();
	mc.setUri(exportUrl);

	try {
		MediaSource ms = documentService.getMedia(mc);

		inStream = ms.getInputStream();
		outStream = new ByteArrayOutputStream();

		int c;
		while ((c = inStream.read()) != -1) {
			outStream.write(c);
		}

		// Restore docs token for our DocList client
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		documentService.setUserToken(docsToken.getValue());
		if (inStream != null) {
			inStream.close();
		}
		if (outStream != null) {
			data = outStream.toByteArray();
			outStream.close();
		}
	}

. . .
}

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.

public class EmailSender extends Controller {
. . .

private static void sendMailWithAttachment(byte[] spreadSheetData) throws UnsupportedEncodingException,
			MessagingException {
	Properties props = new Properties();
	Session session = Session.getDefaultInstance(props, null);

	Message message = new MimeMessage(session);
	message.setFrom(new InternetAddress(userEmail, "Administrator"));

	message.addRecipient(Message.RecipientType.TO, new InternetAddress(recepientEmail, recepientEmail));

	String textBody = "Your Excel Sheet as attachment";

	Multipart mp = new MimeMultipart();

	MimeBodyPart textPart = new MimeBodyPart();
	textPart.setContent(textBody, "text/html");
	mp.addBodyPart(textPart);

	MimeBodyPart attachment = new MimeBodyPart();
	String excelFileName = spreadSheetName + ".xls";
	String filename = URLEncoder.encode(excelFileName, "UTF-8");
	attachment.setFileName(filename);
	attachment.setDisposition(Part.ATTACHMENT);

	DataSource src = new ByteArrayDataSource(spreadSheetData, "application/x-ms-excel");
	DataHandler handler = new DataHandler(src);
	attachment.setDataHandler(handler);
	mp.addBodyPart(attachment);

	message.setContent(mp);
	message.setSubject(String.format("Google Spreadsheet as attachment"));

	Transport.send(message);

	Logger.info("Mail with attachment file: " + excelFileName + " sent successfully");

	}

. . .
}

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-1.0.3.2. Unzip it and add it in the path variable of your operating system.

user@user-laptop:~/emailattachment$ cd <PATH_TO_PROJECT>
user@user-laptop:~/emailattachment$ play eclipsify
user@user-laptop:~/emailattachment$ play run

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.