Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function scheduleStackedDataMove() {
- //get list of sheets to iterate over
- var sheet = SpreadsheetApp.getActive().getSheetByName("ListOfActiveSheets");
- var lastRow = sheet.getLastRow();
- var range = sheet.getRange('A1:A' + lastRow);
- var array = range.getValues();
- Logger.log(array.length);
- //Get name of existing sheet with historical data and retrieve sheet object
- var existingSheet = SpreadsheetApp.getActive().getSheetByName("Shared data source");
- for(var i = 0;i < array.length; i++){
- //Find sheet with name in list and get range of values
- try {
- var currentName = array[i][0];
- var currentSheet = SpreadsheetApp.getActive().getSheetByName(currentName);
- var currentLastRow = currentSheet.getLastRow();
- //If no data rows below row 15, continue
- if(currentLastRow == 15){
- continue
- }
- var currentRange = currentSheet.getRange('A16:D' + currentLastRow);
- var currentValues = currentRange.getValues();
- //append existing sheet with data from the retrieved range
- var existingSheetLastRow = existingSheet.getLastRow();
- var rangeNotation = "A" + (existingSheetLastRow +1).toString() + ":D" + (existingSheetLastRow + currentValues.length).toString();
- Logger.log(rangeNotation);
- existingSheet.getRange(rangeNotation).setValues(currentValues);
- //create new array and add it to E-column of sheet
- var nameArray = [];
- var newName = currentName.split("-")[0];
- for(var j = 0;j < currentValues.length; j++){
- var item = [];
- item.push(newName);
- nameArray.push(item);
- }
- var nameRangeNotation = "E" + (existingSheetLastRow +1).toString() + ":E" + (existingSheetLastRow + currentValues.length).toString();
- existingSheet.getRange(nameRangeNotation).setValues(nameArray);
- }
- catch(err){
- Logger.log(err);
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement