Collaborate on Automated Invoice in Google Sheets for R&D with Ease Using airSlate SignNow

Automated invoice in google sheets for R&D

hi this is greg from business automated and today i will show you how to create zero invoices from data inside of your google sheets let's get started the first step is to create a google sheet that will contain all your invoice information so you can see that here i have already prepared a template for for that purpose and you will see that there are two different sheets inside of this document so the first one is invoices and the second one is line items let's start with the invoices first inside of the invoice sheet we have following columns id which is our internal number of the invoice does not need to match the number of the invoicing zero then we have the customer name so that we know for who this invoice should should go to and the next step is the customer email we'll use this to identify customers inside of inside of xero the next step is reference and then there are two technical fields which is create invoice status and finally there is the zero invoice number that will receive back from zero at this stage we can leave those fields blank it is possible also to add other fields such as date or due date but let's keep it simple for the time being let's move on to the line items inside of line items the first column identifies to which invoice this line item belongs this way we'll be able to link the line items to a specific invoice the next step is the description of the invoice quantity amount and the account code to which will be booking this particular invoice if it's sales invoice most likely it will be 200. for the tax code you can leave it blank so it will take the default settings from your xero now how do we move all that information into xero for that we will use a tool called integromat you can read more about integromat in the description below and once you set up integromat account the first step will be to create a new scenario what we will start with is adding a new google sheet module all right and here we will select search rows first step will be to connect google sheet with your google drive and google docs once this is done you have to select the spreadsheet that we have just created and we will start searching from the sheet that we have created the invoices in which will be invoices the first row contains headers this is correct and inside of the filter we will put two filtering variables the first step will be create invoice we want to create invoice only for the fields that we have marked yes and the second rule will be the status should be empty does not exist means there should be no variables okay so let's test this first so let's run once we have received zero return values we have zero bundles this is because we have not marked anything as yes so let's change the create invoice to yes and let's repeat this you can see that we have found data for the first invoice okay so now let's proceed to find the customer for this invoice inside of zero let's add zero module search for contacts the first step is to authenticate xero and select your organization the next step is to select what are we going to search for to find the customer we'll select the field and we will select email you could also use name but it's always more unique and safer to select for emails because we know those will be unique in this case we say filter emails that equal to the email that we have received from google sheet okay let's test this part as well all right you can see that we have found the customer so that we are able to create invoice for this customer but what in the situation that we have not found a customer inside of our data let's check the situation as well i'll create new invoice for a new customer and let's make sure that we keep the ideas number two so that we don't confuse the invoices okay let's run integrate again and you will see that in the first operation we have found the customer in the second operation we have not found the customer so that means we'll create a new customer we will go this route only if the total number of bundles from the previous step is equal zero zero means no information have been found and we'll create a new customer with the name that will come from our google sheet customer name and we will also add email so that we can find this customer later okay so now that we can cater for both scenarios for the existing customer and non-existing customers will do a small trick we'll basically copy the search for contacts and we'll put it again here so we will search for the contact again after this contact has been already created this will assure that whether this contact is existed or not by the time we'll get to this module it will be already existing in the system so we will execute this lag in all circumstances so there is no filter necessary here what is important though is the order of execution so let's click on the magic wand and you need to know that inside of integromat the execution order follows the top branch first so in this case if there is no no contact found new contact will be created and then after that the second branch would be executed which means we would found that contact because it was already created here however if we disconnect this branch save and connect it again this situation would change let's click on outer line and you can see that in this case the modules have switched places this means that the second search will be executed for the second time without the customer being created yet so it's quite important to know that execution order follows which branch is at the top to know which branch is on the top is not what's visually shown in integromat it is what is being shown to you once you click out to align to change the order of the branches you simply need to unlink and the last connected module is basically the last module in terms of the order so let's reconnect again let's save and then let's reconnect again this was the module that was connected second after the first one clicking on autoline will switch them around so this execution will be executed first and then this module will continue so let's run this one more time so we can see that for the customer that was missing in our database that customer has been created and by the time we got to this second module for search both customer were found so you can see the customer number one and you can see the customer number to integrate okay so now once we are sure that both customers are inside of zero we can proceed now to creating an invoice okay so let's select create an invoice module okay and let's connect it to the second search module here the first step will be to select whether this is a bill or an invoice in our case we are issuing invoice accounts receivable and the next step we need to have the contact id which means the customer we will take it from the proceeding module here contact id okay now let's manually add the line items just for the demonstration okay so we need description quantity and amount we can select tax code but we don't have to do that neither tax amount zero is amazing in this regard that it will take some of the default assumptions for us okay so let's add this line item and let's click ok and let's run this scenario as a test all right you can actually see that there are two invoices that were created straight away because we had two invoices on our list okay so let's go back to zero and see whether they are here and you can see that both invoices are here and what they are missing they are missing the reference and they are missing the details that you want to have inside so let's just go back and fix this okay so let's first add the reference here you can show advanced settings and you can customize date if you do not select it it will be today if you don't select due date it will be the current due date you can decide whether the line items are taxed inclusive or exclusive you can also customize the invoice number for this case we'll keep it as the default invoice number just to follow the numbering in zero and then we'll add reference from our google sheets we can select the branding theme let's select very orange invoice we can change currency code and put other settings over here what is important is we can select status here so we can authorize the invoice straight away we can mark it as submitted or we can just have it as draft in beginning i would recommend keeping this as draft later you can authorize them immediately and you can also immediately send them out to contacts which means the moment you mark the invoice to be created inside of google sheets the whole execution happens and your customer will get the email with the invoice for the time being let's just keep it in the draft stage okay now the question is how do we get the line items to be placed inside of xero for this we will have to do another search inside of google sheets so what we'll do we'll copy this module here and we will be searching the second sheet line items and for the filter what we want to return is only line items that belong to the particular invoice that we are generating so this was the reason why we have created this id in the beginning so we will use that id in the beginning and then we can remove the tax code in this part so that's everything let's disconnect it from the create inverse module just to see how it works and let's just keep one invoice to be created at the time so let's delete the second one and let's test the result so now you can see that we are having the line items listed over here with the names that are similar to what are the names in zero but they are not exactly the same because you can see the dc and the numbering for the columns inside of google sheets so we need to do a bit of transformation so that next step will be to use a module called json json and aggregate to json okay so add it here behind google sheets and then as the source module will take the google sheets module before and then for the data structure we will add a new data structure let's call it zero line items and we could manually add all the line items data required like description quantity unit price and so on but to make it easier you can also copy the template of the data format which is in the description of this video or on the website of xero and you can use this generator here and copy paste this inside of the sample data so you can see that there is a sample data here let's click save and this will create the data structure that is needed for zero line items okay let's save and now it's a simple matching exercise with the data that came from google sheets we are ignoring the item code because we are not tracking any items in our case so let's add description quantity unit amount we can ignore tax that means the xero will use the default and then for the account code we can put 200 for sales as a hard-coded value or we can use the value from google sheet in case you would be having more varied sales accounts you can individually select those otherwise you can just to hard code the 200 here to avoid any mistake okay let's see what happens so we can see we have received a string with all the data gathered together into one single json string but we need to change it into integromat object again otherwise it will not recognize this string inside of the zero module so this might be a little bit tricky maybe there is a better way how to do it but this one works so now we need to transform this to object inside of integromat so we will use parse json and let's click ok let's see what happens now okay and now you see that we have output of two bundles with the data that is coming from our google sheets it's pretty much similar to the output that we were getting inside of google sheets it just has slightly different names for for each of the variables so it's the names that are important to match zero requirements so that it recognizes this a specific amount and and this is the way why we're using those json modules okay so now here we need to just aggregate those two bundles into one we use the array aggregator let's select the preceding module and let's scroll all the way down and let's just select all of the values that are there if some of the values inside of json have no value if there is no or nothing in them they will not show up so for your test data you just need to be sure that any data that you send for line items if you'd like to send tax code you need to have some dummy data before you start experimenting with it here otherwise it will not be visible okay let's click ok let's run it one more time and we have a single integromat array with two items inside which are matching our requirements for zero okay let's connect zero module here and now let's get rid of this mock-up line item and let's click map and here we'll use this array as it is okay and let's give it a go okay let's see what's inside of zero great we see that we have another invoice with the correct reference over here with view amount already here and let's see what's inside and both line items are correctly created over here and you see we don't have a due date because there is no default setting on this account if there would be a default setting it would automatically get added and you can see all the other information like tax rate has been added by by default and right now this invoice is ready to approve or email to the person that will receive it okay let's add one more final touch so we don't want to be sending out the same invoice all the time we just want to update status over here so what we'll add at the end is update row let's select the spreadsheet and we'll update the invoice sheet for this for the row number we'll take the row number that we were operating on from the first module row number and what we want to update over here we want to change the status to whatever status of the invoice we have created so we could either type draft manual here or we could take the status directly from zero which is safer because we know that this is the actual status and also we would use the zero invoice number here so that we can find this invoice later okay so let's create this invoice one more time we can see that this invoice has been created again and we can see that this invoice has status draft and we have the invoice number over here and this way because we have status here when this scenario runs one more time there will be nothing to to look for there will be nothing to look for actually if there is nothing found we just need to put a filter to stop it from execution so if the total number of bundles equals to zero it's not face no invoices to issue okay so let's issue another one so in this case we just move it to yes and start this scenario over here mistake it should be not equal to zero so if it's not equal to zero then it should be executed let's run it one more time the invoice has been created now another one will not be created we can see this additional invoice here with multiple line items and we can also see this invoice here together with status draft obviously we don't want to be clicking manually uh to start the scenario this is why we would use scheduling and we would set this to be checked for example every day or a specific day of the week if you would like to issue the invoices on a specific day of the week let's say tuesday okay i hope that was useful for you guys and if you have more questions about xero or other business automations then let me know in the comments and please subscribe for more business automation videos thank you [Music] you