Google AppScript to parse sheet of walk up songs for spotify soundboard

code.gs

/**
 * The event handler triggered when editing the spreadsheet.
 * @param {Event} e The onEdit event.
 * @see https://developers.google.com/apps-script/guides/triggers#onedite
 */
function onEdit(e) {
  // columns
  // PlayerName	Number	SpotifyURL	StartTime	EndTime

  // get the sheet and the range of data
  var sheet = e.source.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  // array to store the processed data
  var dataArray = [];

  // iterate through each row, starting from the second row to skip headers
  for (var i = 1; i < values.length; i++) {
    var row = values[i];
    var playerName = row[0];
    var number = row[1];
    var spotifyURL = row[2];
    var startTime = row[3];
    var endTime = row[4];

    if (!playerName || !number || !spotifyURL || !startTime || !endTime) {
      continue;
    }

    // combine player name and number
    var name = playerName + " (" + number + ")";

    // extract spotify song ID from URL (the part between /track/ and the ?)
    var track = spotifyURL.split("/track/")[1].split("?")[0];

    // convert StartTime and EndTime notation from mm:ss to seconds
    var startParts = startTime.split(":");
    var endParts = endTime.split(":");
    var start = parseInt(startParts[0]) * 60 + parseInt(startParts[1]);
    var end = parseInt(endParts[0]) * 60 + parseInt(endParts[1]);

    // create an object for the row
    var dataObject = {
      name: name,
      track: track,
      start: start,
      end: end,
    };

    // add the object to the array
    dataArray.push(dataObject);
  }

  // convert the array of objects to compact JSON notation
  var jsonString = JSON.stringify(dataArray);

  // encode the JSON string with base64
  var base64Encoded = Utilities.base64Encode(jsonString);

  // Log or save the encoded string as needed
  Logger.log(base64Encoded);

  // Construct the URL
  var url = "https://soundboard.simeon.dev/#data=" + base64Encoded;

  // Write the URL into cell J2
  sheet.getRange("J2").setFormula('=HYPERLINK("' + url + '", "Soundboard Link")');
}