title: Google App Scripts(spreadsheet) - consolidate data into one sheet
body: <p><b>Here is the set up</b><br><br>
  We have a contest with all employees based on project scores. Each project has two categories of employees(4 employees per category) and two scores(one for each category of employee).<br><br>
  I need to grab all the scores for the employees and output it into a spreadsheet. <a href="https://docs.google.com/spreadsheets/d/1kF0JeIS9mNR1VneNiC8b4__kqRgrQvbcVdTL68wZtTA/edit?usp=sharing" rel="nofollow noreferrer">The following spreadsheet has misc. columns removed</a><br><br>
  <b>Sheet Explanation</b><br>
  The sheet labeled "Example data" is the source we will be pulling data from<br></p>
  
  <ol>
  <li>We need to match Editor and Editor Score<br>  </li>
  <li>We need to match Webmaster and webmaster score<br></li>
  </ol>
  
  <p>The sheet labeled "Example output" is what I want to be generated in another spreadsheet named "Contest Result" with the sheet name from the source sheet(They are named by date ranges).<br><br></p>
  
  <ol>
  <li>We need to compile each employee by the categories</li>
  <li>We need to compile all scores to the row for a singular employee<br></li>
  </ol>
  
  <p>I had found this <a href="https://developers.google.com/apps-script/articles/removing_duplicates" rel="nofollow noreferrer">Removing Duplicates Article</a> that seemed to at least process the information and compare it in a manner that I think this can be done, but am failing to make it work due to being inexperienced. </p>
  
  <p>Did not know what Transpose was till someone commented :)
  Here is the solution in another article for how to pull it off with Google Apps Script and with using the spreadsheet option. 
  <a href="https://stackoverflow.com/questions/17530499/how-to-split-and-transpose-results-over-2-columns">How to split and transpose results over 2 columns</a></p>
  
  <p>Here is the actual code I used to make it work(it is a little horrible but I tried) suggestions on how to improve this?:</p>
  
  <pre><code>function createScoreSheet() {
  
    // Get Source spreadsheet
    var source = SpreadsheetApp.getActive();
    var sourceSheet = source.getActiveSheet();
    var SourceActivate = sourceSheet.activate();
    // Set Sheet Name
    var sheetName = sourceSheet.getSheetName();
    // Set Values to transpose and combine
    var sourceEditor = sourceSheet.getRange("C1:C51");
    var sourceWeb = sourceSheet.getRange("D1:D51");
    var editorScores = sourceSheet.getRange("L1:L51");
    var webScores = sourceSheet.getRange("K1:K51");
    // Used to create a new spreadsheet
    var sheetNameNew = sheetName + " Scores";
    var createSheet = SpreadsheetApp.getActive().insertSheet(sheetNameNew,0);
    var targetSheet = source.getSheetByName(sheetNameNew);
    var totalScore = 1;
    // s is the the counter we use to stick values into the rows
    var s = 3;
    // n is the the counter we use to stick values into the columns
    var n = 1;
    // loops through twice, once for the editor values, once for the webmaster
    for (var j = 1; j&lt;3; j++) {
      if (j == 1) {
        // grab values for the editors and copy to new sheet
        sourceEditor.copyTo(targetSheet.getRange("A1"));
        editorScores.copyTo(targetSheet.getRange("B1"));
        // delete the header row then sort the column ASC by default     
        targetSheet.deleteRow(n);
        targetSheet.sort(1);
        // Find the last value to see how many scores we have
        var lastRow = targetSheet.getLastRow();
      }
      if (j == 2) {
        // grab values for the webmasters and copy to new sheet
        sourceWeb.copyTo(targetSheet.getRange(n,1));
        webScores.copyTo(targetSheet.getRange(n,2));
        // delete the header row then sort the column ASC by default 
        targetSheet.deleteRow(n);
        lastRow = targetSheet.getLastRow();
        targetSheet.getRange(n,1,lastRow,2).sort(1);
        lastRow = targetSheet.getLastRow();
      }
      // this loop will check to see if the value of the cell is equal to the next on the list and move the score
      for (var i = 1; i&lt;lastRow+1; i++) {
        // Grab the name of the current row and the next
        var firstName = targetSheet.getRange(n,1).getValue();
        var nextName = targetSheet.getRange(n+1,1).getValue();
        // Grab the scores
        var oldScore = targetSheet.getRange(n+1,2);
        var newScore = targetSheet.getRange(n,s);
        // Loop to check to see if the firstname is blank and break to find the next value
        if (firstName === "") {
         break; 
        }
        // checks to see if name is equal to the next then shifts then copies the score and adjust the horizontal position
        if (firstName == nextName) {
          totalScore = oldScore + newScore;
          oldScore.copyTo(newScore);
          s = s+1;
          targetSheet.deleteRow(n+1);
        }
        // resets horizontal position for the score and increases the row 
        else {
          s=3;
          n=n+1;
        }
      }
      // kills remaining rows
      targetSheet.deleteRows(n,37);
    }
  }
  </code></pre>