Consolidate data into one master Google sheet using App Scripts

0
votes

I am trying to consolidate data from multiple tabs into a consolidated sheet. Each tab is an individual form and has the same format. On the consolidated sheet, I want to re-arrange the data so the data field name is in a column, and data values are in rows. I tried the following:

function consolidateData(){

// defined all variables
   var sheetNames = [];
   var dataSheet = [];
   var dataValues = [];
   var conso=[];
   var header = [["Faculty Name","Faculty ID","Date of Joining"]];
   var ws = SpreadsheetApp.getActiveSpreadsheet();

 // get all sheets
   var allsheets = ws.getSheets();

 for(var s in allsheets)
 var sheet = allsheets[s];    
 sheetNames[s] = sheet.getName();
 dataSheet[s] = ws.getSheetByName(sheetNames[s]);

// writing data into new sheet
  var newSheet = ws.insertSheet().setName("Consolidated_Data");
   newSheet.getRange("A1:C1").setValues(header);

  var name = dataSheet[s].getRange("B1").getValue();
  var id = dataSheet[s].getRange("B3").getValue();
  var doj = dataSheet[s].getRange("B5").getValue();

 var faculty = [(name),(id),(doj)];//convert into array
 var facultycount = faculty.length;
  for (var i = 0; i < faculty.length; i++)

  //Loop through all rows and write them out starting on ROW2

 {
  newSheet.getRange(2 + i, 1).setValue(faculty[0]);//
  newSheet.getRange(2 + i, 2).setValue(faculty[1]);//
  newSheet.getRange(2 + i, 3).setValue(faculty[2]);// 

  }
  }

There are four tabs and I expect to see results from each tab in the Consolidated_Data tab. But I only saw the last tab data got inserted repeatedly. Can anyone help? Thank you. Consolidated Data Sheet Example of an individual tab

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

While traversing through all your sheets, you haven't used curly braces after the for loop - 

for(var s in allsheets)

So it's running the loop and the value of s stays at the last index of allsheets.

However, might I suggest a simplified version I have tested out - 

function consolidateData () {

  const headers = ["Faculty Name", "Faculty ID", "Date of joining"];
  const rows = { "name": 0, "id": 2, "doj": 4 };
  const consolidatedSheetName = "Consolidated_Data";

  const ss = SpreadsheetApp.getActive();
  const sheets = ss.getSheets();
  let consolidatedValues = [];  

  // Setting headers
  consolidatedValues.push(headers); 

  // Fetching values
  for(let sheet of sheets) {

    if(sheet.getName()==consolidatedSheetName) { continue; }
    let data = sheet.getRange("B1:B5").getValues();
    let faculty = [ data[rows.name][0], data[rows.id][0], data[rows.doj][0] ];
    consolidatedValues.push(faculty);
  }

  // Adding to sheet
  let consolidatedSheet = ss.getSheetByName(consolidatedSheetName);
  if(!consolidatedSheet) {
    consolidatedSheet = ss.insertSheet().setName(consolidatedSheetName);
  }

  let range = consolidatedSheet.getRange(1, 1, consolidatedValues.length, consolidatedValues[0].length); // 1, 1, 3, 3
  range.setValues(consolidatedValues);
}
1
votes

Issue:

The script is overwriting the same range here:

newSheet.getRange(2 + i, 1).setValue(faculty[0]);

Solution:

Add 1 for each row added:

newSheet.getRange(2 + i + s, 1).setValue(faculty[0]);

Or use sheet#appendRow()

newSheet.appendRow(faculty);

If you practice best practices, Your script can be simplified like this: 

const consolidate = () => {
  const ws = SpreadsheetApp.getActiveSpreadsheet(),
    oldSheets = ws.getSheets(),
    newSheet = ws.insertSheet().setName('Consolidated_Data');

  newSheet.getRange(1, 1, 1 + oldSheets.length * 3, 3).setValues([
    ['Faculty Name', 'Faculty ID', 'Date of Joining'],
    ...oldSheets.map(sheet =>
      sheet
        .getRange('B1:B5')
        .getValues()
        .reduce((a, c, i) => (i % 2 === 0 ? [...a, c[0]] : a), [])
    ),
  ]);
};