Google Sheets Expert

Customized Solutions

CASE STUDY 1

PROBLEM

Client needs to automate time and attendance tracking of employees with the following requirements:

• Determine early in and late in

• Employee schedules vary daily and weekly

• Total worked hours are used to calculate salary

• Employees logging in even if not yet in the store location

SOLUTION

I built a timer and attendance web app using Google Sheets as the database. This app includes a signup feature to capture information of new employees, with the app assigning a unique User ID and automatically creating folders in the client’s Google Drive at user signup. A separate Manager Portal allows admin to pre-log employee schedules.

Signup web app that captures information of new employees and ensures consistent data across systems

Timer and attendance web app with geolocation lock

Timer and attendance web app with the ability to capture user clock in and clock out

With this app, the client achieved the following results:

• Only employees with schedules logged in the Manager Portal can log in to the attendance app, ensuring data integrity and accuracy

• Accurately records worked hours

• Automates capturing of new employee information

• Eliminates manual double-checking of clock-in and
  clock-out logs

• Solves the problem of late employees changing their clock-in log as on time. With the geolocation API locking the app to a specific location, this ensures that users can only log in to the attendance app when they are within the vicinity of the store

• Eliminates the hassle of employees needing to interface with a spreadsheet. Now they only need to open the web app on a browser, enter their User ID and password, and click a button to clock in or clock out for the day when they have a schedule

CASE STUDY 2

PROBLEM

Client selling motorcycle parts needs an application that will allow an employee to enter sales into their records. They also need the ability to record purchases and monitor inventory of items.

SOLUTION

I built two separate web apps, one for entering sales and another for entering purchases. The sales web app only allows adding and deleting entries, while the purchases web app allows adding, editing, and deleting entries. The client specifically uses a tablet device, so the web app is designed for this requirement.

With this custom-built web app, the client achieved the following results:

• Record sales and purchases into Google Sheets databases. The app comes with multiple pages and contextual menus that provide the necessary functionalities to add, view, search, filter, and delete items from the databases

• Automatically track inventory (batches and individual items)

• Enable recording of batch purchases

• Ensure data integrity and accuracy by creating unique IDs for entries, which is an essential feature for updating details and deleting entries

• Automate generation of a unique code used to internally determine actual price of items

Custom-built web app for inventory and purchases using Google Sheets as databases

Project Builds

TEMPLATE REDESIGN

This payslip template I reimagined for a client uses Google Apps Script to fetch data from several databases to populate the ranges. It also uses array formulas in the Cutoff_Sched sheet (cells G10 to J10) and Particulars sheet (cells P10 to Q10).

BEFORE

AFTER

DATA CLEANUP & DASHBOARD CREATION

This sample showcases a dataset that has been cleaned up, then transformed into pivot tables placed in a dashboard sheet. Check out the file and see how the data changes when you change the dropdown values in the Pivots_Queries sheet, cells C34, F4 and N4.

ARRAY FORMULAS

This sample showcases monthly marketing data from several platforms, with everything consolidated in a Summary sheet.

Go to each month sheet and check out the array formulas in column F, rows 10 to 12.

Then in the Summary sheet, check out the array formulas in the Target column.

DYNAMIC FORMULAS

Check out cell O11 in the Wealth Waterfall sheet to see the dynamic formula that references the header row (9) of the P&L Review sheet.

In the formula bar, change the year to 2024 or 2025 and see the formula dynamically retrieve the last amount from the P&L Review sheet’s W-X-Y columns.

WEB APPS

I build custom web apps for clients from scratch. This sample is a signup feature for a clock-in/clock-out app.

Like what you’ve seen?

Let’s start a project or hire me for freelance gigs. I’m open to a long-term partnership.

ninja@speedstersolutions.com