Our Solutions = Working Smarter
We're proud of all our solutions, but here are some favorites.
We're proud of all our solutions, but here are some favorites.
Our client, a small business start-up company, utilized a business model where independent contractors provided services on a project basis. The contractors work on short term projects that build the company's product. The current commercial software products available on the market exceeded cost allowances. This business model created a need to track hundreds of projects with thousands of contractors where all contractors had multiple Statements of Work (SOWs) and many invoices. Tracking this manually was not an option.
Using Google Workspace, build a tool to manage projects and vendors. The tool must align with the Project Management Institute's (PMI) Body of Knowledge including cost, schedule, quality, risk, procurement, and resource management. Vendor management features must have high confidence with minimal error.
Using an iterative approach, KilPen built a solution based within Google Workspace that used Sites, Sheets, Forms, Docs, and various APIs. Processes diagrams were built out for each business activity with supporting Google Workplace automation. Various dashboards were created providing insight into project work, including an Integrated Master Schedule (IMS), risk register, stakeholder register, and a deliverables tracker. A centralized Master File housed all financial information related to project cost accounting, including the ability to forecast Accounts Payable.
Iterative over 3 years
Our client operates in a tight margin industry where budget wasn't available to purchase a commercial vehicle maintenance tracking tool. With growth in their business, tracking vehicle maintenance and licensing requirements became more and more difficult. A centralized tool was needed to help retrieve service and mileage reports from technicians and drivers. Also, email reminders were requested to ensure services and licensing renewal was performed timely.
Using Google Sheets, Forms, and Gmail, build upon an existing Fleet Manager sheet and Google forms to upgrade code and eliminate existing errors. Develop new reminders to help managers keep the business on track.
After review of existing code, a decision was made to completely rewrite existing code. A data model was built so that an object-oriented approach could be used. The new code was developed to allow for scaling and maturation of the product over time. Upon completion, the client continued to use the existing Fleet Manager tools, but with high assurance that the back-end code will keep the values updated properly and service reminders are sent reliably.
One month
Our client valued consistency in branding within email. Concerned that employees creating their own signature blocks in email would result in branding conflicts, KilPen was consulted to develop a company-wide signature standard in Gmail.
Using Gmail and Google Cloud Console, KilPen needed to develop a technical solution that would replace user signature blocks domain wide with a HTML template that pulls user details from the Google Workspace company directory.
Using Google Apps Scripting and Google Cloud, a tool was developed that iterated through the Workspace domain's users and executed a Google API call, with OAuth2 authentication, to replace their signature blocks. The signature block was dynamically recreated each day based on the HTML template and the person's name, title, address, and phone number stored in the company directory.
Three weeks
Our client expressed concerns about poor product quality exiting their product development workflows. Due to the nature of their business model, many contributors play a role in development and the quality of their contributions vary.
Using Google Workspace, develop a process and supporting tool to enable a quality check of each contributor early in the development cycle with the goal of reducing the risk of errors and rework later in the cycle. Industry best practices related to risk identification and risk treatment should be used.
A gate review concept was used where a quality team reviewed a small sample of each contributor's work within 10 days of contracting. The quality team, in coordination with a project manager, evaluated the product and decided risk treatment options: mitigate, avoid, transfer, or accept. For efficiency, automation was used within Google Sheets, Gmail, and Google Forms to track when gate reviews are due, notify by email when action is needed, and submit results by form when a review is complete.
Five weeks
Our client identified significant waste related to employees manually updating Google Sheets that fed business critical decision making information. The client operated in a projectized environment with one Google Sheet per project. With a portfolio size of 150+ simultaneously executing projects, executive leadership needed a way to aggregate the data without manual copy/paste.
Using Google Workspace, build a tool to dynamically identify key data elements across 150+ Google Sheets. The solution must be robust enough to gather 100+ data elements per sheet despite a variety of different sheet formats. Furthermore, the solution should further enable migration of the same data set into Salesforce.
KilPen built a solution containerized within Google Sheets that crawled previously indexed sheets and extracted key data elements. A data model was designed as well as a method to identify missing information and other data integrity problems. The data model, with strong integrity, enabled custom report creation allowing executive leadership insight despite the distributed nature of their data.
One month
Our client needed to produce 1,000+ images using an image creation tool. The images exported from the tool in PNG format, but a TIFF format was needed. Doing this conversion by hand would take considerable resources.
Develop an automation tool that would integrate into the existing product development workflow. The tool needs to seamlessly convert images into the TIFF format and sort output files into appropriate folders according to an information management scheme.
Powered by Google Workplace and an API-based image converter, KilPen developed a solution that ran hourly checking for newly created images. A call was made to the API with the new image resulting in conversion to the desired format. Google Apps Scripting was used to retrieve the new image and save into Google Drive where a separate sorting algorithm placed files in the appropriate place.
One week
Our client needed to routinely send a survey to key stakeholders on projects to collect performance feedback. With 70+ surveys needing to be sent at one time, an automated solution was ideal.
Develop an automation tool to simplify emailing of surveys to key stakeholders. The survey questions were provided; however, a Google Form needed to be created. A larger challenge to solve was the need for 100% completion of surveys. The tool must track when surveys are returned and allow for easy resending to non-compliant stakeholders. Also, the survey needed to be prepopulated with stakeholder names and several other fields. Lastly, the survey email needed to be branded and professional looking.
Powered by Google Workplace, KilPen built a Google Sheet containerized add-on tool. When launched, the user was presented with a user interface sidebar where surveys could be selected and sent with one click of the mouse. The tool used stakeholder data contained within the Google Sheet to build custom survey forms for each stakeholder and then sent a custom-built HTML/CSS email aligned with the company brand. When stakeholders completed the form, the results were logged into a Google Sheet and the survey was marked as being completed. The tool allowed for resending in bulk all outstanding surveys with little user involvement.
14 Days
help iS in reach!