Full Transcript
https://www.youtube.com/watch?v=mCk4Rabkmjc
[00:00] hey guys in my last video I demonstrated
[00:02] hey guys in my last video I demonstrated how you can use CET GPT and python to
[00:05] how you can use CET GPT and python to automate Excel this time I will take it
[00:07] automate Excel this time I will take it a step further and show you how cat GPT
[00:10] a step further and show you how cat GPT can help you to automate Outlook make
[00:13] can help you to automate Outlook make PowerPoint presentations from scratch
[00:15] PowerPoint presentations from scratch create interactive charts merge PDFs
[00:18] create interactive charts merge PDFs compose well-written emails and even
[00:20] compose well-written emails and even create an entire website the results are
[00:23] create an entire website the results are truly impressive and I strongly believe
[00:25] truly impressive and I strongly believe after watching this video you will be
[00:27] after watching this video you will be able to automate some of the TDS office
[00:29] able to automate some of the TDS office Tas you may have so without further Ado
[00:32] Tas you may have so without further Ado let's dive in let us start with the
[00:34] let's dive in let us start with the first example in my attachments folder I
[00:37] first example in my attachments folder I have a bunch of Excel files I want to
[00:39] have a bunch of Excel files I want to send these Excel files to different
[00:41] send these Excel files to different receivers using Outlook to do this I
[00:43] receivers using Outlook to do this I have prepared an overview in the
[00:45] have prepared an overview in the financial data. xlsx workbook in my
[00:48] financial data. xlsx workbook in my previous video we also used this
[00:49] previous video we also used this workbook to create the attachments this
[00:52] workbook to create the attachments this is because all the data for the
[00:54] is because all the data for the different countries can be found in this
[00:55] different countries can be found in this worksheet if you're interested in how to
[00:58] worksheet if you're interested in how to split a table like this into SE
[01:00] split a table like this into SE workbooks you might want to check out
[01:02] workbooks you might want to check out that video but for our use case let us.
[01:04] that video but for our use case let us switch back to the email list I want CET.
[01:06] switch back to the email list I want CET GPT to create a python script that reads.
[01:10] GPT to create a python script that reads data from the sheet email list in an.
[01:12] data from the sheet email list in an Excel workbook called Financial data.
[01:14] Excel workbook called Financial data. xlsx the code should use the data in.
[01:16] xlsx the code should use the data in columns a b c and d to compose and open.
[01:19] columns a b c and d to compose and open Outlook emails with different.
[01:21] Outlook emails with different attachments and recipients also it.
[01:23] attachments and recipients also it should locate the attachments in in.
[01:25] should locate the attachments in in folder called attachments located in the.
[01:27] folder called attachments located in the same directory as the Excel workbook.
[01:30] same directory as the Excel workbook and construct the file path to the.
[01:32] and construct the file path to the attachments the scripture Ed the.
[01:34] attachments the scripture Ed the recipient's name from column B in the.
[01:36] recipient's name from column B in the email text and the reion and CC email.
[01:39] email text and the reion and CC email addresses from column C and D.
[01:41] addresses from column C and D respectively lastly python should open.
[01:43] respectively lastly python should open Outlook emails but should not send them.
[01:45] Outlook emails but should not send them until further actions are taken and.
[01:48] until further actions are taken and overall the python code should be robust.
[01:50] overall the python code should be robust this was a prompt I used in chat GPT for.
[01:54] this was a prompt I used in chat GPT for some reason I got a warning that this.
[01:56] some reason I got a warning that this prompt violated the content policy I am.
[01:58] prompt violated the content policy I am not sure why that is but nonetheless.
[02:01] not sure why that is but nonetheless chat GPT generated the following code.
[02:03] chat GPT generated the following code for me at the bottom it also highlighted
[02:06] for me at the bottom it also highlighted that I need to have Outlook installed on
[02:08] that I need to have Outlook installed on my computer I then took the code and
[02:11] my computer I then took the code and pasted it into an empty python file when
[02:14] pasted it into an empty python file when I ran this code Outlook created an email
[02:16] I ran this code Outlook created an email for each country as we can see it got
[02:19] for each country as we can see it got everything right the email addresses
[02:21] everything right the email addresses including the CC's attachments and text
[02:24] including the CC's attachments and text are all correct it even inserted some
[02:26] are all correct it even inserted some text saying that the financial data can
[02:28] text saying that the financial data can be found attached this is pretty
[02:30] be found attached this is pretty impressive in my opinion the only thing
[02:33] impressive in my opinion the only thing I noticed is that python also returned a
[02:35] I noticed is that python also returned a depreciation warning so the code runs
[02:38] depreciation warning so the code runs now but might lead to an error in the
[02:40] now but might lead to an error in the future so I copied the warning and
[02:43] future so I copied the warning and pasted it into the chat to my surprise
[02:46] pasted it into the chat to my surprise CET GPT acknowledged that this should be
[02:48] CET GPT acknowledged that this should be fixed and provided me with a modified
[02:50] fixed and provided me with a modified version all I had to do was copy and
[02:53] version all I had to do was copy and paste the adjusted version and run the
[02:55] paste the adjusted version and run the code again this time the emails got
[02:57] code again this time the emails got still created but without anyone
[02:59] still created but without anyone warnings great so for now the emails are
[03:02] warnings great so for now the emails are only created and not directly sent out
[03:05] only created and not directly sent out this is exactly what I wanted but
[03:08] this is exactly what I wanted but perhaps in the future I want to send out
[03:10] perhaps in the future I want to send out the emails directly so I asked CH GPT
[03:14] the emails directly so I asked CH GPT how I could send out the emails and it
[03:16] how I could send out the emails and it explained to me which line of code I
[03:18] explained to me which line of code I would need to change and suggested
[03:20] would need to change and suggested further error handling okay and with
[03:22] further error handling okay and with that let us move on to the second
[03:24] that let us move on to the second example this time I want CH GPT to
[03:27] example this time I want CH GPT to create an entire PowerPoint presentation
[03:29] create an entire PowerPoint presentation from scr
[03:30] from scr as before I have the same Excel files
[03:33] as before I have the same Excel files let me actually open up one so that you
[03:35] let me actually open up one so that you can see the content each line represents
[03:38] can see the content each line represents the sales record for the respective
[03:39] the sales record for the respective country and I would like to group the
[03:41] country and I would like to group the sales by product from the aggregated
[03:44] sales by product from the aggregated data I would like to create a bar chart
[03:47] data I would like to create a bar chart and insert that chart into PowerPoint
[03:49] and insert that chart into PowerPoint let us see how we can do that with Chet
[03:51] let us see how we can do that with Chet GPT this time I wrote down the steps and
[03:54] GPT this time I wrote down the steps and bullet points the python script should
[03:57] bullet points the python script should access the input folder and read all
[03:59] access the input folder and read all files with in it for each file the data
[04:02] files with in it for each file the data is located from columns a to P any
[04:05] is located from columns a to P any missing values should be excluded then
[04:08] missing values should be excluded then python should group the sales by product.
[04:10] python should group the sales by product afterwards I want to create a chart.
[04:12] afterwards I want to create a chart using the seon library I was specific.
[04:15] using the seon library I was specific about which Library I wanted to use I.
[04:17] about which Library I wanted to use I also twed without mentioning the python.
[04:19] also twed without mentioning the python package in that case jgpt picked the Met.
[04:22] plot lip Library so it's good to be.
[04:25] specific about the library you want to.
[04:27] specific about the library you want to use especially when you have a.
[04:28] use especially when you have a particular require M but in any case.
[04:31] particular require M but in any case those charts should be saved in a folder.
[04:33] those charts should be saved in a folder named charts and it should create the.
[04:35] named charts and it should create the folder if it doesn't exist already.
[04:37] folder if it doesn't exist already afterwards python should create a.
[04:39] afterwards python should create a PowerPoint presentation and insert a.
[04:41] PowerPoint presentation and insert a slide for each chart it should also.
[04:43] slide for each chart it should also include a title and once everything it's.
[04:46] include a title and once everything it's done the presentation should be saved as.
[04:48] done the presentation should be saved as Financial data. pptx Additionally the.
[04:51] Financial data. pptx Additionally the code should include error handling all.
[04:54] code should include error handling all right so this was a very long prompt and.
[04:56] right so this was a very long prompt and I was really curious if jgpt understood.
[04:59] I was really curious if jgpt understood what I wanted to achieve so here is what.
[05:02] what I wanted to achieve so here is what C GPT responded to my.
[05:04] C GPT responded to my prompt as before I took the code pasted.
[05:08] prompt as before I took the code pasted it into an empty python file and
[05:10] it into an empty python file and executed it also this time I received a
[05:14] future warning but the code ran
[05:17] successfully so when I go back to my
[05:19] directory we can see that we now have a
[05:21] new charts folder within this folder we
[05:24] now have an image for each Excel file
[05:27] where we can see a bar chart of the
[05:29] aggregated sales by product so this is
[05:32] already working and the PowerPoint can
[05:35] be found in the input
[05:36] folder now this presentation doesn't
[05:39] look visually appealing and the charts
[05:42] overlaps with the title but I think this
[05:45] is a good starting point for any further
[05:47] customization we now have a slide for
[05:49] each country for the product sales
[05:51] without writing a single line of code
[05:54] the only thing I want to change now is
[05:56] that the PowerPoint presentation should
[05:58] be saved in the same dire as the python
[06:01] script so I switched over to the chat
[06:03] and asked for an updated version as
[06:06] before I then took the updated code and
[06:08] executed it this time we have the
[06:11] executed it this time we have the presentation right next to the python.
[06:12] presentation right next to the python script okay and with that let us move on.
[06:15] script okay and with that let us move on to the next example maybe instead of an.
[06:18] to the next example maybe instead of an static image you might want to create an.
[06:20] static image you might want to create an interactive chart from your Excel data.
[06:23] interactive chart from your Excel data so we will use the same data as before.
[06:25] so we will use the same data as before and this time I simply want to sum up.
[06:28] and this time I simply want to sum up the sales for each country and plot it.
[06:30] the sales for each country and plot it in an interactive chart now in many.
[06:33] in an interactive chart now in many companies you might use coret colors for.
[06:35] companies you might use coret colors for your charts so I also included the color.
[06:38] your charts so I also included the color code in my prompt and just like that.
[06:41] code in my prompt and just like that jgpt generated the following python code.
[06:44] jgpt generated the following python code and suggested further error handling so.
[06:47] and suggested further error handling so I then asked for the full script that.
[06:49] I then asked for the full script that also included the error handling part.
[06:51] also included the error handling part and then I received the following code.
[06:54] and then I received the following code so I took the code and ran it in my text.
[06:56] so I took the code and ran it in my text editor once that was done I had the new.
[06:59] editor once that was done I had the new HTML file in my folder if you open this.
[07:02] HTML file in my folder if you open this file you will now have an interactive.
[07:04] file you will now have an interactive chart that shows the sales for each.
[07:07] chart that shows the sales for each country okay and with that let us move.
[07:09] country okay and with that let us move on to the next example and this example.
[07:12] on to the next example and this example was actually sent to me by a subscriber.
[07:14] was actually sent to me by a subscriber of mine his task was as follows he has.
[07:17] of mine his task was as follows he has two folders cover and draft in the cover.
[07:21] two folders cover and draft in the cover folder we have a couple of PDFs and the.
[07:24] folder we have a couple of PDFs and the file name is just a number and this is.
[07:26] file name is just a number and this is what the PDF looks like so basically.
[07:29] what the PDF looks like so basically Bally this is a cover for the following.
[07:31] Bally this is a cover for the following PDF file and you can find this PDF file.
[07:35] PDF file and you can find this PDF file in the draft folder now he needs to.
[07:38] in the draft folder now he needs to combine those two files into one so that.
[07:41] combine those two files into one so that we first have the cover and then the.
[07:43] we first have the cover and then the correct PDF from the draft folder and.
[07:46] correct PDF from the draft folder and this is what I asked CET GPT to do the.
[07:49] this is what I asked CET GPT to do the important part is here I provideed CET.
[07:52] important part is here I provideed CET GPT with an example of how those files.
[07:55] GPT with an example of how those files should be matched and that was already.
[07:57] should be matched and that was already enough to understand the pattern of the.
[07:59] enough to understand the pattern of the files and jgpt came up with the.
[08:01] files and jgpt came up with the following solution all I had to do was.
[08:04] following solution all I had to do was copy and paste the code into my text.
[08:06] copy and paste the code into my text editor and execut it however I received.
[08:09] editor and execut it however I received the following error message so I took.
[08:12] the following error message so I took the error message and pasted it into the chat
[08:14] the error message and pasted it into the chat in return I received an adjusted version of the code
[08:17] version of the code and at this time the code ran without any errors
[08:20] code ran without any errors if I check my directory we will now have a new output folder with the merge PDF files
[08:23] my directory we will now have a new output folder with the merge PDF files
[08:24] output folder with the merge PDF files so to validate the result let me open up the first PDF file
[08:28] so to validate the result let me open up the first PDF file as we can see we now have the merge cover with the draft
[08:30] the first PDF file as we can see we now have the merge cover with the draft this can be a massive Time Saver because imagine you will need to do it manually
[08:33] have the merge cover with the draft this can be a massive Time Saver because imagine you will need to do it manually
[08:36] can be a massive Time Saver because imagine you will need to do it manually for hundreds or even thousands of files
[08:38] imagine you will need to do it manually for hundreds or even thousands of files
[08:40] for hundreds or even thousands of files so now that you have automated many tedious office task you can also ask cat GPT to write an email to your manager
[08:43] so now that you have automated many tedious office task you can also ask cat GPT to write an email to your manager
[08:45] tedious office task you can also ask cat GPT to write an email to your manager requesting a salary increase without much effort
[08:48] GPT to write an email to your manager requesting a salary increase without much effort CET GPT has created a well-written email providing your manager with the reasons on how the automated Solutions are helping the company and why you should receive a salary increase
[08:51] requesting a salary increase without much effort CET GPT has created a well-written email providing your manager with the reasons on how the automated Solutions are helping the company and why you should receive a salary increase
[08:53] much effort CET GPT has created a well-written email providing your manager with the reasons on how the automated Solutions are helping the company and why you should receive a salary increase
[08:56] CET GPT has created a well-written email providing your manager with the reasons on how the automated Solutions are helping the company and why you should receive a salary increase
[08:58] well-written email providing your manager with the reasons on how the automated Solutions are helping the company and why you should receive a salary increase
[09:00] manager with the reasons on how the automated Solutions are helping the company and why you should receive a salary increase
[09:02] automated Solutions are helping the company and why you should receive a salary increase
[09:04] company and why you should receive a salary increase I think these examples are pretty mind-blowing and I hope this video has inspired you to automate some of the tedious task you may have
[09:07] I think these examples are pretty mind-blowing and I hope this video has inspired you to automate some of the tedious task you may have
[09:09] are pretty mind-blowing and I hope this video has inspired you to automate some of the tedious task you may have
[09:11] video has inspired you to automate some of the tedious task you may have I've
[09:14] of the tedious task you may have I've actually hosted all of the prompts I
[09:16] actually hosted all of the prompts I used throughout the video online you can
[09:18] used throughout the video online you can find them at the following web address
[09:21] find them at the following web address by clicking on the link you will find
[09:23] by clicking on the link you will find the respective chat
[09:24] the respective chat history and perhaps you've guessed it
[09:26] history and perhaps you've guessed it already but even this entire website was
[09:29] already but even this entire website was created by chat GPT you can also find
[09:32] created by chat GPT you can also find the source code under the following link
[09:34] the source code under the following link all right guys and that is all I have
[09:36] all right guys and that is all I have for you today let me know in the
[09:38] for you today let me know in the comments below what you think about cat
[09:39] comments below what you think about cat GPT I would be curious to know if you
[09:42] GPT I would be curious to know if you have been able to automate any task and
[09:45] have been able to automate any task and as always thanks for watching and see
[09:46] as always thanks for watching and see you in the next video
[09:49] you in the next video [Music]