Since we now have a workable shell, we can start with adding the functionality we seek. Most of this is bound to customers (invoicing / quotes / paid invoices etc) so it makes sense to start there as well.
Before we go any further we should have a way to record customer data, to do so we can make use of a very handy google tool called google forms. This powerful (and free) way to do this will save us a lot of time, that we would otherwise spent on coding the different paths.
Start by making a new form (In the top menu, click Tools > create form). In the new screen it’s a simple point and click affair. You can amend where necessary of course but I want to record the following information when adding a new customer to our “system”.
| Type of answer | Values | |
| Customer Name | Short-answer text | |
| Contact Person | Short-answer text | |
| Language | Drop-down | English, Spanish, Dutch |
| Phone Number | Short-answer text | |
| Email address | Short-answer text | |
| Website | Short-answer text | |
| Street | Short-answer text | |
| Postal Code | Short-answer text | |
| Place | Short-answer text | |
| Province | Short-answer text | |
| Country | Drop-down | Main countries you’d expect to do business in. |
| VAT number | Short-answer text | |
| Bank account | Short-answer text | |
| Payment Terms (days) | Short-answer text | |
| Invoiced by company | Drop-down | Origami Panda, Company B |
| Comment | Long-answer text |
You might want to add other options or amend the ones above to your needs. For example as I am working from Spain and doing business in all European countries I have added all countries in the corresponding drop-down. Likewise, I have little use for exact payment terms so I will in the payment terms in days (instead of NET 30 for example). With regards to the layout and design of the form, I’ll leave it up to you! If you intent to fully follow this guide I would advise you to keep the same order as I use above.
As a quick side note, I have also added a few optional entries as well:
Once you are done you can switch back to your sheet once more. As you can see there is a new tab that collect the form responses (with a purple form icon in front of it) I have renamed this sheet to Input_customerData. The first thing we need to add in this sheet is our invoice number. In my case I have the following invoice number sequence: last 2 numbers of the year – numbered sequence of 4 numbers. So for an invoice in 2019 we should see something like this: 19-0001.
In order to get this into calculated automatically we use arrayformulas (other formulas that are put in cells are being deleted an array formula overcomes this limitation). But first start by making the formula so that we get our desired output. We will use a combination of right, year, text and row functions.
It looks quite complex but let’s break it down a bit: to get the first 2 numbers (the 19 in our example) we need to have the right 2 numbers of the year of the invoice date. So this gives us the following formula:
So it first calculates the year number from a data (result is 2019) and then takes the right 2 characters from this result (19).
Ok that’s the first part done! Now let;s have a look at the last part of our invoice number. This has to consist of a 4 number sequence that increases with each invoice. As each invoice is collected in a separate row in sheets we can use the ROW() function, as this gives us the row number of a specific cell. As we have to format this number:
So this first calculates the row (-1 to compensate for the header in row 1) and then format it as 0000. If we combine these 2 parts we now have a result that outputs 190001. This is not quite what we are after so let combine the formulas and add a – in between. We can do this quite easy by adding &” -”& in between. The & can add 2 formulas together and the “-”adds the – in between. So we now should have the following formula in cell Q2.
Now we get the right output, however we do need google sheets to add it once a new invoice has been added (otherwise we have to manually add a formula each time we add an invoice which is quite annoying to do). For this we will be using an arrayformula and an if statement.
While entering data through a form is easy to set up and offers a lot of flexibility. It does lack a bit in the user experience and lacks personalization. In order to overcome these issues we can write our own script. I will not go in-depth as how we do this but you can download and amend as you need it.
Doing it like this we can have an awesome sidebar that pops out to enter new customer data!