google apps script - Automatically write data from one sheet to other with the help of dropdown box

Question

I have a drop down box with 3 options in my sheet 1 and I want to copy data from sheet 1 to the dedicated sheets in the same spreadsheet according to the option selected, how do I do that?

question from:https://stackoverflow.com/questions/65915204/automatically-write-data-from-one-sheet-to-other-with-the-help-of-dropdown-box

Answer 1

Here is my solution using Apps Script. If you want to detect any changes in the dropdown and copy paste your original range to other sheet depening on the values of this dropdown, you want to use an onEdit() simple trigger that will run your function everytime the value of a cell is changed (in your case the dropdown).

Then by just simply using the methods getValue() and setValues() along with getRange() you can move your range from one Sheet to another as you pleased. The following piece of code includes self explanatory comments on the example range and dropdown shown below:

function onEdit() {

  // Get sheet 1
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  // Get sheet 2 
  var sheet2 = SpreadsheetApp.getActive().getSheetByName('Sheet2');
  // Get sheet 3 
  var sheet3 = SpreadsheetApp.getActive().getSheetByName('Sheet3');

  // Get value of dropdown
  var value = sheet.getRange('A1').getValue();

  // Get values of the range in Sheet 1 that we want to copy in other sheets
  var range = sheet.getRange('B1:C2').getValues();

// If dropdown value is A
  if(value=='A'){
    // Set the values of the range you want in sheet 2
    // Bare in mind that for this to work the range in the new sheet must
    // be equal size in rows and columns as from the range we are getting in
    // the original sheet
    sheet2.getRange('B1:C2').setValues(range);
  // If it is B (and you could go on with as many as you want)
  }else if(value=='B'){
    sheet3.getRange('B1:C2').setValues(range);
  }else{

  }
}