Vsk Revolution Points Tracker Google Script (Time Differential Points System)

Requires a Google Apps Scripts Account

Overview

This Google Apps Script is designed to automate the processing of CSV files that are placed in a specific Google Drive folder. The script reads the race results from these CSV files, calculates points for each player based on their performance, and updates a Google Sheet with the accumulated points. The script is triggered to run every minute, ensuring that new files are processed promptly, even if the browser is closed.

Key Features

1. File Processing:

• The script monitors a specified Google Drive folder for new CSV files.
• It processes each file, calculates points, and then renames the file to indicate it has been processed (_processed.csv).

1. Points Calculation:

• Points are calculated based on a race time comparison against a base time (26:30 minutes, equivalent to 1590 seconds).
• Players earn or lose points depending on how their race time compares to this base time.
• Invalid scores (e.g., -1, DNS, DNC, DSQ, DNF) are ignored.

1. Score Accumulation:

• The script keeps a running total of points for each player.
• Points from newly processed files are added to the accumulated total from previous files.

1. Sorting and Display:

• After processing, the players are sorted by their total points in descending order.
• The sorted data is then written to the Google Sheet, ensuring that the top-performing players are listed first.

1. Automatic Renaming:

• Processed files are renamed to avoid reprocessing, ensuring that only new files are considered in subsequent runs.

Script Functions

1. processCSVFiles():

• Purpose: The main function that triggers the file processing.
• Steps:
• Accesses the specified Google Drive folder.
• Iterates through each file, checking if it’s a CSV and hasn’t been processed.
• Calls the processData function to handle the CSV data.

1. processData(csvData):

• Purpose: Processes the data from each CSV file.
• Steps:
• Parses the race time and player information.
• Uses the calculateScore function to determine the points for each player.
• Accumulates the points in a map (pointsMap).

1. calculateScore(raceTime, baseTimeInSeconds, basePoints):

• Purpose: Calculates the score for a player based on their race time.
• Steps:
• Converts race time to seconds and compares it to the base time.
• Adjusts points based on whether the race time is faster or slower than the base time.
• Ensures that invalid scores are discarded.

1. updateSheet(sheet, pointsMap):

• Purpose: Updates the Google Sheet with the accumulated and sorted points.
• Steps:
• Merges the new points with any existing points in the sheet.
• Sorts the players by their total points.
• Clears the sheet and writes the updated, sorted data.

Trigger Setup

• Time-Driven Trigger: The script is set to run every minute using a time-driven trigger. This ensures continuous monitoring and processing of new files without requiring manual intervention.

Usage

• File Management: Place CSV files into the monitored Google Drive folder. The script will automatically detect, process, and update the Google Sheet with the results.
• Automatic Operation: The script runs in the background and requires no manual input once set up, making it suitable for continuous and unattended operation.

Considerations

• Quota Limits: Running the script frequently might approach Google Apps Script quota limits. Monitor usage, especially if processing a large number of files.
• Error Handling: The script includes basic logging for troubleshooting, which can help diagnose issues if the script fails to run as expected.

Vsk Revolution 2024