Problem Statement

I once worked with a small business owner who ran a studio, lets call the company PhotoNow. This company photographed for different sports teams and schools in their area. However, PhotoNow had a problem: they needed a way to keep track of all the orders they received. Their order data was stored in a proprietary format in a text file from the legacy software they used. PhotoNow used a few different computers to manage their orders, and they wanted a way to consolidate all their order data into one place.

Business Need

They had a few requirements:

  1. Parse the proprietary text file format and import relevant fields into a database.
  2. View data and generate reports of key business metrics from the data in the database.
  3. Write to the database from multiple computers at the same time.

After reviewing the requirements, I suggested using Google Sheets as a database. To some this may sound like a non-traditional approach, but it checks all the boxes for their requirements. Their business is cyclical and as a result so is their data. Their biggest need was to be able to view insights for the existing season. Due to the low scale of data, and priority on visualizing and analyzing the data, Google Sheets was a perfect fit.

Solution Architecture

The diagram below shows a high level system architecture for the solution. PhotoNow runs multiple studio computers. In order to watch the filesystem for changes, I wrote a desktop application that runs on each studio computer. I used electron which may have been a bit overkill for this use case, but it worked well out of the box and could be deployed quickly. image

Example Code

The core of the desktop app is appending data to a Google Sheet after parsing new files from the filesystem. The code below shows an example of how that was done using the Google Sheets API. The appendRows method takes an array of entities and appends them to the Google Sheet.

async appendRows(auth, relevantEntities) {
    const sheets = google.sheets({ version: 'v4', auth });

    // Use array.map to transform entities and flatten the result with .flat()
    const values = relevantEntities.flatMap(orderEntity => orderEntity.toSheetRowsArray());

    const response = await sheets.spreadsheets.values.append({
        spreadsheetId: this.sheetId,
        range: 'Sheet1!A1',
        valueInputOption: 'RAW',
        resource: { values },
    });

    // Check if the request was successful before logging and updating syncedFiles
    if (response.status === 200) {
        relevantEntities.forEach(orderEntity => this.syncedFiles.add(orderEntity.fileName));
    }

    console.log(response);
    return response;
}