Loop through Sheet, rearrange range/array and write it to a new sheet

Question

I have a spreadsheet and I want to take a range of data and manipulate it to look differently. I am thinking that I will have set-up multiple loops

1 - To get the main student data (A2:I26) and add it the new sheet.

2- Then loop the range of Test Headings, ex. A,B,C,etc and add those behind the student details.

3- Then another loop to grab the range of scores under each test heading an add them after the test name.

I started writing a script that gets the sheet ranges etc, but I am not sure how to add the loops.

FYI - Some Sheets I have to convert have more Headings of scores than Just A,B,C...Some Sheets I have to work with might just have A scores, some might have scores for A-E etc.

Thanks for any help you can give.

Brandon

***THE SHEET IMAGES AND SIMPLE SCRIPT ARE BELOW

I am looking to take the sheet that looks like this:

And use a script to make it look like this:

function dataReport() {

   var thisSS = SpreadsheetApp.getActiveSpreadsheet(),
       classData = thisSS.getSheets()[3], //The Sheet with the original data 
       dataLastRow = classData.getLastRow(),
       Avals = classData.getRange("A1:A").getValues(),
       Alast = Avals.filter(String).length,

       classDataRange = classData.getRange(3, 1,Alast, 9), 
       dataArray = classDataRange.getValues();
      
       var testNames = classData.getRange(2, 10, 1, classData.getLastColumn()-9), 
       tnArray = testNames.getValues();

       var reportSheet = thisSS.getSheets()[4]; //The sheet to insert the new data
       var reportSheetNewRow = reportSheet.getLastRow() +1;
       var newDataRange = reportSheet.getRange(reportSheet.getLastRow()+1, 1,Alast, 9);
          
       newDataRange.setValues(dataArray);
        
}

BrandonM BrandonM · Accepted Answer · 2016-02-04T05:07:12

It seems I've found an answer, so I figured I'd post it. The loops to re-arrange the sheet accordingly would look like this. I'm sure there is a faster way, but this is what I came up with.

function dataReport() {

   var thisSS = SpreadsheetApp.getActiveSpreadsheet(),
       classData = thisSS.getSheets()[3],
       dataLastRow = classData.getLastRow(),
       Avals = classData.getRange("A1:A").getValues(),
       Alast = Avals.filter(String).length,

       classDataRange = classData.getRange(3, 1,Alast, 9), 
       
       standardCols = classData.getRange("J2:2").getValues(),
       arrayOfStandards = standardCols.join().split(","),
       filtArr = arrayOfStandards.filter(Boolean);
       
       var dataArray = classDataRange.getValues();

    for(var i=0; i < filtArr.length; i++){   
    
        var reportSheet = thisSS.getSheets()[4];
        var reportSheetNewRow = reportSheet.getLastRow() +1;
        var newDataRange = reportSheet.getRange(reportSheetNewRow, 1,Alast, 9);
        
        var avgValues = classData.getRange(3, 10+i, Alast, 1);
        var avgArray = avgValues.getValues();
        var newAvgRange = reportSheet.getRange(reportSheetNewRow, 11,Alast, 1);

        newDataRange.setValues(dataArray);
        newAvgRange.setValues(avgArray); 
         
        for (var j=0, x = dataArray.length; j<x; j++){
        
            reportSheet.getRange(reportSheetNewRow + j, 10).setValue(filtArr[i]);
    }
  }
}

Loop through Sheet, rearrange range/array and write it to a new sheet

1 Answers