Updated Activity Tracker

Eclipse · January 17, 2020

Because of the new military stuff, I was called in to help with some spreadsheet stuff. Because of that I decided to finally update the spreadsheet activity monitor, I made based off of @Pablo's code.

The spoiler shows heavily commented code, I suck at explaining code, but I hope those who don't know a lot of code will still understand, even if it's just a little bit. (It's javascript for people who don't know)

Spoiler


var ss = SpreadsheetApp.getActiveSpreadsheet();
var serverlist_sheet = ss.getSheetByName('Player List')


// Using a 2D array, allows for quicker and less useless tests
var real_array = [
    [7, 8, 9],
    [13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42],
    [46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75],
    [79]
]

// Example of old array
//var player_name_roster = [7, 8, 9, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 79, 80, 81, 82, 83]

// Used internally for organising columns into days.
var days_array = [12, 6, 7, 8, 9, 10, 11];

// A function to add minutes to a certain Date Object. https://www.w3schools.com/jsref/jsref_obj_date.asp
var add_minutes = function(dt, minutes) {
    return new Date(dt.getTime() + minutes * 60000);
}

// A function to gather a list of user data, has been in every version. For this version I used https://imperialgaming.net/moose2/write2.php because it's more accurate
function getData() {
    var queryString = Math.random();
    var sheet = ss.getSheetByName("Player List")

    var cellFunction1 = '=CLEAN(IMPORTXML("' + serverlist_sheet.getRange('B1').getValue() + '?' + queryString + '","' + serverlist_sheet.getRange('B2').getValue() + '"))';
    serverlist_sheet.getRange('C1').setValue(cellFunction1);
}



function checkplayer() {

    var rank_sheet = ss.getSheetByName("Activity Manager");
    var playersheet = ss.getSheetByName("Player List");

    // Gets day of week in a number, Sunday starts as 0. So it gets the days_array and gets the first object. Javascript starts indexing at 0. So when getting an object from two square brackets like [7,8,9] it will go 0 = 7, 1 = 8, 2 = 9.
    var day = days_array[new Date().getDay()]
    // List of players
    var onlinerange = playersheet.getRange(2, 3, 128)
    var onlineValues = onlinerange.getValues()
    // For each [] in real_array. EG [7,8,9] do
    for (var l = 0; l <= real_array.length - 1; l++) {

        // This just gets a range of cells in a specific area. We had to - 1 because the real_array uses data from the personnel roster, which is 1 row ahead of the activity monitor
        var range = rank_sheet.getRange(real_array[l][0] - 1, 5, real_array[l][real_array[l].length - 1] - real_array[l][0] + 1)
        var values = range.getValues()
        //Logger.log(values)

        // For each person online get their steamID, if the steamID is empty, select the next one.
        for (var i = 0; i <= onlineValues.length - 1; i++) {
            var steamIDToTest = onlineValues[i][0]
            if (steamIDToTest == "") {
                continue
            }

            // for each line in the selected array, eg [7,8,9] test if the steamID is empty, if so, test the next one.
            for (var j = 0; j <= real_array[l].length - 1; j++) {


                var steamID = values[j][0]
                if (steamID == "") {
                    continue
                } else if (steamIDToTest == steamID) {

                    //Logger.log(real_array[l][j] - 1)

                    // This section runs if the STEAMIDs match. If they match we get the current hours on the server, and add 5 minutes.
                    var timeToEdit = rank_sheet.getRange(real_array[l][j] - 1, day)

                    //  Logger.log(steamIDToTest)
                    //  Logger.log(real_array[l][j])
                    var time = timeToEdit.getValue()
                    timeToEdit.setValue(add_minutes(time, 5))

                }
            }
        }
    }

}

// Manually did this like an idiot because I was tired. Just resets all the data to 00:00 to set the cell as a duration type
function resetTime() {
    var sheet = ss.getSheetByName("Activity Manager")
    sheet.getRange(6, 6, 3, 7).setValue("00:00")
    sheet.getRange(12, 6, 30, 7).setValue("00:00")
    sheet.getRange(45, 6, 30, 7).setValue("00:00")
    sheet.getRange(78, 6, 1, 7).setValue("00:00")


}

There is some things that could be improved, like me not being a brainlet and using a dumb loop order while comparing SteamIDs. I should've reversed the order I got the online SteamIDs and the player's SteamID. Less checks, more efficient.

This runs in about 1 - 2 seconds for a much bigger sample as apposed to the 5 - 15, maybe even 20 of the old script. It does this by using far less SHEET.getRange() functions.

Many people won't have a use for this, just putting this out in the open in the slight chance people will need it in the future, or I lose my code.