Google App Engine PHP script to process email and send data to a Google Sheet

Question

Our water district sends out automated alarms and process statistics from a SCADA computer in our water treatment plant. The information is sent in the form of SMS messages and daily emails with csv file attachments. Because we also have an informational website on Google Sites, I would like to automatically display real-time process statistics on our website in a simple Google sheet which would be automatically updated.

I have been handling the SMS alarms through custom PHP/Twilio scripts which are separately hosted, but would like to integrate everything in the Google Cloud with Google AppEngine. I think this can be accomplished in a variety of ways, and am currently evaluating alternative automation approaches using PHP and the Gmail API. As a Google Cloud/AppEngine noob, I have a couple of architectural questions:

1. Can I accomplish this automation by simply enabling the Google Sheet for incoming mail and processing the data through spreadsheet scripting? If I elect this approach, can the Google Sheet receive email directly, or must I send email data to the sheet as an http request?

2. Alternatively, would the Google Cloud be a more reliable and robust platform for this automation? From the examples I have seen, it looks like AppEngine PHP scripts can be enabled to receive incoming email, parse required data from the email body or attachments, and form an appropriate web request directed to the Google Sheet endpoint. Under this approach, Google Sheet scripting would be minimal.

Does anybody have any constructive advice before I plunge blindly and lustfully headlong into this project? Thanks.

Answer 1

To answer your questions:

1/ Using Google Scripts

I think using Google Spreadsheet Scripting might be an option if your data was stored in the body text of the e-mail.

In this case you could actually write a Google Spreadsheet script which pulls the e-mails directly from your Gmail account. Instead of sending the e-mail to the spreadsheet you could access your Gmail account the GmailApp service.

However ... if you data is stored inside a CSV attachment I'm not so sure if this is feasible. I'm not sure that you can access the data inside attachments with Google Spreadsheet script.

2/ Using Google Cloud

I'm by no means an expert here. But I think you don't really need to host your code with them. Once you use PHP and leverage the Google APIs (Gmail + Google Sheets) you should be able to host your code anywhere you like.

Extra: Consider using an Email Parser Software

Developing all this yourself is feasible it will take you for sure a lot of time. E-mail is always a bit difficult to handle and you have a lot of moving parts there.

I'm the founder of mailparser.io and I believe that you would be much better of using a ready made e-mail parser software like ours for this job. We integrate natively with Google Spreadsheets and you should have something running within a couple of minutes.