Advertisement
Guest User

Untitled

a guest
Jun 18th, 2019
106
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function scheduleStackedDataMove() {
  2.   //get list of sheets to iterate over
  3.   var sheet = SpreadsheetApp.getActive().getSheetByName("ListOfActiveSheets");
  4.   var lastRow = sheet.getLastRow();
  5.   var range = sheet.getRange('A1:A' + lastRow);
  6.   var array = range.getValues();
  7.   Logger.log(array.length);
  8.  
  9.   //Get name of existing sheet with historical data and retrieve sheet object
  10.   var existingSheet = SpreadsheetApp.getActive().getSheetByName("Shared data source");
  11.  
  12.   for(var i = 0;i < array.length; i++){
  13.  
  14.     //Find sheet with name in list and get range of values
  15.     try {
  16.  
  17.       var currentName = array[i][0];
  18.       var currentSheet = SpreadsheetApp.getActive().getSheetByName(currentName);
  19.       var currentLastRow = currentSheet.getLastRow();
  20.      
  21.       //If no data rows below row 15, continue
  22.       if(currentLastRow == 15){
  23.        
  24.         continue
  25.       }
  26.       var currentRange = currentSheet.getRange('A16:D' + currentLastRow);
  27.       var currentValues = currentRange.getValues();
  28.    
  29.      
  30.       //append existing sheet with data from the retrieved range
  31.       var existingSheetLastRow = existingSheet.getLastRow();
  32.       var rangeNotation = "A" + (existingSheetLastRow +1).toString() + ":D" + (existingSheetLastRow + currentValues.length).toString();
  33.       Logger.log(rangeNotation);
  34.      
  35.       existingSheet.getRange(rangeNotation).setValues(currentValues);
  36.      
  37.       //create new array and add it to E-column of sheet
  38.       var nameArray = [];
  39.       var newName = currentName.split("-")[0];
  40.       for(var j = 0;j < currentValues.length; j++){
  41.         var item = [];
  42.         item.push(newName);
  43.         nameArray.push(item);
  44.       }
  45.       var nameRangeNotation = "E" + (existingSheetLastRow +1).toString() + ":E" + (existingSheetLastRow + currentValues.length).toString();
  46.       existingSheet.getRange(nameRangeNotation).setValues(nameArray);
  47.  
  48.    
  49.     }
  50.     catch(err){
  51.       Logger.log(err);
  52.    
  53.     }
  54.    
  55.    
  56.   }
  57.  
  58. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement