Using Google Apps Scripts to automate conformance reporting for clients

CGS have been adopting Google Sheets for most reporting in recent projects. Utilising this versatile tool along with some custom "Google Apps Script" we have been able to offer our clients close to "real-time" conformance results.

The basic procedure is:

  1. Conventional on site survey to QA the finished asset
  2. Once the data has been manually checked and verified as accurate, it is processed and exported in one mouse click in our CAD software 12d using a custom "chain" and "macro"
  3. The data is then automatically imported into its respective tab of our clients conformance sheet using a "Google Apps Script"
  4. Finally a pdf is exported of newly added data, as well as a snapshot pdf of all current data and sent to a distribution list of client personnel dependant on which section of the job the data is in.

The beauty of this procedure is that, other than the conventional survey to collect the data and the one mouse click in 12d the rest is entirely automated!

Also, as well as having the pdf exports emailed, we also give our clients access to their own copy of the entire Google sheet, meaning they can have current data minutes after it has been surveyed in the field.

The subgrade example above shows a typical sheet with the custom menu options for use by us and the client. Giving them the ability to filter by chainage range and date range in this example then export a pdf of the area/layer in question to a shared Google Drive folder with the correct standardised naming convention, date, signatures, logos, etc all done automatically.

Another advantage of doing these tasks via a script is inbuilt error checking. For example below is a warning that appears if gaps in the data are identified greater than 50 meters in chainage:

Prior to the use of Google Sheets this process was a painfully laborious task taking hours every day for a data processor. As well as the unavoidable manual QA checks on the data, the data processor would also have to do manual exports from 12d, then formatting in Microsoft Office and other applications using templates, and then manually emailing to relevant personnel.

Using this system our clients have current data at their fingertips shortly after construction is completed and are able to interrogate the entire data set or filter it as they wish without having to wait for an email from an ever frustrated surveyor to get back to them!

If you would like more information about having these systems introduced for your reporting get in touch for a demo.