For loops inside getRange call-CodePudding

I am sorting through data collected on a master sheet, and then placing it in separate sheets based on those values. The sorting and placing the data into lists isn't the issue.

I'm having trouble figuring out how to loop the placement of the data into the separate sheets. The data is already separated into lists in 2D arrays. Below is a shortened version of myt code, and at the end is the hard coded solution, but I should be able to accomplish this in a loop, right?

Ideally I should be able to run a loop through the lists and place the sorted data into the separate sheets.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ml = ss.getSheetByName('Master List');
  var lists = ["list1","list2"];
  var masterlist = ml.getRange(2,2,100,100).getValues();
  var list1 = [];
  var list2 = [];
    for(i=0;i<masterlist.length;i  ) {
      if(masterlist[i][2]==lists[0]){
        list1.push(masterlist[i])
      }
      if(masterlist[i][2] == lists[1]){
        list2.push(masterlist[i])
      }
    }
  ss.getSheetByName(lists[0]).getRange(2,2,list1.length,list1[0].length).clearContent();
  ss.getSheetByName(lists[0]).getRange(2,2,list1.length,list1[0].length).setValues(list1);
  ss.getSheetByName(lists[1]).getRange(2,2,list2.length,list2[0].length).clearContent();
  ss.getSheetByName(lists[1]).getRange(2,2,list2.length,list2[0].length).setValues(list2);

Below is a copy of what I think should work with a loop, rather than hard coding the clearContent and setValues commands, but I keep getting different errors as I fiddle with it.

function Problem(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ml = ss.getSheetByName('Master List');
  var lists = ["list1","list2"];
  var masterlist = ml.getRange(2,2,100,100).getValues();
  var list1 = [];
  var list2 = [];
    for(i=0;i<masterlist.length;i  ) {
      if(masterlist[i][2]==lists[0]){
        list1.push(masterlist[i])
      }
      if(masterlist[i][2] == lists[1]){
        list2.push(masterlist[i])
      }
    }
  for(j=0; j<lists.length;j  ){
    ss.getSheetByName(lists[j]).getRange(2,2,lists[j].length,lists[j][0].length).clearContent();
    ss.getSheetByName(lists[j]).getRange(2,2,lists[j].length,lists[j][0].length).setValues(lists[j]);
  }
}

Lastly, I would love some help on how I would splice the sorting column out of my list1 and list 2 before placing them in their individual sheets.

Below is a table example of the master list. |Col 0|Col 1|Sorting |Col 2|Col 3| |-----|-----|--------|-----|-----| |Data 10|Data 11|list1|Data 12|Data 13| |Data 20|Data 21|list2|Data 22|Data 23|

I would like the data to end on each sheet as such: List 1 Sheet. |Col 0|Col 1|Col 2|Col 3| |-----|-----|-----|-----| |Data 10|Data 11|Data 12|Data 13|

List 2 Sheet. |Col 0|Col 1|Col 2|Col 3| |-----|-----|-----|-----| |Data 20|Data 21|Data 22|Data 23|

CodePudding user response：

This works with a few minor changes

function lfunko() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet0');
  var lists = ["Sheet1","Sheet2"];
  var vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();
  var list1 = [];
  var list2 = [];
    for(i=0;i<vs.length;i  ) {
      if(vs[i][2]==lists[0]){
        list1.push(vs[i])
      }
      if(vs[i][2] == lists[1]){
        list2.push(vs[i])
      }
    }
  ss.getSheetByName(lists[0]).getRange(2,2,list1.length,list1[0].length).clearContent();
  ss.getSheetByName(lists[0]).getRange(2,2,list1.length,list1[0].length).setValues(list1);
  ss.getSheetByName(lists[1]).getRange(2,2,list2.length,list2[0].length).clearContent();
  ss.getSheetByName(lists[1]).getRange(2,2,list2.length,list2[0].length).setValues(list2);
}

CodePudding user response：

Modification points:

From var masterlist = ml.getRange(2,2,100,100).getValues(); and your showing following data,
```
  |Col 0  |Col 1  |Sorting|Col 2  |Col 3  |
  |-------|-------|-------|-------|-------|
  |Data 10|Data 11|list1  |Data 12|Data 13|
  |Data 20|Data 21|list2  |Data 22|Data 23|
```
- I thought that you might misunderstand the column number. If you want to check the column "C", please modify the 2nd argument from 2 to 1 like getRange(2,1,100,100). I thought that this might be the reason for your issue.
- But in your situation, I thought that when the values are retrieved from the data range, the script might be simple.
For example, when your script is modified from var masterlist = ml.getRange(2,2,100,100).getValues(); to var masterlist = ml.getRange(2,1,100,100).getValues();, the error can be removed. But in this case, when your sample input value is used, list1 and list2 are [ [ 'Data 10', 'Data 11', 'list1', 'Data 12', 'Data 13', '',,, ] ] and [ [ 'Data 20', 'Data 21', 'list2', 'Data 22', 'Data 23', '',,, ] ], respectively. I thought that this might be your issue.

In order to achieve your following goal, I thought that 'list' might be required to be removed.

list1

  |Col 0  |Col 1  |Col 2  |Col 3  |
  |-------|-------|-------|-------|
  |Data 10|Data 11|Data 12|Data 13|

list2

  |Col 0  |Col 1  |Col 2  |Col 3  |
  |-------|-------|-------|-------|
  |Data 20|Data 21|Data 22|Data 23|

If my understanding of your goal is correct, how about the following modification?

Modified script:

function myFunction() {
  var sortingColumn = 3; // From your showing sample value, I guessed that "Sorting" column is the column "C".

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ml = ss.getSheetByName('Master List');
  
  // I prepared an object for searching 2 values.
  var lists = [
    { name: "list1", values: [] },
    { name: "list2", values: [] }
  ];

  var [, ...masterlist] = ml.getDataRange().getValues();
  for (i = 0; i < masterlist.length; i  ) {
    var sorting = masterlist[i].splice(sortingColumn - 1, 1);
    lists.forEach(({ name, values }) => {
      if (sorting == name) values.push(masterlist[i]);
    });
  }
  for (j = 0; j < lists.length; j  ) {
    var sheet = ss.getSheetByName(lists[j].name);
    var rows = lists[j].values.length;
    sheet.getRange(2, 2, rows < 100 ? 100 : rows, 100).clearContent();
    sheet.getRange(2, 2, rows, lists[j].values[0].length).setValues(lists[j].values);
  }
}

From your var masterlist = ml.getRange(2,2,100,100).getValues(), I used sheet.getRange(2, 2, rows < 100 ? 100 : rows, 100).clearContent();. If this was not your expected situation, please modify it.
In this modification, in order to use 2 search values, I prepared an object. When such an object is used, I thought that your script might be simple.
When this script is run, the retrieved values from "Master List" sheet are searched using the object. And, each value is put into each array. And then, those values are put on those sheets.

Note:

In this script, when the sheet names of list1 and list2 are not existing, an error occurs. Please be careful about this.

This works with a few minor changes

Modification points:

Modified script:

Note:

References: