Automated Invoice in Google Sheets for Security

Automated invoice in google sheets for Security

so your freelancer struggling with invoices and keeping track of your finances trust me I've been there done that for over five years now my name is Marvin Aziz I'm a freelance designer and developer and when I first started my freelance journey I knew literally knew nothing about how to run a business right I was facing challenges with managing accounting um streamlining my my invoicing process and dealing with legal aspects especially when I had no money to pay those premium software services or even a tax accountant right but guess what I found a way to actually automate the whole process for free without a single line of code in this video I'm actually going to show you how to streamline your invoicing process within automation using Google Sheets Google Docs and make formerly known as integromat so if you're ready to level up your freelance business and streamline your invoicing process stick around in the end I'm gonna offer you to download my invoicing template the blueprint for make and also the Google sheet I'm gonna give it all to you if you want you can use it for free all right guys so let's jump into this um today we're going to use Google Sheets Google Docs and make if you don't know Meg make us one of my favorite applications all time um formerly known as integument I've been using this for years and um it allows you to connect multiple sources of data right so you can connect basically any application to one another and all of that without using code if you don't want to you can also Implement some code but it's mainly a no code solution to automate stuff right it's very similar to zapier but I definitely prefer make overzap here because to me it's just it's it's basically zapier on steroids right you can do a lot more with it if you are interested to sign up please use my link below it's an affiliate link and I earn a small Commission in case you decide to go for the paid pricing there's a free tea as well so you get a thousand operations per month it's going to be free forever so yeah definitely go ahead and create an account there okay so let's start by giving you a preview of how it's going to work in the end right so this is make this is the automation I've set up for you guys we're going to walk through this step by step so don't worry it's not it's not that complex right I'm gonna hit run once and we see oh there's something happening here it's searching it's creating it's doing stuff right there right so what just happened basically I've got this Google sheet right here we've got some data in here right so this is invoicing data there's a sum there's some vat um there's an address of the customer there's a description for the invoice customer name the project invoice number and that's a custom field right so when I first started out I wanted to have some purpose within my within my business right implemented and one of the ways I accomplished that was by planting trees with a percentage of the money you make through freelancing right so so far I've planted a lot of trees so that's cool and I have actually integrated this into my invoice voicing by displaying the number of trees that have been planted with that invoice so if that's a idea for you as well feel free to copy that I got some great feedback for doing so so yeah that's the first step right so we've got some invoicing data in Google Sheets then it basically goes ahead and copies this template so um In The End by the end of this video if you stick around I'm gonna tell you where to find this template you can download it for free and use it it took all the data from here and replaced the placeholders in that document right and then what I did it downloaded a copy of that document of that invoice and uploaded it into a specific folder where I can always find it right so this is my YouTube invoicing folder right now it created a folder with the name of the year of the current year right and then it created a folder with the company name right John's LLC that's this one right here so if you double click into that you're gonna see oh well we've got some invoices in here so uh what this automation does is actually create one Word document out of it and copy that file into a PDF right so if you open that PDF you're gonna see oh we got some work done we've got an invoice with the invoice number one with the current date with a project name we've planted 240 trees today client number one the sum is that is the vet amount stuff like that right so it's a very simple template but you can adjust it as you wish and that's really easy to do so right so all of that has been done by this automation just by inserting data in this table Isn't that cool so let's actually scribble something down so you better understand we've got this information right data in Google sheets then what happens um it takes that data and it actually puts it into a doc right so from there it's gonna create a PDF and this PDF has to go somewhere right so we've got our folder YouTube automation folder right the automation is going to create another folder within there with the current year currently 23 right and from there it's going to create another folder with the name of the company and within that name within that company name folder we're going to have the dock and the PDF right and that's where the money is right here okay so that PDF is actually going to be put in here easy right so how do we actually do this in make okay let's have a closer look to what is actually happening here inmate you are going to need a certain trigger and that trigger is supposed to watch for new data coming in right so as soon as we enter a new line and a new row in here it's supposed to take that new row and create a new invoice out of that right so what's that trigger going to be you can click on that little plus sign here to add a new application and you're gonna want to search for Google Sheets and it's currently not here because it's already added to my favorites so if you if I click on here you can see the triggers that are possible within make right so you can either watch rows what changes perform a function and then you have actions right where you can actually manipulate the Google sheet and you've got searches where you can actually search for data within the sheet okay so what we are going to use is the watch rows trigger right so this is the one and you can simply add a connection to your own Google account by clicking on ADD and then signing in with your Google account right so um I am you need to choose your drive and you need to choose your spreadsheet right so um you can simply click on here and I've I've been using the YouTube automate invoicing spreadsheet right it's a spreadsheet name the sheet name is invoices by the way I'm gonna share all of this with you guys by the end of this video so you're gonna get the blueprint for make which helps you to actually set up the complete automation within make um you're gonna get the invoicing template and you're gonna get the Google sheet as well so um in this sheet you actually need to only insert data for the green columns right so you need a customer ID you need the project you need an invoice number and the sum and the description then there's another sheet in here which is called customers where you can add your customers it's basically here CRM in the end right so um I can add another customer in here with the id04 and the company name is web to the flow [Music] um some Street um random number for now it's in Berlin and the contact person is Marvin disease right if I want to make use of that of that customer now of their customer data what I'm going to do is simply add the customer ID in here and you can see that it automatically populates the sheet with the data of the customer right easy so the project is YouTube Project the invoice number is number two it's five fifty thousand Euros right it's actually um you can't have the Euro sign in here otherwise it's messing up though invoice template we've got that you've got the sum and I need a description so YouTube video content [Music] gonna plant two thousand trees with that one okay cool so um yeah it's looking for those invoices the type of contents headers yeah you can specify the rows to watch and you can limit it okay simple then what you want to do is open the Google Drive application right so go ahead and search for Google Drive and not you can click down here and what you want to do is search for files or folders and you want to search for you've got your YouTube invoicing folder right and within there you want to retrieve folders you want to search within file of folder names and you want to search for something that is this I mean this basically translates to 2023. [Music] yeah and make you can actually use functions and variables very basic you can click on that little calendar icon up here and simply choose a date or what you can do as well is you can format the date that's a very basic JavaScript function you can click on it and what you want to do is you actually want to insert you want to insert the date right so that's the date field of your Google sheet you can simply insert it here the format is going to be why why why okay so um I wanted to search for the exact term and I'm going to limit it to one close this okay boom all right so now it's searching for the folder with the folder name of the current year the folder exists right the condition up here you can click on the link between those dots if the folder exists the name of the folder exists right if it found something you are going to search for another folder within that folder right so now we are actually in here right boom we are in the current year folder and we want to look for that company name folder okay so we're gonna choose that previous folder which is this one you can see that it's actually pulsing and what you insert there is simply just the file ID right easy gonna retrieve the folders and you're gonna query the customer okay so what now either it's going to find a folder with the customer name or it's not right so if it's going to find a folder it's going to copy a template so I've got my templates under templates YouTube invoice template right so I've just chosen that file and you got to specify a new folder location which is going to be the previous folder we found right the the folder in the current year in the name folder so right here we want to place that document okay so and we can name this invoice underscore invoice number underscore customer underscore current year and month so right here is how you actually write it into a current year and month right so we got that file in the folder now what you're going to search for Google Docs if you already used it you're going to find it right here and what you want to do is you wanna replace text in a document right what are you doing there we've got that template let's have a look in that template we've got weird stuff like contact in those curly braces client name client three client plz City description the price net date all those curly braces are what I call variables right so you want to replace those variables so contact is going to be replaced with a contact person out of the Google sheet right client name is going to be replaced with a customer name on the Google sheet and so on that's what you do the planter trees the price the client number dv80 all of that stuff has to be replaced okay in the copy of that file right don't manipulate the template okay so as soon as you replaced all the information you actually going to find a module that is called downloader document and you're going to download that document and it's just the documentary created here and replaced and replaced information in here and the type is going to be PDF right so that's where we actually converted into a PDF right here okay and now you upload that file to the folder you created previously boom that's basically it right so that's one path this is the path where the year folder exists and the customer folder also already exists but what if the year folder exists and the customer folder does not exist well we've got a path right here right so customer does not exist and this is by the way the fallback boot right so it's first going to check this one right here and if this is not true it's going to come down here so customer does not exist in the folder structure it's going to create the customer folder and then it's going to go ahead and do the same stuff with previously talked about right so what if there's no current year folder and there's no customer folder right happens every year well it's going to create a folder first and that's the path down here so it's gonna we've got that fall back root again so it's going to create a year folder it's going to create the customer folder it's going to copy the template replace the text in the document download the document and upload it to Google Drive so that's what happened here easy right and that's it guys that's how we automate our invoices now you can you could actually go ahead and do stuff like um email to client email invoice to client right and what else you can send a slack message to be informed about that the invoice has been sent to the client or you can actually you could what else could you do you could connect your calendar and create a new entry with a reminder for 30 days right payment received you could do all of that stuff easily by using make I really hope You Now understand the power of using make for your workflows it takes away the stress the hassle and yeah gives you a lot of time to focus on others important stuff within your business right so I can highly recommend using it really it's saved me so much time yeah so what I'm going to do is actually I'm going to export that blueprint and you are going to be able to download it for free all you need to do is to go on the click the link in the description and type in your email address and I'm going to send it straight to you but not only the blueprint you're going to also receive that sheet right here with customers and invoices and you're gonna get that template you can play around with right so you don't have to use it but um yeah it's a it's a very simple basic template that's working already out of the box right so I'm gonna save you a lot of time here I hope you enjoyed this video if you did make sure to hit that like button write a comment um I really appreciate you guys watching this and hope to see you in the next one