Skip to content

Instantly share code, notes, and snippets.

@Max-Makhrov
Last active Jun 9, 2021
Embed
What would you like to do?
Combine data from multiple sheets to 1
// combine data from all sheets in current file
function combineData() {
// CHANGE SHEET NAME ↓ /////////////////////////////
var sheetTo = 'combined';
////////////////////////////////////////////////////
var file = SpreadsheetApp.getActiveSpreadsheet();
var sheets = file.getSheets(); // get all the sheets
// set where we want to write the results
var outSheet = file.getSheetByName(sheetTo);
var outdata = [], sheet, sName, data = [];
var maxCols = 0;
for (i in sheets) { // loop across all the sheets
var sheet = sheets[i];
var sName = sheet.getName()
if (sName !== sheetTo) {
data = sheet.getDataRange().getValues();
if (maxCols < (data[0].length+1)) {
maxCols = (data[0].length + 1);
}
outdata = outdata.concat(addColumn_(data, sName));
}
}
var normData = fitColumns_(outdata, maxCols);
write2sheet_({data: normData, sheet: outSheet});
}
// [[a, a, a]] → [[a, a, a, NEW!]]
function addColumn_(data, value) {
var res = [], row = [];
for (var i = 0; i < data.length; i++) {
row = data[i];
row.push(value);
res.push(row);
}
return res;
}
// [[a, a, a], [a, a, a, a]] →
// [[a, a, a, ''], [a, a, a, a]]
function fitColumns_(data, numCols) {
var res = [];
var row, val;
for (var i = 0; i < data.length; i++) {
row = [];
for (var ii = 0; ii < numCols; ii++) {
val = data[i][ii] || '';
row.push(val);
}
res.push(row);
}
return res;
}
// common writeToSheet Task
//
// sets (add more to code if needed)
// sheet: {sheet}
// data: [[data]]
// row: N
// col: N
function write2sheet_(sets) {
var sheet = sets.sheet;
if (!sheet) { return -1; }
var data = sets.data;
if (!data) { return -2; }
var row = sets.row || 1;
var col = sets.col || 1;
var rows = data.length + row - 1;
if (!rows || rows < 1) { return -3 }
var r0 = data[0];
if (!r0) { return - 4; }
var cols = r0.length + col - 1;
if (!cols || cols < 1) { return -5; }
var r = sheet.getRange(row, col, rows, cols);
r.setValues(data);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment