validationgoogle-apps-scriptgoogle-sheetsauto-populate

How can I auto-populate a range from another tab based on a Data Validation selection in Google Sheets?

I have been looking for info about this for sometime, but nothing seems to work. I hope I'm going to explain this right, but here goes... I have a data validation setup (cell G31 on 'Fellowship Tracking' tab.) Based on whatever option is selected, I want it to pull data from a certain range on another tab in the same sheet ('ALL Stages+Pit'.) I put a link to the sheet at the bottom.

For an example, on the 'Fellowship Tracking' tab, cell G31, let's say I select the option Stage 1. Then starting in cell C33, I would like it to auto-populate the data from cells B3:G21 from the 'ALL Stages+Pit' tab. In addition, repeat the same for Stage 2 option pull from 'Fellowship Tracking', cells H3:M21 and Stage 3 option pull from 'Fellowship Tracking', cells N3-S21 and auto-populate starting in cell C33 like Stage 1 data. The 'Pit' option, I would like to pull data from the 'PIT Sheet' tab from cells A2-F17 and auto-populate starting in cell C33. So basically whatever option is chosen, it pulls the data from the ranges mentioned above and auto-populates the respective data starting in cell C33.

I hope I explained all that enough. I would to get info on how to do all that or is this not possible because of the way the data is setup?

Sheet Link: https://docs.google.com/spreadsheets/d/1KYSiVggIm7KIKxpJMnUhldrzsIaETCqyjOWJ1a9k1cI/edit?usp=sharing

Images:

'Fellowship Tracking' tab

enter image description here

'ALL Stages+Pit' tab: Stages data

enter image description here

'ALL Stages+Pit' tab: Pit data

enter image description here

Solution

  • Solution

    Here is the full clarified code for solving this issue. This solution is brought to you by @Cooper who set up the path to follow for completing the rest of the required features. 

    function onMyEdit(e) {
  // gets the sheet changes have been made
  var sh=e.range.getSheet();
  // only look for changes on Fellowship Tracking as there is where we will be changing our "stages" cell
  if(sh.getName()!='Fellowship Tracking')return;
  
  // If Stage 1 is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 1') {
  // get the right range from the right sheet for stage1 
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('B3:G21');
  // get range where we want to populate the information
    var desrg=sh.getRange('C33');
 // populate the information
    srcrg.copyTo(desrg);
  }
  
    // If Stage 2 is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 2') {
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('H3:M21');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }  
  
   // If Stage 3 is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='Stage 3') {
    var srcrg=e.source.getSheetByName('ALL Stages+Pit').getRange('N3:S21');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }  
  
   // If PIT is selected
  if(e.range.columnStart==7 && e.range.rowStart==31 && e.value=='PIT') {
    var srcrg=e.source.getSheetByName('PIT Sheet').getRange('A2:F17');
    var desrg=sh.getRange('C33');
    srcrg.copyTo(desrg);
  }    
  
}

// run this function to start the auto-complete programme
function createOnMyEditTrigger() {
  var ss=SpreadsheetApp.getActive();
// runs the function above as an onEdit function on the specfic spreadhseet
  ScriptApp.newTrigger('onMyEdit').forSpreadsheet(ss.getId()).onEdit().create();
}

    You can check the reference for further information in how to catch changes in sheets with onEdit() here