Google Sheets - Combine All Sheets into One When Column Order Doesn't Match - Apps Script - Part 17
Vložit
- čas přidán 13. 02. 2018
- Learn how to combine multiple tabs to one master tab when you have random column order using Apps Script in Google Sheets.
Script: www.chicagocomputerclasses.co...
Hmmm... I am having troubles at minute 13:38. I use this script:
function myfunction(){
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for([i,sheet] in sheets){
console.log(sheet);
}
}
But when I check the logger, instead of getting "sheet, sheet, sheet, sheet" I get "undefined, undefined, undefined, undefined". If I use Logger.log instead of console.log I got null, null, null, null. I checked the script for missing commas or something and I was unable to find the problem. I also got the four sheets in the spreadsheet . Can you give me a hand about this please?
Due to engine update
for([i,sheet] in sheets){
should be
for (let [i, sheet] of Object.entries(sheets)) {
@@ExcelGoogleSheets
UPDATE: I've been typing sheets and not sheet to get whole time. My apologies. You are the GOAT of GAS. Keep the videos coming!
This seems to not work very well. I was having a hard time getting the values out especially since you have to use the `${value}` notation. I couldn't get it to a variable or anything. So i ended up using
sheets.ForEach(function(sheet){
logger.log(sheet.getSheetName())
})
where GAS let me add the ".getSheetName" to the variable this time.
Let me know what you thing.
Thanks!
This works too:
var sheets = SpreadsheetApp.getActive().getSheets();
for(k in sheets){
Logger.log(sheets[k].getSheetName());
};
@@davidlewis3539 Functional programming is always welcome. The procedure that you provide will be the one that I should use in real world programs.
@@javaejercicios4442 Nice!
I love how you teach without everything being over prepared. That style always looks like magic and can be a real confidence blow.
Watching you work this way, shows me how to build a solution step by step using Logger, to be patient, to expect problems to arise and how to deal with them. Awesome. Thank you.
Awesome! Can't wait for more videos like this. :D
Nice. Exactly what I needed.
hahaha. this is awesome. they way you get to your goal ("..... and I DO IT the "hacky-way" now at min 09.37) is
phenomenal. thx
Awesome and really helpful one! Would it be possible to execute your last words for improvements as I am facing the same issue. I have few other tabs also and I do not want to touch them. Please share the script with us. Also I have one question for example if the sheet does not have Region signal as per State signal, can you help me to create if statements and that can be used in master sheet to get states for all regions :) Cheers from India
Hi! Everything works perfectly with the update
"for([i,sheet] in sheets){
should be
for (let [i, sheet] of Object.entries(sheets)) {"
Thank you very much!
Can you PLEEEEAAASE make a version where I can ignore multiple sheets besides the "MasterSheet"?
This would be awesome!!
This video was extremely helpful! Thank you so much for posting. I am impressed (and envious) that you were able to build this script on the spot! I have to agree with Robert Evans to some degree. If this video were organized, planned and edited a little better, it would be easier to follow and probably shorter. Don't take this constructive criticism as a dig though. Do these things and I think you'll be on your way to having a premier youTube resource for all things G-Sheets.
@LearnGoogleSpreadsheeets did you ever make that improvement at 31:50?
Keep up the great work
is i in the for loop defined as index already whether i write it as [sheet,i] or [i,sheet] or does it follow [key, values] format sequence??..why did you declare i in the for loop that you didn't actually use there but in the forEach loop only?
Hi Thanks for this tutorial. The script seems to work, in that its not giving me an error. However, I am not being able to combine the date because the scripts seems to run out of time. Could I have too much data on hand? I am trying to combine data from a 100+ sheets that total close to 18,000 rows.
thank you very much....i have favor to ask you. i want to create column D where it will show its corresponding sheet name. can you help me out here?
Hi I have a question please help me. I have the below google script to movew cells after i put i word for instance once I say "ok" then the row will be moved into another sheet based on my confirmation as I have wrote "ok", but I want to add another word beside "ok", for instance I want to have two words "ok'' or "confimed'' to do the same thing, i have been working on it since two days but I can't execute this function; I don't any videos on youtube as well to show how to run different/ multiple google scripts in one sheet. below is the script just tell how can i add another word beside the ''ok''. Thanks in advance. Please replay me back ASAP. I really need to complete it by the end of this month.
/**
* Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 4.
*/
function onEdit(e) {
// see Sheet event objects docs
// developers.google.com/apps-script/guides/triggers/events#google_sheets_events
var ss = e.source;
var s = ss.getActiveSheet();
var r = e.range;
// to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;
var nameCol = 4;
// Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
// Get the number of columns in the active sheet.
// -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;
// if our action/status col is changed to no do stuff
if (e.value == "ok" && colIndex == actionCol) {
// get our target sheet name - in this example we are using the priority column
var targetSheet = s.getRange(rowIndex, nameCol).getValue();
// if the sheet exists do more stuff
if (ss.getSheetByName(targetSheet)) {
// set our target sheet and target range
var targetSheet = ss.getSheetByName(targetSheet);
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
// get our source range/row
var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
// new sheets says: 'Cannot cut from form data. Use copy instead.'
sourceRange.copyTo(targetRange);
// ..but we can still delete the row after
s.deleteRow(rowIndex);
// or you might want to keep but note move e.g. r.setValue("moved");
}
}
}
I was getting the error Cannot read property 'getSheetName' of undefined (15:55) and had to change to
for (var i = 0; i < sheets.length; i++) {
var sheetName = sheets[i].getSheetName(). Aparently for([i, sheet] in sheets) returns null
I used your logic but it still gives the same error. Any suggestions? Here's the code: function getCombinedColumnValues(label,masterSheetName) {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var colValues = [];
for (var i = 0; i < sheets.length; i++) {
var sheetName = sheets[i].getSheetName();
if(sheetName !== masterSheetName) {
var tempValues = getColumnValues(label,sheetName);
colValues = colValues.concat(tempValues);
}
}
@@mariamasood1761 replace for (var i = 0; i < sheets.length; i++) with for (let [i, sheet] of Object.entries(sheets))
@@TheTramos00 It still throws the same error. This is the sheet if you want to look at, please send request for access: docs.google.com/spreadsheets/d/1_tXgP7HK_4nuBVrGRUzhY9ezChP2liXEIYClMaSyar8/edit#gid=0
Dear sir how to select all column name range in one time with macro in many sheets plz solve my query
Here in this video we can see the spreadsheet is 1 with multiple sheet i want to combine different spreadsheet into 1 master spreadsheet i am having some different columns name and column orders also how can i do that is it possible ?
hello, how to Combine All Sheets into One When Column Order Doesn't Match but not using apps script? can you show me how to do? if using excel i can do with power query but i need to used google sheets. thx you so much
How I import a range from another spreadsheet using the app scripts?
Watch my recent web app videos, you'll see examples of that.
Hi sir I would like to use your script code for differen way
for example you have labels in mastersheet those labels contains similar values in state column from other sheet
and there is sales value that contains values that belongs for each states
how can i use your code to get vertical information from previous sheet that mathches horizontal label in mastersheet and pastes it one after one raw with button click
thank you in advance for your videos it is very usefull and interesting
var sheetName = sheet.getSheetName();
In this line, getSheetName() doesn't supports anymore. Can you help in that?
For some reason, I could not verify new projects any more as it says "Sign in with Google temporarily disabled for this app. Sign in with Google temporarily disabled for this app".
I have waited for nearly a month but still not resolved yet :-( Any idea?
Check if you have installed any addons that haven't been updated recently. If you find any try to delete them and their previous authorization.
I'm getting the following: Exception: The parameters (number,null,number,number) don't match the method signature for SpreadsheetApp.Sheet.getRange. (line 36, file "practice")
Line 36 has the following: var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
I feel like it has something to do with the method i'm using to loop through the tabs:
for(k in sheets){
var sheetName = sheets[k].getSheetName();
if (sheetName !== "Master"){
var tempValues = getColumnValues("ID", sheetName)
colValues = colValues.concat(tempValues);
}
}
I figured it out! My issue was with the if statement I believe. I have several tabs to include and exclude.
Part of my challenge is that I do not completely understand looping.
Below you'll find my code.
for(k in sheets){
var sheetName = sheets[k].getSheetName();
// the code below does not include BHC Peninsula because there is currently no data in it. That does not allow the code to work.
if ((sheetName == "Data BHC Padres Unidos")||(sheetName == "Data CCA")||(sheetName == "Data COPA")||(sheetName == "Data MujeresEnAccion")){
var tempValue = getColumnValue(label, sheetName)
colValues = colValues.concat(tempValue);
}
};
I'm getting an error that says, "The starting column of the range is too small. (line 46, file "Code")." I copied the code from your website and the error still pops up.
I'm getting the same error also
Check the partial sheets. There is at least one of the labels in the master sheet that is not found in a partial sheet. The current code is case-sensitive, so be aware of that. That means, if it doesn't find the exact label, the error comes up.
44 var colIndex = getColumnIndex(label,sheetName);
// if it doesn't find the exact match, getColumnIndex() returns -1, thus colIndex is -1
45 var numRows = ss.getLastRow() - 1;
46 var colValues = ss.getRange(2, colIndex, numRows, 1).getValues(); // then, the error tells it literally
Also, this code is not designed to either include or exclude sheets, it only include all sheets except the master sheet (line 31). That means if your spreadsheet happens to have Not Partial Sheets, they will be included in a loop (line 29), because it doesn't find a label in their first row it will complain with the same error (again, line 46).
To make the code more verbose.
1. Normalize the label,
56 let lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0].map(label=>label.toLowerCase());
57
58 let index = lookupRangeValues.indexOf(label.toLowerCase()) + 1;
2. or, Use regex with loop/filter instead of indexOf to get the index
58 let index = +Object.keys(lookupRangeValues).filter(key=>lookupRangeValues[key].match(new RegExp(label, "i"))) + 1; // using new Chrome V8
3. It is suggested that if you really have partials and non partials in the same spreadsheet, name the sheets according to some convention like "_Partial 1", "_partial2", and you can include sheets just the sheets that match "^_", like the folowing
31 if(sheetName !== masterSheetName && sheetName.match(/^_/) ) { // you could extract the regex to the top function
@@rivosuoth Thank you for the tip. YES. If you have other sheets with no label inside them, he give that error.+
@@rivosuoth Your code is worked! Solved the problem. Come on mannn, thanks a lot!
Thank you for your great content!
I am stuck at 22:28...
The function GetCombinedColumnValues worked just fine, but when I insert label and MasterSheetName and run it in the MainFunction, it only returns the data of one sheet. Any idea what went wrong?
function MainFunction() {
var ColValues = GetCombinedColumnValues("Date", "Master");
Logger.log(ColValues);
}
function GetCombinedColumnValues(label,MasterSheetName) {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var ColValues = [];
for (let [i, sheet] of Object.entries(sheets)) {
var sheetName = sheet.getSheetName();
if (sheetName !== MasterSheetName){
var tempValues = GetColumnValues(label, sheetName);
ColValues = ColValues.concat(tempValues);
return ColValues;
}
}
}
Thank you for your great work with this channel :)
try for (let [sheet,i] of Object.entries(sheets)) {
I have facing error - The JavaScript runtime exited unexpectedly.
Please help how I can resolve this issue.
Hello. I am getting an error that says "TypeError: Cannot read property 'getSheetName' of undefined (line 31, file "Code")". I copied this code from your website. the error still pops up. anybody can help?
for V8 engine change
for([i,sheet] in sheets){
to
for([i,sheet] of Object.entries(sheets)){
Awesome script! However, since V8 Runtime is introduced I get the following error: "Cannot read property 'getSheetName' of undefined"... Is there a way to fix this issue? Thanks in advance!
I would need more context than this.
@@ExcelGoogleSheets I just found a workaround on Stackoverflow :) statement "for ([i, sheet] in sheets)" does not work anymore in V8. A workaround for this is using "for (var i = 0; i < sheets.length; i++)"
@@joepvanschagen8164 that makes sense
@@ExcelGoogleSheets I am trying to find the video you refer to in the beginning of this video, but can't find it. Could you share a link? :)
Hi, I have similar problem as below, but with last function (combineData)
Instead syntax: labels.forEach(function(){}), I wrote this:
for (let [i, label] of Object.entries (labels)){
console.log(i + 1)
}
}
But when I'm trying to increase this "i" by 1, I'm getting 01, 02, 03 instead 1, 2, 3
Anybody can help me with this?
are you sure it's 01, 02, 03 and not something different
@@ExcelGoogleSheets Right! Console returns 01, 11, 21. It looks like text joining. If I write for example (i +10), I have 010, 110, 210
console.log(parseInt(i) + 1)
Unfortunatelly I'm stuck in minute 22. My formula gives the error: "The starting column of the range is too small." and says that the error is in this line: var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
Any idea??
Thanks
something wrong with colIndex
@@ExcelGoogleSheets Thanks, you right, this is the problem. Column with small data, like number of the month or year gives me the same error, but I need this data in my combined table. Can you help me how to fix this?
@Operations Management I had re-ordered the headings but not capitalized them. When I went back and added the headers with Caps, it worked perfectly once again. Not sure if that's your issue, but I was getting the same error before that, and now it works perfectly.
Mine was toward the end tho - like 36:00
Is there a way to do this using query instead of this script?
czcams.com/video/CVkyfGUSHeg/video.html
Please help. It's giving error: TypeError: Cannot read property 'getSheetName' of undefined
replace this code:
for([i,sheet] in sheets){
with this code:
for (let [i, sheet] of Object.entries(sheets)) {
Hey!
Thanks for the video. Unfortunatelly code seems to be not working. Copied it from web page and tried to run it, but got errors.
I'm also getting the same error
🙏🏻
Error : Exception: The starting column of the range is too small. Error at min 19:00
If you have other sheets with no label inside them, when if statement search it, , he give that error. So you must move the code in a new sheet with only sheets + that label and master. You must not have any other sheets with no labels on it. Thats why give this error
I recreated the code for another project and it doesn't work. I tested the first project and it still works. Any thoughts on why this might be? Here's a description of the problem: stackoverflow.com/questions/66575717/syntaxerror-unexpected-token-google-sheets-script-editor
Here's the error I'm getting now:
Exception: The starting column of the range is too small.
getColumnValue @ Copy 2 of Code.gs:59
getCombinedColumnValues @ Copy 2 of Code.gs:39
(anonymous) @ Copy 2 of Code.gs:16
combineData @ Copy 2 of Code.gs:15
Before I was getting this: Syntax error: SyntaxError: Unexpected token ')' line: 14 file: Copy of Code.gs
try changing this
for(k in sheets){
var sheetName = sheets[k].getSheetName();
to this
for(ws of sheets){
var sheetName = ws.getSheetName();
@@ExcelGoogleSheets, Unfortunately, it did not work.
@@kurls123 Sorry, you would need to troubleshoot and see what's causing this. It seems like you data in this spreadsheets might be in different format or layout.
@@ExcelGoogleSheets I not sure what the issue is. I ended up creating a "master" tab with a query: =QUERY({'CBDIO Data'!A2:AX;'CCA Data'!A2:AX;'CHW Data'!A2:AX;'Mujeres en Accion Data'!A2:AX;'Padres Unidos Data'!A2:AX}, "select * where Col1 is not null",0)
I wanted to able to create variables based on text in several columns so I used a different tab to do that. The query stops working if I add more columns. That was my workaround'
.
why show it null???
when I run
for([i,sheet] in sheets){
Logger.log(sheet);
}
and show me:
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info null
2:47:55 PM Info 0
2:47:55 PM Info 1
2:47:55 PM Info 2
2:47:55 PM Info 3
2:47:55 PM Info 4
2:47:55 PM Info 5
in other spreadsheet just two sheets to run it,
it also show null:
2:53:21 PM Info null
2:53:21 PM Info null
try for(let [i,sheet] in Object.entries(sheets)){
This syntax was changed in V8 engine.
Thank you very much!@@ExcelGoogleSheets
@@ExcelGoogleSheets Thank you very much!
Disorganized and unprepared. Next time, prepare a lesson plan and practice what you are about to teach.
Instead a bad critic, we'd appreciate a constructive example of your knowledges building such scripts doing the same jobs like macros. My suggestion to you, just leave the channel!
I gave you clear constructive criticism: prepare a lesson plan, practice what you are about to teach and I will add a third - edit your videos to cut out your blunders because you don't have to do it in one take. You have a chance to improve rather than be lazy. I have taught electrical engineering for 19 years and some days if I didn't have or make time to prepare, I sounded like you and tried to blunder my way through a lesson. Be professional.Your channel has a potential to be great. Watch some other CZcams channels like:
czcams.com/channels/u7_D0o48KbfhpEohoP7YSQ.htmlvideos
No worries, thanks for your input. :)
He's devoting time and resources to impart free knowledge to the public. Until you start doing the same with your electrical engineering lessons your criticism comes off as pretty entitled.
@@ReevansElectro “blunders” are the most precious, teaches us that we all make mistakes, he is not pretending to be someone that he is not. You do not have to like his way of teaching. There are many ways and certainly some people would like your way of teaching. I hope you learned it since your comment.
it show me:
Error
TypeError: Cannot read property 'getSheetName' of undefined
at this line: Logger.log(sheet.getSheetName());