Merge Multiple Google Spreadsheets into One Sheet

3
votes

I have a couple of Google Spreadsheets. This Spreadsheets have the same columns number and columns names, but diffrent data.

I want to merge all of this Spreadsheets into one Sheet. The closest thing i found is this: http://ctrlq.org/code/19980-merge-google-spreadsheets , but it make new Spreadsheet with diffrent Sheet for every Sheets on Spreadsheets. I want just one Sheet with all data. Do you guys have idea how to do that?

2
arraysgoogle-sheetsgoogle-sheets-formula

2 Answers

11
votes

Short answer

Use embedded arrays. The simplest form for spreadsheets with regional settings that use dot as decimal separator are

  • ={A1:A2;B1:B2} for vertical merging and
  • ={A1:A2,B1:B2} for horizontal merging.

Explanation

To merge vertically two ranges,

  • for spreadsheets using dot as a decimal separator and comma as function parameter separator

    ={Sheet1!A1:B5;Sheet2!A1:B5}

  • for spreadsheets using comma as a decimal and semicolon as function parameter separator

    ={Sheet1!A1:B5\Sheet2!A1:B5}

Reference

0
votes

You can use Google Apps Scripts for this.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var All = ss.insertSheet("All-Values");

function TotalsSheet() {
  var totaldata = [];
  var sheets = ss.getSheets();
  var totalSheets = 2;

  for (var i=0; i < totalSheets; i++) {
    var sheet = sheets[i];
    var range = sheet.getDataRange();
    var values = range.getValues();

    for (var row in values) {
      totaldata.push(values[row]);
    }
  }
  return totaldata; 
}

function Start() {
  var totaldata = TotalsSheet();
  for (var i =0; i < totaldata.length; i++) {
    All.appendRow(totaldata[i]);
  } 
}