Automation - generating monthly charges
Automation in Google Sheets The Google Sheets application allows users to create spreadsheets to present data more effectively. In addition, the application itself has a built-in...
Automation in Google Sheets
The Google Sheets application allows users to create spreadsheets to present data more effectively. In addition, the Apps Script language is built into the application itself, enabling us to write scripts that work with our sheets. Using it allows users to automate operations on their sheets even further and reduce the time spent on manual work. In this article, I will present one such solution.
What is automation?
In the times we live in, the saying "time is money" has become very apt. The ever-growing number of tasks we have to perform is not matched by the time available for them. This is where process automation comes in. Automation can be described as a program that does something on the user's behalf. For example, instead of writing the same e-mail to 200 clients ourselves, we can give the program the message text and a list of e-mail addresses, and at the press of a single button it will do everything for us.
You can read more about automation in the article: Working with invoices and data
Why this solution?
When we have a sheet with 3 companies and 20 rows of data to sort, we can easily do it by hand. Things get more complicated when that number grows to 20 companies and 500 rows. It becomes problematic and time-consuming. This is where a script that automates the task as fully as possible comes in.
Using the example below, I will show how the script works.
Example
Our sheet contains data imported from a CSV file that stores the most important information about the companies. The file includes project start and end dates, which are obtained from project management software. From these, the Google sheet calculates the working time by simply subtracting the project start date and time from the end date and time.
The script
At first glance, you can see that the sheet is not sorted. Of course, the built-in sort A to Z function could be used here. However, in case the sheet is used by someone who does not know about it, I created a button that does it for them.
Once our sheet has been sorted alphabetically, we can move on to the next stage: splitting. Our goal is to create a separate sheet for each company containing information on the cost of the services provided. Doing this by hand on a larger sheet would take who knows how long. This is where the “sort sheet” button comes in.
The way it works is very simple. Clicking it starts a for loop that takes the first cell in the client column as the pattern and compares it with the next one; if the cells are the same, we increase the variable l (counter) by one and then compare the next cell with the pattern. When the cells differ, a function is triggered that copies the previously prepared “Template" sheet, renames it after the client and copies into it all the records assigned to that company. We need the counter to know how many cells to copy.
Once the sheet-creation function has run, the value that differed from the pattern becomes the new pattern, and the sorting function continues until it reaches the end of the sheet.
Result
Once our sheets are sorted, it would be useful to send them to the clients; for this we can use the previously created “send” sheet.
The "Send" sheet
This sheet allows an employee to send the sheets previously prepared by the script easily from one place. The “sheet to send” field is a drop-down list that retrieves the names of the created sheets from hidden column “E” , which contains all the names written there at the end of the previous script's run.
After writing the e-mail, simply press the “send” button and the script will prepare the file and the message to be sent on its own. When the program finishes, a table next to the form is filled in with information about previously sent e-mails.
Summary
As you can see, automation is very helpful when it comes to working with data. With a single click, we fairly quickly obtained sorted sheets for each company. Instead of tedious work and endless retyping, we created a function that does it for us.
