Integrating Donation Forms and Google Sheets

  1. Web Services Blog
  2. Employment
  3. Sample Widgets
  4. Sitecore Resources
  5. Training
  6. Contact Us
  7. Marketing and Communications
  8. MarCom Service Request
May 2, 2016

Cedarville’s now annual tradition of a one-day, twenty-four hour giving day blitz fell on April 20 in 2016. While we were able to re-use a number of website front-end design patterns from the 2015 site, several back-end modifications were needed due to system changes and new requirements from the advancement staff.

The first major change was in our credit card handling. Due to PCI compliance requirements, the university is on a completely different system for handling credit cards from a year ago. We no longer host credit card form fields and therefore no longer funnel the credit card processing on our site through back-end systems for processing. The fields are all hosted off-campus through a series of iframes. Until this point, we had directed users to a standard payment screen for all website transactions that required credit card input. For this one-day giving site, one of the requirements was to streamline the user flow and maintain the giving day user interface as much as possible. After overcoming several challenges, we were able to have the iframe fields hosted in the giving day UI to create the desired seamless experience.

The second major change was the integration of Google Sheets into the back-end process. For a giving blitz like this, it’s helpful to have a subset of the data go into a spreadsheet that can be concurrently edited and collaborated on by several offices. The helps keep everyone in multiple offices spread across campus on the same page regarding where the university is in the giving matches and challenges presented throughout the day. It also helps expedite receipting and thank you notes written by volunteers throughout the day. Once the gift was processed and added to our standard donation database, a row was added using Google’s C# APIs to a particular Google Sheet shared by the giving day team. The Google Sheet document had two additional worksheets–one for manual entry of gifts not processed by the online form and one for summarizing the data from the first two worksheets. The summary data worksheet also was used to determine what total dollar amount was shown on the website–the website would periodically fetch the data from the sheet for display.

Another interesting tidbit of this giving day page is the number of systems that are tied together to make it happen. The giving day website itself is managed in Sitecore, our web content management system. The giving site template used javascript to make a REST call to get JSON data for showing the ever-changing totals. The giving page used knockout for the in-page form data management and validation and for posting of the form data as JSON. The form data was submitted to a C# MVC app, which in turn did a server-to-server post to ColdFusion (our current donation system is written in ColdFusion) to utilize existing business processes to create the donation record. Next, the same MVC controller created the Google Sheet row. Processing was then handed back to the Sitecore page, which loaded the credit card form in the iframe. At that point, the credit card form fields are iframes within an iframe. What a fun challenge! Once the credit card form data was submitted and processed, our giving system was updated with the processed payment information (C# updating the payment database), MailChimp’s Mandrill service was used to send a nice-looking Thank You receipt email, and the user was shown a thank-you-success message.

Because of time constraints, we were unable to fully test the entire system for edge case scenarios, causing a few hiccups when the giving site went live on the giving day (sound familiar Martian movie?). First, Internet Explorer (all versions, of course) had issues with the iframe-within-an-iframe that caused the credit card fields to either not show or not be clickable/navigable. Some rearrangement of our jQuery reference fixed that issue (moved from pointing to our on-campus .js file to the CDN version).  Second, for about 45 minutes that morning, Google had some crazy thing going on with their API calls, causing them to intermittently fail to add rows to our Google Sheet with the following error – Google.Apis.Auth.OAuth2.Responses.TokenResponseException: Error:”internal_failure”. At first we thought the errors were caused by our site hitting some API rate limits or something like that, but after 45 minutes, the issue just disappeared. Thanks Google. Thankfully our error handling captured enough data to recreate the rows by hand.  Disaster averted.

This was a challenging project, especially given a short time constraint and the rush to fix the two issues during the giving rush in the morning. But it was rewarding as well to see it all come together and work well (besides the few hiccups I mentioned). The integration of Google Sheets as a secondary target of information from the giving form provided a streamlined workflow for processing the steady stream of incoming donations and thereby proved extremely helpful to the team of users running the giving day.

Posted in: ,