Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (Yeti)
  • No Skin
Collapse

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

Scheduled Pinned Locked Moved Member Filters & Projects
4 Posts 1 Posters 108 Views
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • ? Offline
    ? Offline
    A Former User
    wrote on last edited by A Former User
    #1

    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.

    ? 1 Reply Last reply
    0
  • ? Offline
    ? Offline
    A Former User
    wrote on last edited by
    #2

    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.");
        }
      }
    }
    
    
    
    1 Reply Last reply
    0
  • ? Offline
    ? Offline
    A Former User
    wrote on last edited by
    #3

    Screenshot 2023-07-20 at 07.37.29.png

    Screenshot 2023-07-20 at 07.36.48.png

    Screenshot 2023-07-20 at 07.37.16.png

    1 Reply Last reply
    0
  • ? Offline
    ? Offline
    A Former User
    replied to A Former User on last edited by
    #4
    This post is deleted!
    1 Reply Last reply
    0

  • Login

  • Login or register to search.
  • First post
    Last post
0
  • Login

  • Login or register to search.