I have a piece of testing equipment (brake dynamometer) that generates a lot of data and I would like to automate the analysis process a bit.

I would like to have a Google Sheets template that I can copy/paste raw data into, and have that data automatically filtered into different tabs. I would like it divided based on columns A (Cycle) and then subdivided by column B (Test). Each test will have a unique tab, 1:1, 1:2, 1:3,...1:20...20:1...20:20, etc... I anticipate 10k-40k rows of data, 1-20 Cycles and 1-20 Tests within each Cycle, each Test is about 250 rows of data so that could be 100k rows to filter (bonus points will be awarded to anyone who can make it run 'fast').

Not all data sets will have the same amount of Cycles and/or Tests within each Cycle so this template would need to have some flexibility to accommodate these changes, ie, no hard coded lines to look for specific values like Cycle 10 Test 20 because I may only have data for 5 Cycles and 10 Tests in each Cycle.

Sample data

I have seen a few videos and threads on splitting a master sheet into multiple tabs. Unfortunately for me they all are based on one column, while I have two columns required to filter my data. I followed along with a video and was able to filter by the first column but I am not a proficient enough coder to repeat the process to subdivide each of those sheets by an additional column.

If anybody would be willing to help me through the process of adding a second filter that would be much appreciated!

Here are the first and last few rows of some sample data:

CodePudding user response：

function splitData() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Data");
    var data = sh.getDataRange().getValues();
    var cycle = data[1][0];  // Skip the first line header
    var test = data[1][1];
    var results = [data[1]];  // Put the first row in result
    var i = 0;
    function newTab(ss,cycle,test,results) {
      try {
        var name = "C_" cycle "_T_" test;
        var sh = ss.getSheetByName(name);
        if( sh ) throw "Error sheet [" name "] already exists"
        sh = ss.insertSheet(name);
        sh.getRange(1,1,results.length,results[0].length).setValues(results);
      }
      finally {
        return;
      }
    }
    for( i=2; i<data.length; i   ) {  // Now we can start with row 2
      if( data[i][0] === cycle ) {
        if( data[i][1] === test ) {
          results.push(data[i]);
        }
        else {  // New test
          newTab(ss,cycle,test,results);
          test = data[i][1];
          results = [data[i]];
        }
      }
      else {  // New cycle
        newTab(ss,cycle,test,results);
        cycle = data[i][0];
        test = data[i][1];
        results = [data[i]];
      }
    }
  }
  catch(err) {
    Logger.log(err)
  }
}

You could also import your TSV file with this:

function importTSV() {
  try {
    // Its best to get by Id otherwise if you have a lot of files getFilesByName can take a while
    var file = DriveApp.getFileById("1EV_uSnOcCUU-k9NinSnI7-aaQ_7xEae4");
    var data = file.getBlob().getDataAsString();
    data = data.split("\n");
    var results = [["CYCLE","TEST","TIME","RPM"]]; // I only have 4 values per row in my test data 
    for( var i=0; i<data.length; i   ) {
      results.push(data[i].split("\t"));
    }
    Logger.log(results)
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Someone voted this post down but what I've seen is everyone's situation is a bit different and trying to learn Google app script it helps if you can see some example code