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."); } } }