Google Sheet "master" to populate "child" on different tabs

Question

I'd like a main "master" Google Sheet to write to separate sheets of another "child" Google Sheet, based on the a Status condition. If a project is "Finished", "Approved", or "Ready", these would show on their own tab. I figured it out with Functions, but it's clunky, and there's a Fetch limit within a single sheet. I've researched JavaScript (which I believe to be the most simple answer), but I'm doing it wrong, and can't find a sample that closely defines what I'm trying to accomplish.

This is what I'm trying to do:

IF "Management" sheet & column "Status" = "Finished"  
    THEN write to "Production" sheet on the "Finished" tab  
ELSE IF "Management" sheet & column "Status" = "Approved"  
    THEN write to "Production" sheet on the "Approved" tab  
ELSE IF "Management" sheet & column "Status" = "Ready"  
    THEN write to "Production" sheet on the "Ready" tab  

This needs to update in real time.

The "Management" sheet would drive (populate) the "Production" sheet: https://docs.google.com/spreadsheets/d/19NIn4TukYnKkkpOg8E8s-6loS8rCplf-tGpY2cENIwQ/edit?usp=sharing

The "Production" sheet is a dummy, showing only what I would intend the script to do: https://docs.google.com/spreadsheets/d/1aCh0_GzQKzbUxWAn8e27q0-z48tmxK0mK8wGMm2YDuk/edit?usp=sharing

I know that this is better done using a database, but my management has, in it's infinite wisdom, abandoned a perfectly good Access based MIS ($) for a free spreadsheet system. They have left it up to us middle-managers, with little to no programming knowledge, to make it work. I have some programming knowledge, if you count COBAL and 1978 AppleSoft!

Answer 1

In the source spreadsheet, set up the simple trigger that runs on edit. When you edit a cell in your source spreadsheet, an event object is raised and passed to the onEdit() function as an argument.

The function examines the context of the event and attempts to write to the target sheet if all conditions are met. The function uses the entered value to find the relevant sheet in the target file. If the sheet doesn't exist, it displays error message in the source file.

function onEdit(event){

       var sourceSpreadsheet = event.source;
       var targetSpreadsheet = SpreadsheetApp.openById(yourId); //spreadsheet to write to
       var sheet = sourceSpreadsheet.getActiveSheet(); //sheet that was edited
       var row = event.range.getRow();
       var col = event.range.getColumn();
       var statusColNumber = 2; //column number of the 'Status' column


      if(sheet.getName() == "Management" && col == statusColNumber){

          var value = event.value;

         if(!value) { //checking if the value is an empty string or undefined

              return;
          }

          try {

         var targetSheet = targetSpreadsheet.getSheetByName(value);

         targetSheet.getRange("A1").setValue("Hello from row number " + row);


         } catch(error) {

             sourceSpreadsheet.toast("Failed. Sheet " + value + " not found in target spreadsheet");



           }



      }