I am having trouble getting data from about 40 different workbooks each with two sheets. The sheets are labeled 'Session 1' and 'Session 2'. I thought I wrote the script to divide the data between the two tabs on the master but its filling both tabs with the same data from session one only. I have never posted on here so sorry in advance if I wasn't supposed to include a bulk amount of my script but I have no clue where to go from here.
//while loop to get all files in folder
while(filesIterator.hasNext()){
file = filesIterator.next();
fileType = file.getMimeType();
//if statement to find spreadsheets in folder
if(fileType === "application/vnd.google-apps.spreadsheet"){
ssID = file.getId();
data = getDataFromSpreadsheet(ssID);
session = getSheetNames(ssID);
Logger.log(session);
//Fill last cell in row w/ original file name
data = data.map(function(r){return r.concat([file.getName()]);});
combinedData = combinedData.concat(data);
} //end if
}//end while
//If statements to seperate sessions tabs
if(session = SpreadsheetApp.newFilterCriteria()
.whenTextContains('Session 1')
.build()){
//Build session 1 spreadsheet
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Session 1");
//Clear form after every run
ws.getRange("A2:D" + ws.getLastRow()).clear();
//Write data to spreadsheet
ws.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
} //end if
if(session = SpreadsheetApp.newFilterCriteria()
.whenTextContains('Session 2')
.build()){
//Build session 2 spreadsheet
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Session 2");
//Clear form after every run
ws.getRange("A2:D" + ws.getLastRow()).clear();
//Write data to spreadsheet
ws.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
} //end if
}
//Function to pull all data from workbook
function getDataFromSpreadsheet(ssID) {
//Open workbook
var ss = SpreadsheetApp.openById(ssID);
//Get all sheets in workbook and put into array
var ws = ss.getSheets()[0];
//Get data in the open sheet
var data = ws.getRange("A4:C" + ws.getLastRow()).getValues();
return data;
}
//Function to return name of sheets in workbook
function getSheetNames(ssID) {
var ss = SpreadsheetApp.openById(ssID);
var sheets = ss.getSheets();
var sheetNames = []
sheets.forEach(function (sheet) {
sheetNames.push(sheet.getName());
});
return sheetNames;
}
Among other possible problems, apparently you are confused about how to write "//If statements to seperate sessions tabs" (sic) because the if statement expressions like the following doesn't make sense
session = SpreadsheetApp.newFilterCriteria()
.whenTextContains('Session 2')
.build()
as they always will return true (if you change the comparison operator to == or to === apparently they always will return false).
Try session.find('Session 1') or ~session.indexOf('Session 1')
The first will return null if 'Session 1' is not found which is falsy and the element member which is truthy. The second will return false or true.