Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

179
Visualizações
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 Respostas
Responde à pergunta

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 Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda