How to Automatically Extract Content Ideas from Your Newsletter Subscriptions Using AI (Part 5/5)
Connecting OpenAI to Google Sheets to automatically organize your content ideas
Your inbox is a goldmine going to waste.
You subscribe to dozens of newsletters in your niche for inspiration, but most sit unread in your inbox or get skimmed once and forgotten.
What if you had a system that automatically:
Monitored your Gmail inbox for newsletters from your favourite senders
Used AI to find insights from those newsletters
Generated content ideas inspired by the incoming newsletter but tailored specifically to your audience
Provided each article a ‘Potential’ score indicating which ones will likely resonate most strongly
Stored everything in Google Sheets so you can instantly find most relevant content ideas for your audience
In this series, we’re building this system.
Here’s a preview of what this system will do
Read an unread email from your inbox
Push that through the automation
Output 3 article ideas based on this email but tailored to YOUR audience
Mark the email as read so we don’t process it again
Let’s go!!!
Series Breakdown
Since the whole automation requires quite a few steps, I’ve broken this series into 5 parts:
Setting up our Google Cloud Project so that we can automatically monitor our inbox
Connecting Gmail to Make, our automation engine
Getting an API key from OpenAI
Connecting Gmail to ChatGPT and extracting article ideas
Storing the article ideas in Google Sheets
For those just joining in, here are parts #1, #2, #3, and #4:
In this final article, we’ll automatically store these AI-generated ideas in Google Sheets, creating a neatly organized content pipeline ready for action.
Note: Nobody will be able to access your inbox automatically apart from you.
This article is more comprehensive than usual and includes many screenshots to guide you through each step. For the best experience, view it on a larger screen.
If you’re reading this in email, it may be clipped due to length. Click here to view the full tutorial on the website.
Let’s begin.
Step 1: Add the Iterator Module
From your Make dashboard, open the scenario you created in the previous articles (the one that already has Gmail and OpenAI).
Click the ＋ icon after your OpenAI module to add a new module
Search for “Iterator” and select it. This module will fetch the content ideas from ChatGPT one by one.
Drag
content_ideas[](generated by the OpenAI module) into the Array field in the Iterator module.
Step 2: Set Up Your Google Sheet
In your Google Drive, create a new folder named “Automatic Content Ideation”.
Inside that folder, create a new Google Sheet named “Article Ideas”. We will store the content ideas in this sheet.
Create the following five columns in that sheet:
Title
Description
Pain Point Resolved
Promise to the reader
Potential
Step 3: Connect the Iterator Module to Google Sheets
From your Iterator module, click the ＋ icon to add another module.
Search for Google Sheets and select “Add a Row”.
Step 4: Configure Google Sheets Module
Set Search Method to “Search by path”.
Under Drive, choose “My Drive”.
Click on “Click here to choose file” under Spreadsheet ID.
Select the “Automatic Content Ideation” folder
Click on ＋ next to the folder name and choose your “Article Ideas” Sheet.
Choose the Sheet Name (e.g., Sheet1) where you want to store the article ideas.
Under “Table contains headers”, select “Yes” (because the first row in your Google Sheet is column names).
Map each field from the Iterator module to the matching columns:
Title (A) ← title (from the Iterator)
Description (B) ← description
Pain Point Resolved (C) ← pain_point_resolved
Promise to the reader (D) ← promise_made
Potential (E) ← potential
Click “Save.”
Step 5: Test Your Automation
Right-click on the Gmail module and select “Choose where to start”
Select “All emails”, and click on Save
Make sure you have at least one unread newsletter email in the label you’re monitoring.
Click “Run once”. Wait a few seconds for the automation to run.
Check your “Article Ideas” Google Sheet. You should see new rows with the content ideas generated by ChatGPT.
Here’s the email I processed:
It was an opinion piece by Ethan Mollick on which AI to use. Our system took the key ideas from this email and created these three article ideas
According to ChatGPT, the first article ideas: “Eliminating Filler Content” will resonate the most with my audience (Potential: 8.8/10)
Step 6: Schedule Your Automation
Right-click on the Gmail module and select “Choose where to start”
Select “All emails”, and click on Save
Click the scheduler at the bottom and select an appropriate interval when you want the automation to run.
Congratulations!
That’s it! You now have a fully automated system that:
Fetches unread newsletters from Gmail.
Analyzes them with ChatGPT.
Iterates through each AI-generated idea.
Stores each idea in your “Article Ideas” Google Sheet.
So, now, instead of scratching your head to come up with article ideas, you have a database of readymades ideas with their potential for success.
All you need to do now is research and create the article.
Enjoy your new streamlined system!
Want personalized help building your customized AI system?
I offer 90-minute sessions where we build your custom AI content system together.