Last week I went down a slight rabbit hole while trying to figure out if it’s possible to create an automated invoice generator using Monday.com with Google sheets. Which was surprisingly more difficult than I thought.
Originally this whole tangent started with following my brother Gideon’s tutorial on how to create automated invoices using Monday.com, the primary function was the following:
When a button is pressed inside monday.com, the invoice generation process starts:
- First, it pulls information on the invoice, including the Name of the invoice, invoice number, etc.
- Second, it gets the customer info, such as Name, address, email, and phone number. (Which pulls from a separate list on Monday)
- Next, it grabs the list of subitems; in this case, subitems on Monday will be the items ordered on the invoice and renaming all of the information into variable names, making it easier to pull from later.
- It uses the “{{VARIABLE}}” tag, which registers inside of make.com. Then it creates a Google doc from a template I started at once. It uses Google Documents and downloads a copy as a pdf. Then it inserts it into monday.com onto a predetermined column.
- Once all of that is done, it updates the status in monday.com to indicate it ran successfully, then deletes the original Google doc so as not to take up space inside your Google storage.
This is all done within 10 seconds, excellent, and well organized. Most of it runs pretty well. The setup process is pretty self-explanatory when creating it and relatively efficient.
The only downside is that admittingly isn’t even that bad. If any items are not filled in, the slots will be blank. If you had two things and there were six slots, there would be four extra empty rows, which can be annoying but isn’t detrimental to the functionality of this invoice.
A reason why you can’t have make.com automatically delete extra rows is because of the Google API for Google Documents. Currently, you cannot add HTML straight through Make. But a platform that you can edit easily through Make is Google sheets. Google sheets work on a row-by-row basis, as you can delete whatever row you want specifically. Since naturally, it isn’t in.pdf format.
An issue with using Google sheets is the formatting. Naturally, Google sheets don’t have its file type, nor does it default to pdf when you export (although you can choose to export to pdf.) This wasn’t easy to figure out. For one, I got a download link to my spreadsheet, and when you went to it, it started a download. But two issues; Monday’s “upload a file” module did not support using links to insert files into their columns, the second issue is that the file was only downloadable to those who had permission to download it; I could set it as “public” manually but not automatically. So if Make tried downloading it, it would deny its request due to invalid authentication.
It took a lot of time to Google and research different ways to save a file automatically. I ended up asking in the make.com forum. I then got a response a couple of hours later.
You should use the “download file” module of the Google Drive application and toggle ON the advanced settings and select convert file to pdf. This way you can use your PDF downstream in your scenario without making your document public.
In this case, I would need to use the “Google drive” module, which takes more work to set up when using personal accounts (as listed in the documentation.) But once it is set up, it works amazingly well. Very simple when uploading to monday.com from there.
Next was figuring out how to delete the rows and how many to delete. This was a more straightforward task. I used two math modules, one with the following formula:
This calculates what row to start deleting by using the number 11, the row at which the spreadsheet item list starts. “___IMTAGGLENGTH___” is a value that contains how many subitems there are (or, in this case, items for the invoice.) If there were two subitems, it would output the number “13” I then use this with a “delete a row” spreadsheets module, which deletes the row “13” (or whatever the math output is.)
Next is another math module that uses 23 (the ending row number of the spreadsheet’s item list) and subtracts it from the previous math output. Using the last number example, this calculation would be 10, indicating to delete ten rows (there are 12 rows total in the spreadsheet)
and finally, the actual deleting module setup. Which consists of a “repeater” module that will repeat any action after as many times as you want, in this case, 10. Which I set to be the output of the second math module. After the repeat module is a “delete a row” module that deletes the row that the first math problem generates, it would delete row “13”, but since it is inside of a repeating function. It deletes rows 13 and 10 times. Since Google sheets work in a way when you delete row 13, the row after becomes row 13, thus when deleting row 13, 10 times. This I found to be the most efficient way, as there is no way to delete multiple rows in the “delete a row” module.
You can see the final result in the video below:
