-
-
Save matthewfinnell/c27dead514f53f1d6e17 to your computer and use it in GitHub Desktop.
| /* MergeAllSheets | |
| * | |
| * Takes a list of sheet names and add their data to a single sheet | |
| * Uses leftmost col. for names of the orginiating sheet of ea. row | |
| * If no args specified, takes all sheets except current - reports count | |
| * | |
| * Always assumes header row (doesn't take row 1 of anything) | |
| * | |
| * Usage: | |
| * =MergeAllSheets("D.C.","Illinois","Los Angeles","Massachusetts","Ohio","Seattle") | |
| * =MergeAllSheets() | |
| * | |
| * Matthew Finnell 2014 | |
| * | |
| * h/t to VMerge() by ahab facit 2010 | |
| * | |
| */ | |
| function MergeAllSheets() { | |
| var al=arguments.length; | |
| var sheets = []; | |
| var data = []; | |
| var nRow = []; | |
| var nGrid = []; | |
| var sheet, lastCol, lastRow, grid; | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| //populate our sheets array | |
| if (al < 1) { | |
| sheets = ss.getSheets(); | |
| sheets.pop(ss.getActiveSheet().getIndex()); | |
| } else { | |
| for( i=0 ; i<al ; i++){ | |
| sheet = ss.getSheetByName(arguments[i]); | |
| if (sheet != null) { | |
| sheets.push(sheet); | |
| } | |
| else { | |
| throw "The argument '"+arguments[i]+"' is not a valid sheet name"; | |
| } | |
| } | |
| } | |
| //take sheet array get data ranges and put in data array, add place names | |
| for (i=0; i<sheets.length; i++) { | |
| lastRow = sheets[i].getLastRow(); | |
| lastCol = sheets[i].getLastColumn(); | |
| grid = sheets[i].getRange(2,1,lastRow-1,lastCol).getValues(); | |
| for (j=0; j<grid.length; j++) { | |
| for (k=0; k<grid[j].length; k++){ | |
| if(k==0) { | |
| nRow.push(sheets[i].getName()); | |
| } | |
| nRow.push(grid[j][k]); | |
| } | |
| data.push(nRow); | |
| nRow = []; | |
| } | |
| } | |
| return data; | |
| } |
Hey, I know this is probably an old script now, I'm sort of learning as I go. Is it possible to adapt this to just extract say 5 tabs out of a multitab sheet and archive it off into one sheet to store on a google drive folder?
I've figured out how to export out each one individually I just now need to combine them on export into one
Hey, I know this is probably an old script now, I'm sort of learning as I go. Is it possible to adapt this to just extract say 5 tabs out of a multitab sheet and archive it off into one sheet to store on a google drive folder?
I've figured out how to export out each one individually I just now need to combine them on export into one
Hmm - honestly, it's been such a long time since I looked at or used this, I'm not even sure that it's compliant with the current way that Google Scripts works, unfortunately. As I recall, and my comment makes wrong, it does take tabs and not sheets per se. Not sure about the folder would need some modification.
Hey, I'm quite a beginner. Would you help me understand how to make it work?
I have an example google sheet "VMergeTest" with 3 Sheets:
Is this script meant to show the followoing result in sheet "Merge":
Row1: A1:B4 of "1"
Row2: A1:B4 of "2"?
If yes, what do I have to adapt in order to make it work?
I get a failure Wrong koordinates in row 47.
Thank you!