How to automate weekly email to spreadsheet with compressed PDF for GPT processing?
Hello!
I’m creating an automation that triggers off a specific weekly email that lands in my inbox. From there, I’d like to take the contents of that email and have my spreadsheet associated with said email be automatically updated with its contents.
I figured building a GPT that understands where and how I’d like my spreadsheet updated would be helpful and have already created one. My problem right now consists of payload sizing for the GPT to handle and maintaining the PDF contents while trying to compress/transfer the file.
My initial thought process: I thought that by pulling the file from the email within outlook would be the trigger > uploading the file to google drive to hopefully turn into a document and allow for me to use the text file that google drive gives me the option to use for future fields like within Files By Zapier to make it easier for the GPT to process > send that to assistant > update spreadsheet
My problem: For “Upload a File,” I need to not convert to document in order to maintain the elements of the PDF that include a majority of it’s actual content, but in opting to not do this, I cannot opt to turn it into a txt file within google drive. To circumvent this, I tried using a Zip Archive API to compress the file into a text file, but the payload to send the text file to the GPT was still 2MB larger that what GPT’s best model could handle. Kind of stumped on how to get the full PDF compressed and able to be read by the assistant who could then do all the hard work in the process.
Here’s the flow and the fields I spoke to earlier! Let me know if you guys need anything else! Thanks for taking a crack at this :)
Page 1 / 1
Hey @blazersfan77
Hmm, perhaps you could switch to the New Attachment trigger for Microsoft Outlook? That would trigger on each email attachment individually so that you’re not having to deal with a large Zip file containing multiple attachments?
You’d also need to add a filter to the Zap to ensure it only continues for certain file types .e.g PDF. But I expect you could then just pass the file to the ChatGPT action and ask it to read it without needing to extract the text from it.
Do you think that approach could work or would the individual PDF files still be too big?
I’m not sure that approach would work.
I unfortunately learned today that it’s not actually an attachment, but rather a page built into the body of the email itself.
Any thoughts? (So sorry for getting back to you so late...)
No worries on the delay @blazersfan77, it’s all good!
Hmm, are you saying that the PDF file (or a link to that file?) is embedded in the HTML of the email? Or is the desired PDF actually HTML that’s within the body of the email?
If it’s a link to the file or embedded within the email itself then you could potentially look at using a Formatter (Text > Extract Pattern) action to extract it.
That said, can you share some screenshots showing how the PDF appears in the email as well as the HTML received in the Microsoft Outlook trigger where the file is embedded/linked to? This will help me to better determine the best way to extract it. Make sure to blur/remove all personal information (names, emails, addresses etc.) from screenshots before sharing.
Also, looking back at your earlier screenshots just now I spotted that the file objects (Files: (Exists but not shown)) has been selected as for File Extension field which is incorrect. You’d need to have a file extension selected for that field, not the file itself.
So that Files field needs to be removed here:
Looking forward to hearing from you on this!
I hope this is everything that you’re looking for! I think an issue at hand for the clever formatter > text > Extract Pattern that you came up with is that the actual link within the rankings page is gatekept behind a log-in page.
Thanks so much for sharing those screenshots @blazersfan77. I can’t quite see the full HTML that’s present in the Body Content field:
Can you please share some further screenshots so I can see the rest of what’s in there?
Sorry for any hassle on that. Once I’ve got that it should give me a better idea on the best way to go about extracting it. Thanks!
No worries at all, I should’ve sent the entire body in the original post - sorry about that!
This should be all of it! :)
Thanks for the additional screenshots here @blazersfan77, it’s very much appreciated!
Ok, so it looks like it might be an image that’s embedded within a HTML table. I’m not 100% certain on that as I couldn’t identify the exact image’s src from the code in the screenshots. But if you right click the table in the email does it give you “Save image” options? If so, then that would confirm my theory.
In which case, it may be best to extract all the images so we can determine which one needs to be sent to the GPT. To get the images from the email it might be better to use a Code step for this instead of Formatter. Not sure how familiar you are with coding in Javascript or Python but we have an AI feature that can help generate the code for this.
Once we know what image it is, you can pass it over to the ChatGPT assistant to analyse and extract the contents from it.
Can you give that Code step a try and let me know how it goes?
It does give a save image option when I right click on the photo of the rankings! I’ll build out the code step and let you know how that goes here in a bit!
That’s great news @blazersfan77! Thanks for checking on that and confirming it’s definitely an image. Keen to know how it goes so will keep an eye out for your reply here!
Hey @SamB
I’ve written a Python script that extracts the specific link tied to the rankings we’re looking for from the HTML body of the email. However, I can’t seem to send that to a GPT assistant for processing (at least, based on the interactions I’ve tested so far).
My current plan is to implement a Webhooks by Zapier step that sends a POST request with image data to Microsoft Azure (specifically the Computer Vision OCR via API endpoint), and then forward that output to the custom GPT. Do you foresee any challenges or anything I might be overlooking with this approach?
Nice work on getting that Python Code step extracting the image link @blazersfan77!
Hmm, I wonder if there’s an issue with the image link that it’s extracted. If you open that link in your web browser are you able to view it? If not then it’s most likely an issue with the link itself. But if it loads the image, then it’s potentially an issue with the ChatGPT action.
Can you share screenshot of how the extracted link has been selected in the ChatGPT action? That’ll help me to see if there’s any issues on that side of things.
My current plan is to implement a Webhooks by Zapier step that sends a POST request with image data to Microsoft Azure (specifically the Computer Vision OCR via API endpoint), and then forward that output to the custom GPT. Do you foresee any challenges or anything I might be overlooking with this approach?
The ChatGPT app on Zapier has an Analyze Image Content with Vision action that should be able to read the image and give you back the information it contains. So I’m thinking that webhook step might not be necessary here if we can get the issues with the image link sorted. Totally fine if you’d prefer to use Microsoft Azure but you’ll likely run into issues with the authentication if their API uses OAuth, as Webhooks currently only supports basic authentication methods.
Uh...I just didn’t realize that there was an option to do that haha
It worked lol
@SamB Do you know if it’s possible to take each rank from this output and utilize looping (or maybe something else) to add each one to a google sheet?
Yay! So glad that worked @blazersfan77!
Yeh it would be possible, but you’d want to adjust the instructions given to ChatGPT to get it to just output the rankings as a comma separated list. And for the best results I’d recommend including an example of how you want it exported. Maybe try something like this “List the rankings from the images as a comma separated list, including the rank number and corresponding company name, with no additional text proceeding it. For example: 1. Company name, 2. Company name etc.”. The more explicit you are with your instructions the more consistently it would behave.
That should then give you a list of the rankings which you can then use in a Create a loop from text (Looping) action. Can you give that a try and let me know how you get on?
Hey @SamB , super helpful!!
I’ll give that a shot.
My only other problem outside of this would be feeding the data to my assistant that tries to answer queries based on all the data from the spreadsheet. Do you know how I would be able to give a GPT assistant access to a live spreadsheet? I tried uploading the spreadsheet from google drive to the assistant per query but sheets/xlsx files don’t seem to be valid file types for assistants. Manually pushing all the data to the spreadsheet in the prompt is not a viable solution unfortunately lol
So quick update:
This is my flow:
I’m waiting for a weekly email -- from there, I’ll clean the title of the email (to use later for a specific variable related to month/year), extract the specific image url im looking for and then send that image over to be analyzed. I’ve asked ChatGPT to do the following with the image:
I’ll send the output of this to an assistant who will then assign a category from a list of valid options using this information:
From whatever the assistant responds with, it usually includes some unnecessary introduction to the meet of the material and so I have a script that cleans the output to just be the raw JSON output:
From here, I wanted to loop through all of the 25 brands that the assistant pulled out, ordered, assigned to a specific category, and slot them into the spreadsheet consolidating all this information.
When I publish and test this, it works BEAUTIFULLY up until the actual spreadsheet-filling. It has the correct information, the correct order, but when it comes to inputting the data correctly, it goes berserk.
Here is an example:
This is the output despite having this in the DATA OUT from troubleshooting via Zap History:
(This is the last one from the loop, hence why it’s doing rank 1 in this screenshot -- this is on purpose).
What can I do to clear this issue up?
I’m so sorry for missing your latest replies here @blazersfan77!
Ah, it looks like some of the loops are running quicker than others, causing the rows to be added out of sequence. To get around this you could try adding a Delay After Queue action which will add each loop to a queue. Can you give that a try and let me know if that gives better results?
Hey @SamB , hope you’re doing well! No worries at all, I know I flood your teams inboxes haha. I appreciate all the help so so much!
It worked beautifully. I’m all set, thank you so much.
Very well thank you, @blazersfan77. Hope you are too!
That is awesome news! Really pleased that Delay After Queue action did the trick. If you need any help down the road, don't hesitate to reach out in the Community. We’re always happy to assist!