SHARE
TWEET

Untitled

a guest Jun 17th, 2019 48 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function iterateSheets()
  2. {
  3.   var ss=SpreadsheetApp.getActive();
  4.   var sh=ss.getSheetByName('filesSheet');
  5.   sh.clear();
  6.   var folder=DriveApp.getFolderById('1axTLHOBPhl5_u7ngFOxViHg14OSw7pVC');//replace id with actual id of folder
  7.   var files=folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  8.   var consol_sheet = SpreadsheetApp.openById('18JnkQYa1L-FhXFmKJBrqJ6GI7BBO-V8QAVKP8PLLoXo').getActiveSheet();
  9.   var x=2;
  10.   var z=0;
  11.   var final_values = new Array(50000);
  12.  
  13. while(files.hasNext())
  14.  {
  15.    var file=files.next();
  16.     var ts=SpreadsheetApp.openById(file.getId());
  17.     var allShts=ts.getSheets();
  18.  
  19.        for(var i=0;i<3;i++)   //allShts.length  
  20.        {  
  21.             var consol_values = allShts[i].getRange(8,1,40,26).getValues();  //.getRange(8, 1, 1, 1).getValues();
  22.             var headers = allShts[i].getRange(7,6,1,20).getValues();  //.getRange(8, 1, 1, 1).getValues();
  23.             var position= allShts[i].getRange("B1").getValue();
  24.             var period = allShts[i].getRange("B2").getValue();
  25.             var email = allShts[i].getRange("B3").getValue();    
  26.  
  27.          for (var z = 0;z<20;z++)//
  28.          {  
  29.  
  30.            for (var y= 0;y<40;y++)              
  31.            {          
  32.                if (consol_values[y][i] != "")
  33.                {
  34.               /*  consol_sheet.getRange(x,1).setValue(consol_values[y][0]);  //AI pack
  35.                 consol_sheet.getRange(x,3).setValue(consol_values[y][1]); //measure
  36.                 consol_sheet.getRange(x,4).setValue(email);
  37.                 consol_sheet.getRange(x,5).setValue(position);    
  38.  
  39.  
  40.                 final_values[x] = [];
  41.                 final_values[x][0]  =consol_values[y][0];  //AI pack
  42.                  //blank
  43.                 final_values[x][2]  =(consol_values[y][1]); //measure
  44.                 final_values[x][3]  =(email);
  45.                 final_values[x][4]  =(position);    
  46.                 final_values[x][5]  =(headers[0][z]);//location
  47.                 final_values[x][6]  =1;
  48.                 final_values[x][7]  =(period);
  49.                 final_values[x][8]  =(consol_values[y][3]); //price
  50.                 final_values[x][9]  =(consol_values[y][z+5]); //fcst
  51.  
  52.                 if (consol_values[y][z+5] != "")
  53.                 {
  54.                     final_values[x][10] =(consol_values[y][25]); //fcst value            
  55.                 }                          
  56.  
  57.  
  58.                 x = Number(x)+1  //row count for consolidation output
  59.  
  60.               }
  61.           }          
  62.          }    
  63.       }
  64.     }
  65.  
  66.  
  67. consol_sheet.getRange(2,1,x,10).setValues(final_values);
  68.  
  69.  }
  70.      
  71. [
  72.   [1,,'orange'], // 2nd element of this inner array is empty
  73.   , // 2nd element of the outer array is empty
  74.   [] // This array is empty
  75. ]
  76.      
  77. [
  78.   [1,'','orange'],
  79.   ['','',''],
  80.   ['','','']
  81. ]
  82.      
  83. consol_sheet.getRange(2,1,final_values.length,final_values[0].length).setValues(final_values);
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top