Here is the link with an example:
I want to combine the data from the "First","Second" and "Third" tab into the "Combined" tab all under the same Headers (Title,Type,Genre)
Please let me know if there is a script for this - as the actual doc I need to use it on has more than 30 tabs and a formula would potentially crash.
The following script uses forEach to iterate over the sheets,
excluding the ones mentioned in the exclude_sheets array.
For every sheet, it stores the values, between particular ranges that you need to setup in the beginning of the script, to the data array.
The range starts from the 6th row (start_row) between column A (start_col=1) and column C (end_col = 3). The end row is defined as the last row with content, so you don't need to manually set it up and it can be also different between the sheets. However, the starting row and column ranges must be the same between the sheets.
It finally pastes the data to the Test sheet (res_sheet = 'Test') starting from the cell A2.
Feel free to modify the parameters above but respect the underlying logic, otherwise the script won't work properly.
function myFunction() {
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
const exclude_sheets = ['Combined','Test'];
const res_sheet = 'Test';
const start_row = 6;
const start_col = 1;
const end_col = 3;
const data = []
sheets.forEach(sh=>{
if(!exclude_sheets.includes(sh.getName())){
let temp = sh.getRange(start_row,start_col,sh.getLastRow()-start_row+1,end_col).getValues()
data.push(...temp);
}});
ss.getSheetByName(res_sheet).getRange(2,1,data.length,data[0].length).setValues(data);
}
Please make sure that V8 runtime enviroment is enabled before you use this solution.
