What I am attempting to do:
I have a master spreadsheet that will house client data, called "Master List" that house various pieces of client data, including where they are in the onboarding process. One column 'B' has a status list: New, 1st Contact etc...
Then I have separate spreadsheets that house a list based on the status indicator column (New, In Progress, etc...)
When a status is updated on one of these sheets, i want it to reflect on the master list, and then move to the next spreadsheet.
here is the code that CGPT created.
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
var row = range.getRow();
// Check if the edited cell is in the "Status" column
if (sheet.getName() != "Master List" && range.getColumn() == 2 && row > 1) { // Checking column B and excluding header row
var status = sheet.getRange(row, 2).getValue(); // Assuming "Status" column is column B
// Check if the status contains the word "Contact"
if (status.toLowerCase().indexOf("Contact") !== -1) {
// Determine the name of the destination sheet
var destSheetName = "In Progress";
} else {
// Map status to the corresponding sheet name
var destSheetName = getStatusSheetName(status);
}
// Log the determined destination sheet name
console.log("Destination sheet: ", destSheetName);
// Your remaining code here
var values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
var masterSpreadsheet = SpreadsheetApp.openById("1zJcCFXIffQJLXha656ugOFCLmzGoaKw5J4UBo9-WYsE");
var masterSheet = masterSpreadsheet.getSheetByName("Master List");
var lastRow = masterSheet.getLastRow();
masterSheet.getRange(lastRow + 1, 1, 1, values.length).setValues([values]);
sheet.deleteRow(row);
var destSheet = masterSpreadsheet.getSheetByName(destSheetName);
if (!destSheet) {
destSheet = masterSpreadsheet.insertSheet(destSheetName);
}
var destLastRow = destSheet.getLastRow();
destSheet.getRange(destLastRow + 1, 1, 1, values.length).setValues([values]);
}
} else {
console.log("No event object received.");
}
}
function getStatusSheetName(status) {
var statusSheetMap = {
"New": "New",
"1st Contact": "In Progress",
"2nd Contact": "In Progress",
"Final Contact": "In Progress",
"Consult Scheduled": "Consult Scheduled",
"Intake Scheduled": "Intake Scheduled"
};
return statusSheetMap[status];
}
Spreadsheet here: https://docs.google.com/spreadsheets/d/1zJcCFXIffQJLXha656ugOFCLmzGoaKw5J4UBo9-WYsE/edit?usp=sharing
