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?
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.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}
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]);
}
}
