I wanted to write about an integration I created for a client recently, as a form of documentation to myself on how it works, should I ever need to repair or update it.
I also thought it was interesting to build since a couple of different APIs and services are involved along the way. The data takes quite a journey to get from ZohoCRM to Google Drive.
The aim was to get data from scheduled ZohoCRM Reports, sent via email with attached Excel documents, into a spreadsheet on Google Docs, so that overall summary information could be viewed by the relevant stakeholders.
All of the following was necessary because:
- ZohoCRM don’t provide a way to send Reports directly to Google Drive, manually or automatically
- The kind of (summary) information provided in the Reports isn’t accessible via ZohoCRM’s API, at least not directly.
If ZohoCRM provided more data via their ZohoCRM API, then it would have been possible to have 1 small script running in the background to periodically poll ZohoCRM for new data and add it to a Google Spreadsheet without all of the “moving parts” that were needed in the end.
A summary of how it all works:
- Scheduled emails are sent from ZohoCRM to one of the members of the team.
- A rule was created on the mail server to automatically forward those emails to a virtual email inbox I created using http://www.mailgun.com/, an email like: email@example.com.
- Mailgun receives the email and POST’s the data to a URL I provided it
- This URL is a PHP script, running on an Ubuntu Digital Ocean server.
- This PHP script receives the data and stores it in a JSON file in a local folder. The data is parsed to get information on any attachments and those attachments are stored in a local folder also.
- A separate PHP script, scheduled to run periodically scans the folder mentioned previously for any Excel files (xls or xlsx) and performs the following:
- The excel files are copied to another folder for processing and removed from their original location to avoid re-processing the same excel files during the next iteration.
- The excel files are converted to CSV format, using LibreOffice –headless (https://www.libreoffice.org/), meaning it runs from the command line without a user interface. (Strangely, I was able to push Excel files directly to Google Drive on a Windows based server, but not on a Linux based server, which I couldn’t solve. It wasn’t related to permissions or mime type encoding like I thought it would me. I opted for converting to CSV which worked well)
- The resulting CSV files are pushed to Google Drive and converted to Google Spreadsheet format (so that they don’t contribute to the owners allowed storage space there) and given a title based on the name of the excel file and the date the file was created. For example ‘my_report.xls’ would be saved as ‘My Report 20/01/2016’
The excel files are stored locally in case they are needed in future for debugging purposes.
I thought all of the above was an interesting process to develop as I imagined the journey an email from ZohoCRM took to get to Google Drive as intended.
- An email from ZohoCRM originated from one of their servers, presumable somewhere in the US.
- The email landed in one of the team members Microsoft based mail servers’ mailbox, presumably somewhere in Europe.
- The mail server forwarded the email to MailGun, who are based in San Francisco, so their server(s) are probably located over in the US somewhere.
- Mailgun posted the data in the received email to a URL I provided, which is an Ubuntu server created using Digital Ocean, located in London.
- The PHP code on the Digital Ocean server in London used Google’s Drive API to push the data to Google Drive, again probably hosted somewhere in the US.
Despite hopping from country to country a couple of times, an email sent from ZohoCRM ended up a Google Spreadsheet just a few seconds later, no worse for wear.