Automate PDF Invoices Data Transfer to Google Sheets with ChatGPT & Zapier | Tutorial
Let's build with Zapier and AI (100+ videos)📅 2023-10-21
Transcript
[00:00] welcome back to corbon AI where I'm showing you daily how to start leveraging artificial intelligence in your personal and your business life and you clicked on today's video as I'm going to show you how to automatically take invoices like this and push it towards a Excel sheet using Chad gbt this came from a suggestion from one of our viewers here so let's go and dive into today it's going to be a pretty powerful tutorial we've been doing a lot of tutorials lately on the powers of Chad gbt zapier and automation when it comes to PDF specifically so if that's a big part of your business or your personal life just type in Corbin AI PDF
[00:30] or just check out our recent videos here so we're doing a ton of stuff when it comes to PDFs and how to manipulate the data so this one we're going to deal with the invoice as we got from one of our viewers here asking essentially if I went ahead and you know talked and we found out that they're looking for taking uh you know amounts dates client info from a bill PDF and pushing it towards an Excel sheet so in today's video we're going to do that we're going to use a slightly different software here we're going to use Google Drive and Google Sheets as we're able to access the API better in this context text let's go to jump in okay so I'm going to
[01:02] go ahead and start off by uploading our example PDF here let's go to check it out together so we can kind of get an idea of what's going on here when I was originally going to make this tutorial I was going to do it with one line item but I realized that people are going to want to know for multiple line items so I went ahead and ensure that we have multiple line items in this invoice this is a pseudo invoice here uh for a fake client called James Brown we got two different invoices for this we got website development SEO work and then a bunch of information associated with this PDF so the way we're going to do this is we're going to have one drive folder that's going to be for when I
[01:33] just drag in the invoice everything else incurs we automatically add it to an Excel sheet and you know kind of proceed from there so let's go to jump over to our other folder here which we call sheet made you can name these however you want and we went ahead and made our Excel sheet so what we're going to grab and you can go ahead and grab whatever is specific for your invoice and what you care about but in our context we're going to grab the invoice number the bill to the customer email Services total due date and if the service is complete or not in most contexts is going to say no because we are just starting service so to start off let's
[02:03] go ahead and open up zapier okay so I'm over in zapier I'm going to go ahead and create a new zap all those zaps are from our channel here we have over 900 videos when it comes to automation Chad gbt and other stuff when it comes to AI I'm going to go and rename this zap to uh Auto PDF invoice and I think actually I can't so I'm going to go ahead and have to go ahead and share the code in the comments down below zap doesn't allow the sharing of Zap saaff code yet but you'll be able to copy it directly from the description down below or the comment so from here though we're just going to set up simply so if you're using Dropbox You' use a similar trigger
[02:34] here for Google Drive it is just adding a new file more specifically a new file to the folder so I'm going to go ahead and open this up here if it wants to work today all right so let's go ahead and choose the trigger of new file and folder then you're going to choose your Associated email account here for us we're going to do our courses account we're going to hit continue here and then we're going to choose our folder so this is going to be the folder that you are going to drag your invoice into now if you're using stuff like uh stripe or whatever invoice software you're using you could actually automate that process
[03:05] as well so where you know once the invoice is created on stripe you can automatically add it to a Google drive folder therefore triggering this there's going to be a lot more Nuance to this but for now we're just going to assume that you're just dragging the PDF into the folder let going continue here I'm going to test this trigger and we should get our new input here of invoice as that's what I called it there we go I may continue here and next we're going to have to go ahead and do another Google drive here for the purpose of actually converting this uh PDF into a doc file the reason we're going to do that is because how the as a PDF using
[03:38] zapier were not actually able to access the underlying data found within that file so let me show you what I mean by that so we're going to do upload file here make sure you choose the same account and as you'll see the file exists but not is not shown therefore we can't actually access the the text that is found within the PDF which would be a issue in order to circumnavigate that we're going to do file here we're going to we to make sure that we're converting it we're going to say true and we're going to go ahead and name it to whatever you want the new file to be called for our context we're going to keep the same title and maybe we'll add a hyphen here or a a vertical hyphen and
[04:12] we'll go ahead and call it uh data Excel this is going to be whatever you want the Google Doc to be named this is not too important we're going to continue here we're going to test this step and actually I did mess up here I needed to actually identify a specific folder so let me go and hit action here again this folder make sure not to choose the folder that you drag the PDF in if you choose the same folder it's going to create a loop therefore when you drag a PDF it's going to create a dock in that folder therefore you're triggering it
[04:42] again so like it's just going to create a loop you don't want to do that so make sure to choose a separate folder here we're going to do uh the folder of let's see what our initial folder was here real quick PDF the sheets okay so the separate folder we're going to do here is going to be the let me go to top here sheet made we're going to go ahead and continue here and now we're going to go ahead and retest this step here if I come over to sheet made we should see a Google doc appear here okay so there we go I go ahead and open this up here and this is the PDF we just had but in Google doc form now yes the formatting is a little off but it's not too
[05:14] important because as in the AI model they don't care about formatting and whether it looks aesthetically pleasing all they care about is all this data that is associated with this Google doc and you know everything relevant that we care about is found on this Google doc proceeding from here we can jump back over and we're go ahead and use a code block purpose of this code block is to grab all that data that's associated with that Google doc to do that we're going to run some JavaScript going to continue here and if you're familiar with this Channel or you've seen our other PDF tutorials we're going to use a very well-known code block that we've
[05:45] used in a ton of other tutorials this code block is very important as it allows us to grab data found in files so I'm going hit text here text is going to be uh what we're grabbing or essentially what we want to grab out of a underlying file here and then the variable we're going to inut is going to be the file but don't choose file exist but not shown we want to do file uh file text as that's all we care about in this context file text exists but not shown we're going to continue here that 3,000 is important so if you're dealing with longer files you'd want to increase that
[06:15] that's basically the max amount of output of words you want to Output in this context we're willing to take 3,000 words obviously most invoices are probably not that large I'm going hit test step here and we should get the entire underlying invoice all right there we go so we got a result here this is all the data associated with that Google doc that was from that PDF from here we're going to go ahead and show you a couple things the first thing I want to show you is the ability just to make it uh formatted so it looks nice and then the second thing I'm going to show you is how do we identify specific
[06:46] things we want from the output therefore pushing it towards that Excel sheet so let's go go or Google Sheets I've had people get mad at me for saying excel in Google Sheets just when I say it just think of as a unilateral term for you know The Columns and rows from here I'm going to hit chat gbt and let's go and start off by just formatting this so it looks nice and then we're going to go ahead and nitpick specific data points we care about and we might actually be able to get away with one AI prompt here so make sure to choose the event of conversation we're
[07:16] going to say we received uh an invoice for our company period And if you're familiar with this channel you know that I typically tell you to use as minimum amount of words as possible in prompts but when you're dealing with prompts that are just very minimalistic to begin with like one sentence just to give you context you don't have to just say invoice you can give a little bit more context so I'm going to go ahead and give the results here and just so we can make it very clear we say invoice data we're going to say generate say generate
[07:48] the invoice in the following format so make call in so what do we care about the invoice that is up to you and your discretion for us we care about all three of these so I'm going to go ahead and see if I can just try to do this real quick Copy this paste this okay perfect so we're going to go ahead and just enter here or shift return and for me all I care about are the following variables total due date service complete and stuff of this nature if you care about more add more if you care about less add less but for me that's
[08:18] all I care about so what I'm going to do here though is I'm going to add semicolons here you could in theory use a gbt 3.5 model if you want to I like using for for you know some stuff like this so it's just making sure that it's you know it's more um forgiving in this context so I'm going to uh so service complete we can just actually delete that because we actually don't know if it's complete yet we just assume it's no all right so we're going to go ahead and up the model to gbt 4 here and get a consistent output of invo data memory key random 32 character
[08:51] string uh ensures consistent outputs at scale so when this output comes out if it looks good I like it then I know for the next 10 out out puts it'll look the same so let's going to see if we like this output or if we need to repoor it I like it looks perfect um that's actually exactly what I wanted so just to gut check this entire process here as you see we have the build two is James Brown build to James Brown also provided an address invoice number we have right up here grab the correct invoice number uh total is 750 USD 750 USD um test email
[09:25] is going to be the customer's email so we know that the it's accurate just grabbing the right information it wasn't grabbing my information and it was able to interpret it correctly so I'm going to go and just rename this to format data so now the fun begins we're going to go ahead and add a uh formatter block here so there is going to be a formatter block that we're going to go ahead and use in the context of text formatters formatter block is like the dumb down version of a 3.5 Chad gbt it is more cost effective because essentially what a formatter block is is just JavaScript
[09:56] or code but their way of making it look nicer as a UI so you use formatter blocks for a very very simple test because in theory what I'm about to do right now I could have Chad gbt do to an extent but actually not true not completely true because of the fact that I actually am splitting it based off data points so sometimes form met blocks actually are important um in the context of splitting up data blocks um as I mean what I mean by that essentially is as you see from this output if I come down here this is all congested into one data point therefore if I try to put that
[10:27] separate into different data points for the columns in the spreadsheet it wouldn't work out well so we're going to go ahead and use a format block for that specific context for the input we're going to use chat gbt reply we're going to do separator and this is just the dictation that zapier uses so we're going to do new line if you want to understand more ways you can format data you can kind of hit more here and then check out their documentation we going to do a segment index here now just as reference if you hit first it would only output the first line if you hit second
[10:57] only output the second line for us typically you're going to do all and all of separate Fields it's going to allow us to identify each one of these as a specific data point there we go now if we want to take this one step further here in theory what I could do here is I could add another formatter block that removes invoice number removes build to but maybe let's just try to go ahead and reoc do this a little bit different we're going to say format don't have the titles of the C
[11:31] categories just output the requested values this might be too vague for cat gbt understand but I'm actually curious if this works and essentially what I'm trying to do here is I'm trying to remove this from the output but keep the formatting of invoice number buil to stuff like that so since we're doing a new type of output we're looking for we're going to go ahead and refresh that member key to invoice data one ahead and retest this step and I'm looking for this output again but with no invoice number no bill to no customer when to see there we go Perfecto okay so now
[12:01] when I reformat this and you'll see why that was important later I'm retest this and now we're just getting this okay there we go it's actually a lot simpler what I'm about to show you is when I was originally going to approach this topic I thought this was going to be a little bit difficult um this is actually a lot simpler than I thought I'm going to go ahead and create a new row here using that data therefore every time I upload a spreadsheet it's going to automatically put it in there if you want a more complex way of handling data and maybe the data you're handling requires uh multiple instances or multiple Rose goad and check out that video right there it's called automating
[12:32] data entry I believe it's 40 minutes long that givs you to even more complex version of how to handle data when it comes to a PDF so check that out if you are interested in more complex versions of dealing with Excel or Google Sheets so I'm going to go ahead and do an event of create spreadsheet row I'm going to continue here I'm going to choose my uh courses account here I'm hit continue again and this is where we're just going to do some easy drag and drop input values or our spreadsheet is the spreadsheet we already created here which was customer invoices example our
[13:02] worksheet is going to be sheet one so since we've already identified the rows in our Google sheet here we don't have to necessarily create any new rows so we go ahead and go to our text formatter we know apport one is going to be our invoice number because we consisted it or had consistent outputs to our output using the memory key we know our bill to is going to be output two we know that our customer email is going to be the email so we kind of proceed from here in every single one of these outputs as scale if you had 50 invoices is going to ensure that they stay in the right
[13:34] category so I'm going to go ahead and continue here and then total we're going to go ahead and put 750 and I want ah go I went ahead and left the service complete as a row here because I wanted to show you that also on top of variable points you can actually add a fixed text here so in theory we're going to add no here so every single time an invoice is added obviously it's going to say no but now you know how to add fix text as well in this context so if I hit continue here and I test this step watch this pop boom there we go so we got our line here
[14:06] and if I want ahead and shrunk this a little bit we can see all the relevant information you know maybe I want an output where it doesn't give the address maybe I wanted a separate column for the address but as you see we have successfully oh I went ahead and messed up it is not 750 is the due date is not 750 that is not the correct one come down here November 20th is the due date and if I hit continue again test this step again there we go so now we got the correct due date and as you'll notice it didn't overlap it didn't replace the underlying row therefore every time you
[14:38] would upload or drag a PDF it would automatically create a new row for the underlying information associated with that PDF therefore what we just learned today is how to successfully automatically take invoice PDFs and put them to our Google sheet and all it requires you to do is simply drag a PDF into a Google drive folder and as I said in the beginning of the tutorial you can actually take that one step further here depending on the invoice software you use if it actually integrates with zapier or has the ability for API documentation you could in theory take
[15:08] it one step further here and have it where a invoice is completed it would trigger this entire flow as well but without further Ado make sure to leave a like if you felt value in today's video um I will say that as of the last three days or just in general on this channel I found a ton of demand when it comes to how to manipulate PDFs in the context of data and using AI so seems like that's a pretty big pain point I'm hoping if you found this interesting and you want to learn more about that specific context really check out uh the PDF videos we have on this channel because I think
[15:39] this is kind of the the top of I think this covers most of the context when it comes to Automation and PDFs let me know in the comments if there's any other topic around this realm or just in general any other automation you're interested in seeing as if you're familiar with this channel I do I do read the comments and I do see either requests or suggestions for different animations and I build them out in videos like this you know therefore everyone can learn together and we can really start leveraging AI better but without further Ado if you like what you saw you can check out a playlist at the end here where I am diving into all
[16:11] 5,000 apps by zapier showing how AI integrates every single one I'm also going to leave a video at the end here and if you got in this far you probably already know this so you probably don't have to watch this one but if you don't um I might leave a video at the end where it shows essentially how to integrate zapier and open Ai and just everything fundamental understand the process and pricing in that context without further Ado I'll see you in the next video thanks for tuning in and yes surprise I'm an AI Avatar make sure to explore more here at Corbin AI where we
[16:43] demystify AI for your personal and business life until next time