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