In my quest to create an automated workflow that manages time and costs for one of our clients, Five by Five, a growing consulting company, I first encountered the wonderful world of Google Apps Script. Managing the different Google file types (docs, sheets, slides, forms, etc.) with code is powerful and not that tricky. But the road can be long and sometimes a bit bumpy. This article is geared at helping you get past some of the more confusing obstacles that you might encounter when using Google Apps Script in the Google Drive ecosystem.
In the case of our client, a consulting company, they generate leads, sign new deals, assign new projects to team members and track their progress and time spent across these projects. Most consulting companies use time tracking software or project management tools to track billable hours, while some don't do it at all.
A simple workflow...
Using Google Apps Script, I generated a Google Form each week based on our client's project information – things like the names of ongoing projects, the projects' details and the list of employees. We then use Apache Airflow to send employees the link to the form, through Slack, every evening. Each employee fills out the form and declares how many hours they spent on each project.
The Google Form captures these responses and stores them in a Google Sheet. I then use this information along with the responses provided throughout the week to calculate the total hours spent on each project, for each employee in a given week. I put the outputs of these calculations into a new Google Sheet. I then use Airflow to send this end-of-week report to project mangers. The long-term aim is to gather enough data to create clear insights about time and cost management, and in the future, predict metrics like the cost of newly signed projects, the correct time to sell to clients and estimates of employee bandwidth.
... but a confusing obstacle
At the end of every week, the calculations are executed in an intermediary Google Sheet, which is then archived. In order to do this, the Google Sheet I use to make the calculations must be updated in real time. Because the Google Form that collects the hours spent on each project from employees is sent every day, new data constantly needs to be taken into account.
However, one of the features present in Google Sheets essentially blocks any "re-calculation" within a cell as long as the parameters in the cell's formula have not changed. For instance, Sheet1 includes formulas that refer to another sheet, Sheet2. While cell values in Sheet2 may change, Sheet1's formulas that refer to Sheet2 do not. Therefore, when Sheet2 is updated (deleted and replaced with the new one), I get #REF! errors in Sheet1. The reason is because Sheet1 only displays the cache of the previous calculations, since Sheet2 has been temporarily deleted the formulas refer to a sheet that does not exit.
I did notice that after a couple of hours, the formulas run again and the Google Sheet is updated. However, in the context of workflow automation, where the expectation is to have updated information in real-time, this delay can be quite annoying.
A couple of solutions that generally work
When I did some research to solve this issue, two solutions came up and seemed to work for most cases. Unfortunately they did not work in the context of my project, however they might work for others.
Using the Google Apps Script .flush() function
After some time, I tried using the .flush() function. This function forces the code to wait for all changes to be made before executing the rest of the code. In the words of Google Developers :
Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.
Calculations in Google Sheet settings
If you have formulas using NOW, TODAY, RAND, RANDBETWEEN and you have trouble with the calculation process, then it may help to change the settings. When opening the Google Sheet, under File, Spreadsheet settings you can choose the conditions and frequency your calculations re-run.
The little trick that worked for me
The best thing about Google Apps Script is that it mainly mimics how someone would use the features in Google's file ecosystem with code. Since I did not find the previous solutions to be useful, I decided to try several manual operations in Google Sheets in order to find an appropriate solution and subsequently translate it into code.
I discovered that both cut and paste and copy and paste functions give the illusion that the formulas and their parameters have changed, so I decided to encode a cut and paste of all the cells in Sheet2 in order to update the cells in Sheet1.
Now, the Sheet runs the formulas again with instant results. The solution is clearly not groundbreaking, but it does work for this kind of issue. Hopefully, in the future, I'll find a feature developed by Google that allows me to bypass this sort of inconvenience in a more elegant way.
.svg)
.svg)
