Effortlessly Manage Your Invoice Tracker Google Sheets for NPOs

Invoice tracker google sheets for NPOs

if you don't pay your invoices on time people are not going to want to work with you so I created this automated invoice tracker to make sure you never miss an invoice if you haven't seen the overview showing how the invoice tracker Works make sure to go back and watch that CU this is the full tutorial showing how to build it the first thing we'll do is create a brand new spreadsheet and you can do so by going to file new we're going to rename this spreadsheet whatever you want I'll rename mine as invoice tracker tutorial you can exit out of the table popup over there to the right and then we're going to rename the tab as pending invoices this tab name does matter so make sure that you put it exactly like that then we'll select columns a through I go to format alternating colors and you can select whichever color scheme you want to use if you want to change any of the colors you can change those down below so now I have a dark blue header and alternating blue and white going down I'm going to change the font color to white to make it easier to read I'm going to change it to bold make it a little bit bigger make it centered make the text wrapped and vertically aligned all right now that it's a little bit easier to read and still A1 I'm going to type invoice ID B1 company C1 amount D1 invoice date E1 due date F1 description G1 days until due H1 category and i1 status now I'm going to right click on the tab to duplicate it and we're going to rename this new tab as past invoices and once again the name of the tab does matter and now we're going to go to cell G1 and change the header to payment date instead now we're going to go back to the pending invoices Tab and the first thing we're going to do is add a drop down under status so I'm going to select a few rows go to insert drop down I'm going to change the end of the rows I selected to be the end of column I for the first option I'm going to put paid and make that green then I'm going to change the color to Yellow for the second option and make that pending and then for the third option I'll change the color to Orange and put hold and obviously you can change whatever colors you want the only thing that needs to be the same is paid so once I save that you should be able to click on one of those drop downs and see exactly what those options are that you put in all right so now we need to do category but before we do that we're going to do a drop down from a list so we need to add a new tab and name it worksheet then in C a30 I'm going to put the word category and then you'll need to select the cells for however long you want the category list to be then we'll go to format alternating colors select whatever color scheme you want then hit done and to make it look even more like a table I'm going to add borders and that's all we need all right so next thing we want to do is go back to our pending invoices tab select H2 down just a few cells and then go to insert drop down and we're going to select drop down from a range change the end to H2 colon H select the four boxes to select the range go back to our worksheet and select cells a31 all the way through the end of the table that you created and now once we had done we can exit out of the editor and go to our pining invoices Tab and you'll see that nothing is there that's because there is nothing listed in our worksheet but if we were to add different categories so for example I'm just putting this is a test but obviously you would put real categories if I go back to the pining invoices tab you'll see those options now listed in that dropdown and if for some reason you have them listed twice don't don't worry it will only show up once now we're going to embedd a formula into our header so I'm going to click on G1 and type equals open parentheses quote put the header name quote semicolon array formula open parenthesis if open parenthesis is blank open parentheses E2 colon e closed parenthesis comma comma E2 colon e minus today open parentheses closed parentheses closed parentheses closed parentheses Clos curly brace and if you did it right it should be blank because there is nothing listed in column e for dates I'm going to go ahead and test this out so I'm going to select all of column e go to data data validation is validate and I'm going to change the validation range from starting with E1 to E2 then hit done and now that we have validation in there all we have to do is to select a date is double click on the sell and a pop out calendar will appear to make it super easy so if I were to select different dates you'll see that the calculation in column G will update automatically based on how many dates it's been from today to that date I filmed this on September 12th for reference and everything looks to be working properly so I'm going to go ahead and delete the due dates and select all of column g go to format conditional formatting I'm going to change G1 to G2 then I'm going to use the drop down to select less than zero I'll change the background fill color to red and the text color to white then hit add another rule I'm going to change the drop down to be is equal to zero and I'm going to make this orange then click add another Rule and now I'm going to change the drop down from is equal to to less than or equal to and change the zero to a seven change the background color to yellow and the text color to Black then I'll hit add another rule greater than leave it as seven change the background color to green and hit done so now if the value is less than zero it'll be red if it's equal to zero it'll be orange if it's less than or equal to 7 it will be yellow and if it's greater than seven it will be green and if multiple conditions are true whichever one is listed first on this list will take priority so now we can go back to our due date and double click on the cell and select different dates and we'll want to put a date that's less than today equal to today within 7 days and more than 7 days from now and everything's working exactly as planned now we're going to go to our past invoices tab select all of column g go to conditional formatting we'll use the drop down to select custom formula is and type equals is blank open parentheses es G2 close parenthesis and we want the start of the range to match the apply two range so I'm going to change the apply two range to G2 change the background color to none and then we'll select add another Rule now we're going to change this custom formula to equals G2 is greater than E2 and we're going to select the background color of red and make the text color white we'll select add another rule then change it to less than or equal to and make it green with a text color of black then hit done so we can go ahead and test it out but in order to do so we need to bring in a due date so I'll go ahead and paste one of our due dates there and then we can type in a payment date and since the payment date is before the due date it's green which means it's working exactly how we want it to and we can make it where the payment date is after the due date and it should turn red because we did not pay it on time so everything's working great now we can go ahead and delete those two dates since we don't need to use those anymore and we can go back to the pending invoices tab all right so from here there's two ways to proceed one you can simply put all of your invoices listed on this pending invoices tab manually and if that's the case you don't need these next two tabs but if you are planning on using a report to automate the addition of each invoice then you'll want to continue on directly so we'll add a new tab and rename that to add and it is important that it's renamed that we'll select columns a through F and then paste that on the two add column in column A we'll go ahead and delete this due date since we don't actually need it and then we're going to right click on to add and duplicate it right click on the new tab rename that as report and here is where you import your report details you'll need the invoice ID the company the amount the invoice date the due date and then the description is optional you can add that later if you want now on the two add column we're going to set up a formula where it automatically extracts anything that's on the report that is not listed on the pining invoices or past invoices Tab and we're going to embed this formula into the headers so in cell A1 we'll type equals open brace inside quotes invoice ID a semicolon array formula open parentheses filter A2 colon a comma not open parenthesis count if open parenthesis open curly brace we'll go to our pining invoices Tab and select columns A2 through the end of column a put a semicolon go to the Past invoices Tab and select columns A2 through the end of column a close curly bracket comma report A2 colon a then we'll have four closed parentheses and a closed curly brace and if you did this correctly you should see anything that's listed on the report tab automatically populate on the two add column and that's because there's nothing listed on the pending or the past invoices tab so let's test it out so I'm going to go ahead and add one two 3 3 4 five this really doesn't matter I'm just adding random numbers at this point that way we have some data so now I'll go back to my two add column and I can see that it did not populate so that means we must have an error in the formula and we do instead of it going to A2 colon a from this spreadsheet I want it to be A2 colon a from the report spreadsheet so now once we fix that you'll see that everything automatically populated so now we need to go to our pining invoices tab let's put in one of those numbers and that should disappear automatically from the invoice IDs listed onto ad and we'll do the same thing for the past invoices we'll put a different number and that should also remove automatically from this list that are needed to be added so now everything's working properly and we're good all right so we need to add some more formulas to the two add tab but before we do that to make it easier we just need to go ahead and add some dummy data into our report and you don't need to go crazy all we need is probably three or four to make sure we have enough information to test things out sufficiently so go ahead and put some company information in there put the amount you can go ahead and format that as currency while you're there add some invoice dates you can add some due dates what I did was I just simply added 60 to all of the dates then pasted the special so they became dates and you don't need any descriptions now back on the two add column in cell B1 we're going to type equals open curly brace inside quotes put company semicolon array formula open parenthesis X lookup A2 colon a comma go back to our report you can delete if anything populates then go to A2 colon a comma B2 colon B then we'll need two closed parentheses and a Clos curly brace and if you press enter you'll see that we have a bunch of Nas and that's because there's a lot of items missing but after the B2 colum B we can put a comma and now will be blank if there is no value found now I don't want to build this every time so I'm going to Anchor in the A2 colon a for this spreadsheet as well as the report and now we can use the fill handle to drag the formula to the right and we can simply change the name from company to amount but the return range automatically shifted and we'll drag that over again this time we'll change the amount to invoice date and we'll drag that one over again we'll change invoice date to doe date and we'll do it one last time this time we'll change due date to description all right so now if anything appears on the report it will automatically flow through to the two add Tab and you can see here that we have some formatting issues there's some numbers for the dates and that's because on the back end dates are actually formatted as numbers so all we have to do is change formatting back to their date and now you'll view the date inside of the number and if you wanted to you could go to the one two three and format it that way as well I don't know why I went the long way and then we'll go ahead and click on column C and format that back as currency and if you want to adjust the decimals you can very easily but since it's actual currency we'll leave the two decimals now going back to our pending invoices tab I'm going to delete the invoice ID along with the due date and when I do the calculation will disappear and I'm going to delete the invoice ID from the past invoices as well and now when I go back to the two add column you'll see that the ones that were originally missing are now present along with all their correlated info all right so now we're going to set up the Automation and whether you're watching this on YouTube Tik Tok or Instagram you can go to my link in BIO to find my website and up top you'll see the link to the templates if you want to buy them instead but we're going to click on appscript and you'll see two different lines that say invoice tracker those are the two codes that we need so first we'll click on invoice tracker D copy pending and then we're going to click on the copy to copy raw file go back to our spreadsheet and go to extensions appscript you can delete anything that's previously populated and simply paste the code that you just copied we'll rename the project you can rename it whatever you want I'll just say invoice tracker then hit rename and then we'll click save now over to the left side I'm going to click on the plus button and this will allow me to add a new script so I'll click on plus then script and I'm going to rename this as move paid because that's where we're going to put the new code so we'll go back to my GitHub then click on invoice tracker D move paid copy the raw file go back to the app script delete anything that's currently there and paste that new code in there and hit save now we need to authorize this code to work so we'll hit run go to review permissions select your account go to Advanced go to invoice tracker unsafe and allow and all this is doing is saying that the code is allowed to make the changes to that spreadsheet on your behalf aka the automation now we can go to the other code and press run and it should automatically work since it now has permissions now if you go to the two ad column you'll see that all the information disappeared and that's because the first code that we added automatically copies anything on the two add column and paste it to the next available rows on the pining invoice tab so it appears to be working properly but just to show you exactly how the flow Works let's add another line to the report so we'll do an invoice ID we'll put test five and then we'll put some amount and due dates and since this invoice is not on the pending invoices or past invoice tab yet it populates there and if I go to the appscript and click run it will execute the automation so now if I go back to my spreadsheet you'll see that it's no longer on the two add column and it's in the next line of the pending invoices tab so from there you can add any categories that you want you can also add statuses as pending or hold or whatever you want to do so I just assign random categories and I'm going to mark everything in status as pending be warned that you do not want to put paid yet because that is what triggers the automation so once you mark it as paid the invoice that you marked as paid should disappear from the pinning invoices Tab and be added to the next line in the past invoices tab all right so we don't want to run this report to bring anything from the two add column to the pending invoices tab so we're going to go back to appscript go to Triggers on the left side we'll make sure the copy to pending invoices is selected for select Event Source we're going to put time driven and then here you select the interval that you want to run I want it to be once a day so I'll select daytimer and then I'll put it at 7: a.m. you can set it up to notify you daily or weekly or whatever if there's an error then hit save now one thing to note is it will flag as an error if it's blank because nothing actually happened but that just could mean that there's no additional invoices to log and once you hit save you'll see the trigger automatically appear right there so now it's good to go now we're going to go back to our pining invoices Tab and go ahead and Mark everything else as paid and know that if you go to quickly and you start marking multiple as paid before the previous one gets deleted from the list it could unsync and copy a wrong row now Under Paid invoices we're going to select columns a through I go to insert pivot table select existing sheet and then we're going to go to our worksheet and select cell C30 then hit create we're going to drag the payment date under rows if you see here we have payment date one day at a time we just need to rightclick that create pivot date group and select year month so now everything is sum by the month instead of the date next we're going to select category and drag that under columns so this will show the date paid by category then the amount will go under values and finally we want to add the payment date under filter as well and then we're going to go into the filter and go to filter by condition use a drop down to select greater than then put the number zero and hit okay so since we had the payment date filtering by anything greater than zero anything that is blank will no longer be visible we're going to uncheck show totals for the category and payment date and now we're good we can exit out the editor so right now everything has the payment date of September 12th because that's the day that I am doing it and it's adding a time stamp I'm going to go ahead and manually update one of these to be October and you can see that it automatically updates the pivot table now I'm going to select the entire pivot table scroll over to the right side and in P30 I'm going to paste it to we click the little pencil to edit it and we're going to get rid of the category and you can do so by just simply clicking X and we're going to drag down the company instead and we'll uncheck show totals so now we have two pivot tables one broken up by category and one broken up by company now I'm going to select cells c31 all the way through the end of column O then I'll go to insert chart and I'm going to cut this chart and paste it all the way at the top now one thing to know for some reason it doesn't actually cut it and delete it so we'll scroll down to that chart and press delete now we can go back up to the top and edit the one that we are keeping so we can click on the three dots in the top right hand corner and click edit chart we'll drop down the column chart to be a stacked column chart and everything's looking good so we have the category up top and the payment date on the x-axis now we're going to go to customize and select chart and access title chart title and put it as paid invoices then we'll select chart subtitle and say buy category then we'll use a drop down again to select horizontal access title and we'll put payment date and then we'll use the drop down again to select vertical axis title and this time we'll put amount paid so now the chart's looking like a chart so we have everything that we need and now we can exit out of the chart editor now if you look at the Y AIS you'll see that there are no dollar signs next to the amount paid and that's because it's pulling from the pivot table and the pivot table has numbers as opposed to currency but if we select everything and change to currency the chart will update automatically as well so now we have this like we want it so I'm going to click on the chart copy it and paste it over here to the right and I'm going to drag these and put them in a different position just to make it look a little bit better now we'll click on the three dots in the top right hand corner of the new chart to open up the editor I'm going to select a different date range click on the four boxes clear out the date range and then click on p31 all the way through the end of column Z and now once I press okay instead of the data being based off of the first pivot table it will be based off the second pivot table instead but before we see the chart I want to go ahead and format all of this as currency just like we did before so now I can go back up to the top and see everything looking exactly how we want last thing we need to do is double click on this buy category and change it to buy company instead so now I can exit out of the chart editor and I'm going to drag both of these charts down a little bit so we can add slicers just above them and if you don't know what a slicer is it's just a way to have specific filters at a different location than way you would actually normally filter so I'm going to click on one of the pivot tables then go to data add a slicer and since it's linked to the pivot table all the different headers that you normally would have for that table that it's based off of will appear on the right hand side I'm going to select company then copy and paste the slicer and then I'll select category I'll copy and paste the slicer again and then this time I'm going to select payment date so now all three slicers are built that I'm going to add you can add as many as you like and I'm going to drag these over to make it centered a little bit more so it looks better and if you have them equally spaced you'll see a little line in between show up and now it's all good to go so let's test it out by going to company and selecting one of the different companies and you should see that both of the charts as well as all the pivot tables will only show data related to that company now we can go back into that pivot table and select all and we can do the same exact thing for any of these but since we have very limited data I want to go back in and run the report and add some more data in there and we might as well test out the whole flow in the process so I'm going to add some random invoice IDs I'll put test five I want to have the same company as one that I have previously listed so I can test out how the charts look I'll add a random amount as well as random invoice dates and I'm going to add a second one as well so I'm just going to go ahead and copy everything that's in row five and I'm going to change the invoice ID so that way it's Unique now on my two add column I'm going to go ahead and run this and as a reminder you don't actually need to run this in real time since you set up that trigger it will run once a day for you but since we want the results now I'm going to go ahead and hit run and it will disappear from this list go to the pending invoices Tab and now all we need to do is assign a category and use one of the categories that you've already selected so that way you can see how the chart will look the days until due will automatically calculate based off of the due date and you can add a description if you want and now we can go ahead and Mark both of these as paid to move them to the bottom of the paid invoices tab in the time stamp is going to default to today just for example purposes for testing things out I'm going to change one to next month and change one to last month so now if I go back to our worksheet all the pivot tables are added automatically and the charts are updated automatically as well so now for category for example I can select one of the categories and I'm going to select one of the categories where it has multiple so I'll select this and we can see how this category has changed over time and I can see which companies they're correlated with as well to see if there's any investigation that needs to be done and I can do the same exact thing for company so I'll select test five for example and I can see that test five has decreased month over month but they're related to different categories and you can use both of these filters at the same time to see exactly how a category is changing for a specific company now still on the worksheet tab we're going to select row 30 all the way through the end of the spreadsheet right click view more row actions group rows and now you can click on the line to condense them and as you see all of our visualizations are gone but you can select include data for all and now you'll still see everything in the visualizations whether or not it's visible below so that's just a good way to keep things clean if you want to see it again all you have to do is press the plus button in the bottom left hand corner and you can see see everything add more categories or whatever you need to do now we're going to go back to our past invoices Tab and click just to the left of a and just above one to select everything and then click on filters and now if you need to inspect a specific company or anything further all you have to do is filter and you can see exactly what's going on all the different amounts and you can see the descriptions that are input and that way you can follow up ingly so that's everything and your automated invoice tracker is ready to use like and comment for more