SHARE
TWEET

Stack Overflow - Webapplications - Custom data transpose

RadekVechet Dec 1st, 2013 87 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function transposeColumns(sheetName) {
  2.  
  3.   if(sheetName == null) sheetName="students";
  4.  
  5.  // loading sheets to work with
  6.  var ss = SpreadsheetApp.getActiveSpreadsheet();
  7.  var printSheet = ss.getActiveSheet();
  8.  var sheet = ss.getSheetByName(sheetName);
  9.  
  10.  // we need to know how wide and tall is the sheet
  11.  var lastRow = sheet.getLastRow();
  12.  var lastColumn = sheet.getLastColumn();
  13.  
  14.  /* storing data - it's basically array of arrays
  15.  [
  16.    [Mentor 1, student 1, student 2, student 3, ...],
  17.    [Mentor 2, student 1, student 2, ...],
  18.    [Mentor 3, student 1, ...]
  19.  ]
  20.  */
  21.  var foundMentors = new Array();
  22.  
  23.   //set variables to loop through data, scanning begins at cell C2 (coordinates 2,3 - ROW,COLUMN)
  24.   var activeR = 2;
  25.   var activeC = 3;
  26.   var step = 0
  27.   var pushedMentors = 0;
  28.   var studentPushed = 0;
  29.  
  30. //loop through data on sheet  
  31.   for(var r = activeR; r <= lastRow;r++){
  32.     for(var c = activeC; c <= lastColumn;c++){
  33.       step++;
  34.       var mentorName = sheet.getRange(r,c).getValue();
  35.       Logger.log(step+' -- cell:'+r+','+c+' = '+mentorName);
  36.       // if the cell is not empty
  37.       if(mentorName != ''){
  38.         // checking if mentor is unique/already found, id relates to array key
  39.         var mentorId = findMentor(foundMentors, mentorName);
  40.         Logger.log('mentorName: '+mentorName+': has id:'+mentorId);
  41.         // Mentor does not yet exists
  42.         if(mentorId === undefined){
  43.           // adding him to array with current student
  44.           foundMentors[pushedMentors] = new Array(mentorName, sheet.getRange(r,2).getValue());
  45.           pushedMentors++;
  46.         // Mentor exists, push new student name in its array
  47.         } else {
  48.            var newStudent = sheet.getRange(r,2).getValue();
  49.            // checking if the student is unique in Mentors stack/array
  50.            var studentExists = existsInArray(foundMentors[mentorId],newStudent);
  51.            Logger.log('newStudent: '+newStudent+': exists:'+studentExists);
  52.           // student is unique - adding him to Mentors array
  53.            if((studentExists == false) && (newStudent !== undefined)){
  54.              foundMentors[mentorId].push(newStudent);
  55.              studentPushed++;
  56.            }
  57.          }
  58.        }
  59.     }
  60.   }
  61.  
  62.   // loop for printing results (names) into current sheet
  63.   for(var r = 0; r < foundMentors.length; r++){
  64.     for(var c = 0; c < foundMentors[r].length; c++){
  65.       var studentName = foundMentors[r][c];
  66.       // acutal coordinates of cells are shifted
  67.       printSheet.getRange(r+2, c+1).setValue(studentName);
  68.     }
  69.   }
  70.  
  71.   // loop for printing out generic student labels
  72.   var printSheetlastColumn = printSheet.getLastColumn();
  73.   for(c = 2; c <= printSheetlastColumn; c++){
  74.      printSheet.getRange(1, c).setValue("student " +(c-1) + " name");
  75.   }
  76.  
  77.   // printing out first cell in table
  78.   printSheet.getRange(1, 1).setValue("mentor names");
  79.  
  80.   // searching function for existing students in mentors arrays - returns boolean
  81.   function existsInArray(searchArray, term){
  82.     var result = false;
  83.     for(var i=1; i<searchArray.length; i++){
  84.       if (searchArray[i] == term){
  85.         result = true;
  86.         break;
  87.       }
  88.     }
  89.     return result;
  90.   }
  91.  
  92.   // searching function for existing mentors in main array - returns undefined or key of found value
  93.   function findMentor(searchArray, name){
  94.     var result = undefined;
  95.     for(var i=0; i<searchArray.length; i++){
  96.       if (searchArray[i][0] == name){
  97.         result = i;
  98.         break;
  99.       }
  100.     }
  101.     return result;
  102.   }
  103.  
  104.   Logger.log(foundMentors.toString());
  105.   Logger.log(' pushedMentors: ' +pushedMentors+ ' studentsPushed: '+studentPushed+ '  steps: '+step);
  106. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top