Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function iterateSheets()
- {
- var ss=SpreadsheetApp.getActive();
- var sh=ss.getSheetByName('filesSheet');
- sh.clear();
- var folder=DriveApp.getFolderById('1axTLHOBPhl5_u7ngFOxViHg14OSw7pVC');//replace id with actual id of folder
- var files=folder.getFilesByType(MimeType.GOOGLE_SHEETS);
- var consol_sheet = SpreadsheetApp.openById('18JnkQYa1L-FhXFmKJBrqJ6GI7BBO-V8QAVKP8PLLoXo').getActiveSheet();
- var x=2;
- var z=0;
- var final_values = new Array(50000);
- while(files.hasNext())
- {
- var file=files.next();
- var ts=SpreadsheetApp.openById(file.getId());
- var allShts=ts.getSheets();
- for(var i=0;i<3;i++) //allShts.length
- {
- var consol_values = allShts[i].getRange(8,1,40,26).getValues(); //.getRange(8, 1, 1, 1).getValues();
- var headers = allShts[i].getRange(7,6,1,20).getValues(); //.getRange(8, 1, 1, 1).getValues();
- var position= allShts[i].getRange("B1").getValue();
- var period = allShts[i].getRange("B2").getValue();
- var email = allShts[i].getRange("B3").getValue();
- for (var z = 0;z<20;z++)//
- {
- for (var y= 0;y<40;y++)
- {
- if (consol_values[y][i] != "")
- {
- /* consol_sheet.getRange(x,1).setValue(consol_values[y][0]); //AI pack
- consol_sheet.getRange(x,3).setValue(consol_values[y][1]); //measure
- consol_sheet.getRange(x,4).setValue(email);
- consol_sheet.getRange(x,5).setValue(position);
- final_values[x] = [];
- final_values[x][0] =consol_values[y][0]; //AI pack
- //blank
- final_values[x][2] =(consol_values[y][1]); //measure
- final_values[x][3] =(email);
- final_values[x][4] =(position);
- final_values[x][5] =(headers[0][z]);//location
- final_values[x][6] =1;
- final_values[x][7] =(period);
- final_values[x][8] =(consol_values[y][3]); //price
- final_values[x][9] =(consol_values[y][z+5]); //fcst
- if (consol_values[y][z+5] != "")
- {
- final_values[x][10] =(consol_values[y][25]); //fcst value
- }
- x = Number(x)+1 //row count for consolidation output
- }
- }
- }
- }
- }
- consol_sheet.getRange(2,1,x,10).setValues(final_values);
- }
- [
- [1,,'orange'], // 2nd element of this inner array is empty
- , // 2nd element of the outer array is empty
- [] // This array is empty
- ]
- [
- [1,'','orange'],
- ['','',''],
- ['','','']
- ]
- consol_sheet.getRange(2,1,final_values.length,final_values[0].length).setValues(final_values);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement