CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Patrick
Patrick

Reputation: 85

Split sheet based on column

I have a sheet that I have scripted and I am stuck on one spot. The report I get has all of the warehouses together and it's time consuming to manually split them. This is the last part of the script and I am stumped.

Column B is where all of the warehouses are listed. I want all of warehouse 1 to be moved to the page for warehouse 1, warehouse 2 to warehouse 2... etc. This is my code so far and it is not acting as I expect. Calling for orders in warehouse 1 gets me an order from warehouses 2 and 5. Not all of them, just a handful. Any help would be appreciated.

 function splitYards(sheet){
  //getting the proper range from All Warehouses This works don't change it
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All Warehouses");
  var awLastRow = sheet.getLastRow();
  var awRange = sheet.getRange(2,2,awLastRow-1,1);
  var awValues = awRange.getValues();
  //end of this working
  //console.log(awValues);
  //begin For statement
    for (var i=1; i<awValues.length; i++) {
      if (awValues[i] == 1){
        var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Warehouse 1");
        var drange = sheet.getRange(1,i,1,1);
        var dValue = drange.getValues();
      //console.log(dValue);
//does not work yet...
     // dValue.copyTo(destination);
      }
    }
}

I have added a simple example to try to explain. Anything that is coming from warehouse 1 gets copied to another sheet that is called Warehouse 1. All items coming from Warehouse 2 gets moved to a sheet called Warehouse 2. I want to move the entire row to the new sheets.

enter image description here

Upvotes: 0

Views: 146

Answers (2)

Tanaike
Tanaike

Reputation: 201513

I believe your goal is as follows.

  • You have a sheet in Google Spreadsheet as shown in your question.
  • You want to check the column "B" and want to move the rows to the destination sheet.
  • For example, when the value of column "B" is 1, you want to move the row to the sheet of Warehouse 1 as the appending value.

Modification points:

  • getValues() returns 2 dimensional array. In this case, awValues[i] == 1 is awValues[i][0] == 1.

  • In your script, only one sheet is used.

  • In order to retrieve the values from a row using your for loop, please modify var drange = sheet.getRange(1,i,1,1) to sheet.getRange(i + 2, 1, 1, sheet.getLastColumn()).

  • For example, when you want to use your for loop, you might be able to modify as follows.

      for (var i = awValues.length - 1; i >= 0; i--) {
    if (awValues[i][0] == 1) {
      var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Warehouse 1");
      var drange = sheet.getRange(i + 2, 1, 1, sheet.getLastColumn());
      drange.moveTo(destination.getRange(destination.getLastRow() + 1, 1));
    }
  }

  • But, when SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Warehouse 1"), moveTo and getValues() is used in a loop, the process cost will be high.

So, in this answer, I would like to propose the following modified script.

Modified script:

function splitYards2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("All Warehouses");
  var range = sheet.getRange("A2:D" + sheet.getLastRow());
  var values = range.getValues();
  var sheets = [...new Set(values.map(r => r[1]))].reduce((o, e) => (o[e] = ss.getSheetByName("Warehouse " + e) || ss.insertSheet("Warehouse " + e), o), {});
  [...values.reduce((m, r) => m.set(r[1], m.has(r[1]) ? [m.get(r[1]), r] : r), new Map())]
    .forEach(([k, v]) => {
      var s = sheets[k];
      if (s) s.getRange(s.getLastRow() + 1, 1, v.length, v[0].length).setValues(v);
    });
  range.clearContent();
}
  • When this script is run, the rows are moved from All Warehouses sheet to Warehouse # sheet by checking the column "B".

About some unclear points, I guessed as follows.

  • I couldn't understand that when the sheet of the sheet name retrieved from the column "B" is not found, what you want to do. So, in this modification, when the sheet name of Warehouse # retrieved from the column "B" is not existing, new sheet is inserted as the sheet name.

References:

Upvotes: 2

metro
metro

Reputation: 508

Your code appears to be grabbing the incorrect range to copy to the destination sheet.

The starting index of awValues is 0. Your code calls sheet.getRange(1,i,1,1), but this fails to consider that the indexes in your array do not align with the row numbers on your sheet. Index 0 of your array actually corresponds to row 2 of your sheet, index 1 -> row 3, and so on.

You should adjust the starting row of your range selection as such:

var drange = sheet.getRange(1,i+2,1,1);

Two additional side notes:

  1. Your iteration of awValues begins at index 1, not 0. This means that you're inadvertently skipping over the first row.
  2. awValues is a two-dimensional array because you called getValues. To avoid confusion, it would be best to treat it as such and change your if condition to awValues[i][0] == 1 (i.e. first column of row i).

Upvotes: 0

Related Questions