Apps Script for merging data from all Google sheets in a folder into a master Google spreadsheet

Question

How do I correct this AppsScript code (written by someone else) to merge data from all Google sheets (form responses from various google surveys with the exact same questions) in a named folder into a master sheet with headers and without lots of blank lines?

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Refresh data', 'mainFunc')
      .addToUi();
}

function mainFunc(){
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var activeSheet = activeSpreadsheet.getSheetByName(SHEET_NAME);
  var header = activeSheet.getRange(1, 1, 1, activeSheet.getLastColumn()-1).getValues()[0]

  var folder = DriveApp.getFolderById(FOLDER_ID);
  var files = folder.getFiles();
  var combinedData= [];
  while(files.hasNext()){
    var file = files.next();
    if(file.getMimeType() === "application/vnd.google-apps.spreadsheet"){
      var data = getDataFromSource(file.getId(), header);
      Logger.log(data)
      data = data.map(function(r){return r.concat([file.getName()]);});
      combinedData = combinedData.concat(data);
    }
  }

  activeSheet.getRange("A2:AA2000").clearContent();
  activeSheet.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
}

function getDataFromSource(ssId, header){
  var spreadsheet = SpreadsheetApp.openById(ssId);
  var firstSheet = spreadsheet.getSheets()[0];
  var data = firstSheet.getRange(1, 1, firstSheet.getLastRow(),     firstSheet.getLastColumn()).getValues();
  var pos = []
  for(var i=0; i<header.length; i++){
    var colIndex = data[0].indexOf(header[i])
      pos.push(colIndex)
  }
  data.shift()
  var processedData = shiftColumns(data, pos)
  return processedData
}

function test(){
  var old_arr;
  var new_arr;

  old_arr = [
    [1, 2, 3, 4],
    [2, 2, 6, 4],
    [4, 2, 3, 4],
    [9, 2, 7, 4]
  ];
  new_arr = shiftColumns(old_arr, [3, 0, -1, 2]);
  Logger.log(new_arr);
}

function shiftColumns(rows, pos) {
  return rows.map(function(cols) {
    return pos.map(function(i) {
      if(cols[i])
        return cols[i];
      else
        return ""
    });
  });
}

I copied the code from another sheet that works and the only changes made where the FOLDER_ID and SHEET_NAME to the relevant folder and the master sheet name on the new document.

It seems to run (execution started and execution completed appear in the execution log) but only a few lines of random data that appear on the sheet with no headers and lots of blank lines in between.

The source data for the original sheet that I copied this from is laid out in the same way (form responses from a Google survey) and displays it correctly on the original master sheet.

Very new to Apps Script, so any help appreciated.

Answer 1

There were a few issues in the original code, but primarily capturing empty cells and ranges.

Here is a revised version:

function onOpen() {

  const ui = SpreadsheetApp.getUi()

  ui
    .createMenu('Custom Menu')
    .addItem('Refresh data', 'mainFunc')
    .addToUi()

}

function mainFunction() {

  const SHEET_NAME = ``
  const FOLDER_ID = ``

  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(SHEET_NAME)

  const sheetHeaders = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()
    .flat()

  sheet
    .getDataRange()
    .offset(1, 0, sheet.getLastRow()-1)
    .clearContent()

  const folderSheetData = getSheetsDataObject(FOLDER_ID)

  sheetHeaders
    .forEach((header, colIndex) => {

      if (folderSheetData[header]) {

        const columnData = folderSheetData[header]

        sheet
          .getRange(2, colIndex+1, columnData.length)
          .setValues(columnData)

      }

    })

}

function getSheetsDataObject(folderID) {

  const files = DriveApp
    .getFolderById(folderID)
    .getFilesByType(MimeType.GOOGLE_SHEETS)

  let filesData = []

  while (files.hasNext()) {

    const [headers, ...sheetData] = SpreadsheetApp
      .openById(files.next().getId())
      .getSheets()[0]
      .getDataRange()
      .getValues()

    const sheetValuesByHeader = sheetData
      .reduce((data, row) => {

        headers.forEach((header, index) => {
          if (row[index] === ``) return
          if (!data[header]) data[header] = []
          data[header].push(row[index])
        })

        return data

      }, {})

    filesData.push(sheetValuesByHeader)

  }

  const consolidateData = filesData.reduce((data, fileData) => {

    Object.keys(fileData).forEach((header) => {
      if (!data[header]) data[header] = []
      data[header] = [...data[header], ...fileData[header]]
    })

    return data
  
  }, {})

  return consolidateData

}

Commented:

function mainFunction() {

  // Insert your IDs.
  const SHEET_NAME = ``
  const FOLDER_ID = ``

  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(SHEET_NAME)

  const sheetHeaders = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()
    .flat()

  sheet
    .getDataRange()
    .offset(1, 0, sheet.getLastRow()-1)
    .clearContent()

  // Get all data from sheets in folder, organized by { "Header": [...rows] }
  const folderSheetData = getSheetsDataObject(FOLDER_ID)

  // For each header of primary sheet...
  sheetHeaders.forEach((header, colIndex) => {

    // If the current sheet header exists in the data from other sheets..
    if (folderSheetData[header]) {
      
      // Get the row data for this header..
      const columnData = folderSheetData[header]

      // and set it to the current column's index.
      sheet
        .getRange(2, colIndex+1, columnData.length)
        .setValues(columnData)

    }

  })

}

function getSheetsDataObject(folderID) {

  // Get all Sheets in specified folder.
  const files = DriveApp
    .getFolderById(folderID)
    .getFilesByType(MimeType.GOOGLE_SHEETS)

  let filesData = []

  while (files.hasNext()) {

    // Get headers and values of the Sheet..
    const [headers, ...sheetData] = SpreadsheetApp
      .openById(files.next().getId())
      .getSheets()[0]
      .getDataRange()
      .getValues()

    // Convert sheet values to { "Header": [...rows] }
    // (For each row..)
    const sheetValuesByHeader = sheetData.reduce((data, row) => {

      // (For each sheet column - identified by header..)
      headers.forEach((header, index) => {
        // (If cell is blank, skip.)
        if (row[index] === ``) return
        // (If this header has not been added to the result, add it.)
        if (!data[header]) data[header] = []
        // Add the current row/column cell value.
        data[header].push(row[index])
      })

      return data

    }, {})

    // Add this Sheet's data to the collection.
    filesData.push(sheetValuesByHeader)

  }

  // Consolidate all Sheet data into one big object.
  // (For each Sheet..)
  const consolidateData = filesData.reduce((data, fileData) => {

    // (Get each header of this Sheet..)
    Object.keys(fileData).forEach((header) => {
      // (If this header doesn't exist yet in the final result, add it.)
      if (!data[header]) data[header] = []
      // (Add this Sheet's column data for this header to the final result.)
      data[header] = [...data[header], ...fileData[header]]
    })

    return data
  
  }, {})

  return consolidateData

}

I used snippets from one of my projects, so if you have any errors please let me know and I'll help you as soon as possible!