For: DPG Media – Marketing (CRM/Content team)
Project Goal
This project aimed to streamline content creation by integrating OpenAI’s GPT models directly into Google Sheets. The goal was to allow users to generate text content based on custom prompts and automatically populate it into specific cells, and then further refine it by extracting key email components. This would significantly reduce manual work, improve efficiency, and ensure consistency in content generation. For DPG Media’s E-mail marketing team, with only 2 copywriters and more than 70 brands, it’s impossible to (manually) create brand/tone of voice- matching content for each of them, highlighting the necessity for automation.
How it was built
The solution consists of three main components: a Google Apps Script, a Google Cloud Function, and an external Python script. The Google Apps Script acts as a bridge, capturing user prompts and sheet metadata within Google Sheets. It sends this information to the Google Cloud Function via an HTTP POST request. The Cloud Function, written in Python, then interacts with the OpenAI API, specifically using the GPT-4 model, to generate an email based on the provided prompt. It receives the response and uses the Google Sheets API to write the generated content back into the specified cells. Additionally, it employs function calling with the same model to extract components like the headline, salutation, main body, offer headline, offer body, and call to action from the generated text, and write those to separate cells. The extracted data is structured into a JSON object. Finally, this data is written into a specified Google Sheet, updating the cells based on the provided range parameters. The script uses environment variables to keep the API keys and other credentials secure. This design provides both flexibility and security in handling sensitive data, ensuring efficient data flow and processing using the OpenAI Python library and Google Sheets API.
Technologies used
- Google Apps Script: For handling user interaction within Google Sheets.
- Google Cloud Functions: For serverless execution of the main logic.
- Python: For overall logic, backend processing and API interaction.
- OpenAI API: Specifically, GPT-4 for email generation and function calling to generate text content using GPT models.
- Google Sheets API: For reading and writing data to Google Sheets.
- gspread Library: For simplifying Google Sheets interactions in Python.
- openai library: For interaction with OpenAI API