Copy specific data to other sheet (with script)

Question

https://docs.google.com/spreadsheets/d/1mpALs4rdNj-TFFgCQ0CEBP453v-FfQJR_bBxOzQakWU/edit#gid=1256640730 (Anyone with the link can view this spreadsheet)

I have a "masterlist" sheet collect data from Google Forms and I need the script to copy the data to the related sheet according to their "Location"...The name of other sheet is named from "Location"

Example 1  

Sheet 1: Contain all the data (Master List)
Name......Address...............................Location
Rose.......21,Radje Road,88888.........Kedah
Rose.......21,Radje Road,88888.........Kedah
Rose.......21,Radje Road,88888.........Penang
Stone......5,Jae Road,22222...............Penang  

Sheet 2: Copy data from Sheet 1 (Location-Kedah Only)
Name......Address...............................Location
Rose.......21,Radje Road,88888.........Kedah
Rose.......21,Radje Road,88888.........Kedah

Sheet 3: Copy data from Sheet 1 (Location-Penang Only)
Name......Address...............................Location
Rose.......21,Radje Road,88888.........Penang
Stone......5,Jae Road,22222.........Penang```

Example 2

Sheet 1: Contain all the data (Master List)
Name.....Gender
Bryan.....Male
Mei.....Female
Lily.....Female
xx.....Female
xx.....Male  

Sheet 2: Copy data from Sheet 1 (Gender-Female Only)
Name.....Gender
xx.....Female
Lily.....Female
Mei.....Female

Sheet 3: Copy data from Sheet 1 (Gender-Male Only)
Name.....Gender
Bryan.....Male

function copyDataUniqueToColG() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Master List');
  const shsr=2;//data start row on master list
  const shhr=1;//master list header row
  const hA=sh.getRange(shhr,1,1,sh.getLastColumn()).getValues().map(function(r){return[r[1],r[2],r[3],r[4],r[5],r[6]]})[0];
  const vs=sh.getRange(shsr,1,sh.getLastRow()-shsr+1,sh.getLastColumn()).getValues();
  const base='Sheet';
  let shts=ss.getSheets();
  //shts.forEach(function(s,i){if(s.getName()!='Master List'){ss.deleteSheet(s);}});//delete all other sheets
  const gvs=sh.getRange(shsr,7,sh.getLastRow()-shsr+1,1).getValues().map(function(r){return r[0]});
  const s=new Set(gvs);
  const g=[...s];//g has unique values now
  g.forEach(function(v,i){
    let sA=[];
    ss.getSheets().forEach(function(obj){sA.push(obj.getName())});
    if(sA.indexOf(v)==-1){ss.insertSheet(v);}
    let aA=[]
    aA.push(hA);//start with header row
    vs.forEach(function(r,j){
      //compare to column G value
      if(r[6]==v) {
        aA.push([r[1],r[2],r[3],r[4],r[5],r[6]]);//add rows that match
      }
    });
    let dsh=ss.getSheetByName(v);
    dsh.getRange(1,1,aA.length,aA[0].length).setValues(aA);
    SpreadsheetApp.flush();//not really necessary but fun to watch the progress
  });
}

done sharing the code to the question.. but the code is not working for me.. — Karyee
Provide minimal reproducible example. Describe the problem. "It doesn't work" isn't descriptive enough to help people understand your problem. Instead, tell other readers what the expected behavior should be. Tell other readers what the exact wording of the error message is, and which line of code is producing it. Use a brief but descriptive summary of your problem as the title of your question. — TheMaster
=QUERY('Master List'!A:M, "where G = 'Melaka'", 1) the result from this formula is exactly what I need, but I need it in "script editor" instead of formula — Karyee
You could use the method Range.setFormula() example: Range.setFormula("=QUERY('Master List'!A:M, 'where G = "Melaka"', 1)") — Aerials

Aerials Aerials · Accepted Answer · 2020-08-06T09:19:01

Please keep in mind Stack Overflow is not a code writing service. People in the community answer programming and code related questions, but are not there to write the code for others.

With the information from the answers and comments you should try and try, and continue to ask questions if you do not understand. If the code is written for you, it is unlikely that you will learn. That being said, since you are a new user, here is a sample code to solve your problem:

function distributeMasterToSheets(){
  // Get all sheets except master list
  var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var destinationSheets = allSheets.slice(1);
  // And set the sheet name dependent formula in range A1 
  destinationSheets.forEach(
    function(sheet){
      sheet.getRange("A1").setFormula("=QUERY('Master List'!A:M,\"select * where G = '"+sheet.getName()+"'\",1)")
    }
  )  
}

The following documentation is where the methods used are explained, I suggest you go through some quickstarts to get the hang of it.

Copy specific data to other sheet (with script)

2 Answers

References: