Import Daily Qualifier Spreadsheet by Strategy Name - P&L spreadsheet

[A Former User]

If you prefer managing your races via spreadsheets or haven't started using automation software, here's a guide that can help streamline your daily race tasks and save you some time.

Step 1: Prepare your Spreadsheet

Download the "Daily Qualifiers List" from the Horse Racing Stats software and import it to your google drive. Open it with google sheets and copy the spreadsheet id, it's the long string in the spreadsheet URL.

Also, ensure you have a target spreadsheet where each tab corresponds to a strategy name. The tabs should have the following columns: selection_id, race_date, race_time, course_name, horse_name, race_type, going and any other column that you might want to add. You can find a template here.

Step 2: Open the Google Apps Script Editor

With your target spreadsheet open (your P&L spreadsheet), go to your Google Sheets, click on "Extensions" in the menu, and then select "Apps Script". This will open the Google Apps Script Editor.

Copy and paste the script provided in this guide into the Apps Script editor. Remember to replace the placeholder 'sourceSpreadsheetId' with the ID of your source spreadsheet (Daily Qualifier List).

Step 4: Save and Run the Script

After pasting the script, click on the disk icon or select "File > Save" to save the script. You can name the project as you wish, for example, "Spreadsheet Automation".

Then, click on the play button or select "Run > Run function > importDataBasedOnRules" to run the script.

If this is your first time running the script:

Click on Review Permissions, then sign in with your Google account. You'll see a warning that "Google hasn't verified this app." Click on Advanced and then Go to YOUR_PROJECT_NAME (unsafe) to proceed. On the next screen, click Allow to give the necessary permissions.

That's it! Your Google Sheets is now set up to automatically transfer and process data from your source sheet to the respective strategy sheets. The "race_date_time" field is split into "race_date" and "race_time" fields in the target spreadsheet, which can make it easier to plan your day.

[A Former User]

Script

SOURCE_SPREADSHEET_ID = spreadsheet id

Example: https://docs.google.com/spreadsheets/d/1fffffkLZZh8FtG6WCIQ--pmul4OWkWlSdevQDoBY1_I/edit#gid=345667

Always save your script before running it after replacing the "Daily Qualifiers List" of the day.

function customTrim(x) {
  return x.replace(/^\s+|\s+$/gm,'');
}

function importDataBasedOnRules() {
  // Get the source and target spreadsheets

  let sourceSpreadsheet = SpreadsheetApp.openById('SOURCE_SPREADSHEET_ID');
  let targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();  //assuming the script is bound to the target spreadsheet

  // Get the source sheet and its data
  let sourceSheet = sourceSpreadsheet.getSheets()[0];  // get the first sheet
  let sourceLastRow = sourceSheet.getLastRow();
  let sourceLastCol = sourceSheet.getLastColumn();

  // Fetch the displayed values from the source sheet
  let sourceRange = sourceSheet.getRange(2, 1, sourceLastRow-1, sourceLastCol);  //get the range
  let rawValues = sourceRange.getDisplayValues();  //get displayed values including the format

  // Process the data: ignore certain columns and split race_date_time into race_date and race_time
  let values = rawValues.map(([strategyName, , , raceDateTime, courseName, raceType, going, horseName, selectionId]) => {
    let [raceDate, raceTime] = raceDateTime.split(' ');
    return [selectionId, raceDate, raceTime, courseName, horseName, raceType, going];
});

  // Write the processed values into the target sheet
  for(let i = 0; i < values.length; i++) {
    let strategyName = customTrim(rawValues[i][0]);  //fetch strategy name
    let targetSheet = targetSpreadsheet.getSheetByName(strategyName);  //find the sheet with the same name

    if (targetSheet) {  //if the sheet exists
      let targetLastRow = targetSheet.getLastRow();
      let targetRange = targetSheet.getRange(targetLastRow+1, 1, 1, values[i].length);  //set the range in the target sheet
      targetRange.setValues([values[i]]);  //write the values
    } else {
      //if the sheet doesn't exist, handle the error (you can modify this part to fit your needs)
      Logger.log("The sheet for strategy " + strategyName + " does not exist in the target spreadsheet.");
    }
  }
}

[A Former User]

[A Former User]

This post is deleted!