CODEWITHSUNDEEP
google-apps-script
Taaut
Taaut

Reputation: 457

Google Apps Script: work with multiple sheets

I have a sheet with some information about users here an example:

Name, Time, Location Peter,15.01.15, Frankfurt Michael, 18.02.15, Frankfurt Peter,17.02.15, Paris Michael, 17.02.15, Paris

What i would like is to create for each unique user a new sheet and sort the new sheet. Is that possible with GAS? It should look like this:

/New Sheet/ Name, Time, Location Peter,15.01.15, Frankfurt Peter,17.02.15, Paris /New Sheet/ Name, Time, Location Michael, 17.02.15, Paris Michael, 18.02.15, Frankfurt

Thanks. EDIT:

  function getall(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(), 
  sourceSheet = ss.getSheetByName('Source'),
  newSheet = ss.insertSheet('newSheet'),
  sourceRange = sourceSheet.getDataRange(),
  sourceRows = sourceRange.getValues();
  newSheet.appendRow(sourceRows[0]);
  var   i;
  for (i = 1; i < sourceRows.length; i += 1) {

  newSheet.appendRow(sourceRows[i]);

  } 

  Browser.msgBox("New Sheet Added!");
  }

Edit 2:

  var ss = SpreadsheetApp.getActiveSpreadsheet(), 
  sourceRange = ss.getDataRange(),
  sourceRows = sourceRange.getValues();

  var users = {},
  colWithTheUsersNames = 6,
  currSheet;

  for( var lin = 1; lin < sourceRows.length; lin++ ){
  if( !users[ sourceRows[lin][colWithTheUsersNames] ] ) sourceRows[lin]     [colWithTheUsersNames] = [];

  users[ sourceRows[lin][colWithTheUsersNames] ].push( sourceRows[lin]  [colWithTheUsersNames] );
  }

 for( var usr in users ){
 currSheet = ss.insertSheet( usr );
 currSheet.getRange(1,1, users[usr].length, users[usr][0].length  ).setValues(users[usr]);
 }

Edit 3: 

var ss = SpreadsheetApp.getActiveSpreadsheet(), 
  sourceRange = ss.getDataRange(),
  sourceRows = sourceRange.getValues(), 
  users = {},
  colWithTheUsersNames = 6,
  currSheet;

for( var lin = 1; lin < sourceRows.length; lin++ ){

if( users[ sourceRows[lin][colWithTheUsersNames] ] ) sourceRows[lin][colWithTheUsersNames] = [];
    for(var column = 0; column < sourceRows.length; column++){
      if("undefined" != typeof users[sourceRows[lin][colWithTheUsersNames]]) {
          if("undefined" != typeof sourceRows[lin][column]){
        users[sourceRows[lin][colWithTheUsersNames] ].push(sourceRows[lin][column]);
          }
      }
      else {
        users[sourceRows[lin][colWithTheUsersNames] ] = new Array();
        if("undefined" != typeof sourceRows[lin][column]){
        users[sourceRows[lin][colWithTheUsersNames] ].push(sourceRows[lin][column]);
        }
      }
    }

}
//   users[ sourceRows[lin][colWithTheUsersNames] ].push( sourceRows[lin][column]);

var i = 0;
for( var usr in users ){
  currSheet = ss.insertSheet( usr );
  Logger.log(typeof users[usr]);
  currSheet.getRange(1,1, users[usr].length, users[usr].length ).setValues(users[usr]);
}

Edit 4:

function getallEmployer(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(), 
  sourceRange = ss.getDataRange(),
  sourceValue = sourceRange.getValues(),
  sourceRows = sourceRange.getNumRows(),
  users = {},
  colWithTheUsersNames = 7,
  currSheet;

for( var lin = 1; lin < sourceRows; lin++ ){

if( users[ !sourceValue[lin][colWithTheUsersNames] ] ) sourceValue[lin][colWithTheUsersNames] = [];
  //for(var column = 0; column < sourceValue[lin].getNumColumns; column++){
      if("undefined" != typeof users[sourceValue[lin][colWithTheUsersNames]]) {
          if("undefined" != typeof sourceValue[lin]){
        users[sourceValue[lin][colWithTheUsersNames] ].push(sourceValue[lin]);
          }
      }
      else {
        users[sourceValue[lin][colWithTheUsersNames] ] = new Array();
        if("undefined" != typeof sourceValue[lin]){
        users[sourceValue[lin][colWithTheUsersNames] ].push(sourceValue[lin]);
        }
     // }
    }

}
//   users[ sourceRows[lin][colWithTheUsersNames] ].push( sourceRows[lin][column]);

var i = 0;
for( var usr in users ){
  currSheet = ss.insertSheet( usr );
  var range = currSheet.getRange("A1:D1");
  var parameters = [["Date","Name","Project Category", "Time","Nicht verrechenbar","Bemerkung"]];
  range.setValues(parameters);
  Logger.log(typeof users[usr]);
  currSheet.getRange(2,1, users[usr].length, users[usr][0].length ).setValues(users[usr]);
}

}

Upvotes: 0

Views: 229

Answers (1)

Kriggs
Kriggs

Reputation: 3778

Here's one way to do it, creates a new sheet for every user, and append alls rows of that user to thy sheet:

var users = {},
    colWithTheUsersNames = ??,
    currSheet;

for( var lin = 0; lin < sourceRows.length; lin++ ){
   if( !users[ sourceRows[lin][colWithTheUsersNames] ] ) sourceRows[lin][colWithTheUsersNames] = [];

   users[ sourceRows[lin][colWithTheUsersNames] ].push( sourceRows[lin][colWithTheUsersNames] );
}

for( var usr in users ){
   currSheet = ss.insertSheet( usr );

   currSheet.getRange(1,1, users[usr].length, users[usr][0].length ).setValues(users[usr]);
}

Not tested but should work.

Now only need to implement verification for sheets already present and such.

Upvotes: 1

Related Questions