Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

178
Views
Pulling data from multiple workbooks and sheet

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;

}
about 4 years ago · Juan Pablo Isaza
1 answers
Answer question

0

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.

about 4 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!