Excellent, The past month I have been working in something like that but using Query's (a lot of querys), this is more elegant and simple. thank you for the tutorials.
This is great, thanks for sharing, the tutorial has been super useful. I've been trying to adapt it to take data from multiple ranges (within the same sheet), however can't seem to get it to work. Do you know if there's a simple way to adapt the script to make it possible? thank you. 🙏
Love this! Does the script function as more rows and/or columns are added to the source tab? Or in other words, is there a way to include logic that the array range is the last row with data and rightmost column with a header?
This solves a lot of problem in converting data from erp. Thanks you so much for this video. Now, one of my data has multiple fix columns and multiple rows too. Anything to handle that?
When I add new columns to my table, I want it still automatically run and work with this script. How I can change setRange() to solve this case. Thank your helpping
Thank you very much. In the other hand, If I have some "List " need to do the Crosstab how can I do? I will be looking forward to seeing your next video.
Thank you for your answer. Actually, I need to code on Apps script to run an operation from List to Crosstab like a pivot table automatically or Should I use a Macro?
Since for loops tend to become slower in big datasets, is there another way of doing unpivoting? Will .map() or .forEach() or other array methods work or be even better than using a for loop?
A Question: ss.getRange("A3:D3").activate() ss.getRange("""A"i":D"i""").activate() If instead the number 3, I want to use a variable, how can I do it? I tried more possibility, aber it's not working. Thanks for your great Videos!!
So basically I can add another argument in the push method, I have tried it, it works but is there a way to say from column 0 to 7 instead of adding 8 arguments ?
function crossTabToList() { var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CrossTab'); var rs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Results'); var startRow = 2; var startCol = 1; var dataArray = ss.getRange('A1:E8').getValues(); var resultsArray = []; for (var i = 1; i < dataArray.length; i++) { for (var j = 1; j < dataArray[0].length; j++) { if (dataArray[i][j] != '') { resultsArray.push([dataArray[i][0], dataArray[0][j], dataArray[i][j]]); } // end if } // end small loop } // end big for loop //Logger.log(resultsArray); rs.getRange(startRow, startCol, resultsArray.length, resultsArray[0].length).setValues(resultsArray); } // end function
Excellent, The past month I have been working in something like that but using Query's (a lot of querys), this is more elegant and simple. thank you for the tutorials.
Great video. Well explained and useful application. Keep 'em coming!
This is great, thanks for sharing, the tutorial has been super useful. I've been trying to adapt it to take data from multiple ranges (within the same sheet), however can't seem to get it to work. Do you know if there's a simple way to adapt the script to make it possible? thank you. 🙏
Love this! Can you make more videos on data cleaning using app script or google colab? Thanks!
Great tutorial; used it to clean some survey data!
👍
Love this! Does the script function as more rows and/or columns are added to the source tab? Or in other words, is there a way to include logic that the array range is the last row with data and rightmost column with a header?
This solves a lot of problem in converting data from erp. Thanks you so much for this video. Now, one of my data has multiple fix columns and multiple rows too. Anything to handle that?
When I add new columns to my table, I want it still automatically run and work with this script. How I can change setRange() to solve this case. Thank your helpping
var dataArray = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();
Thank you very much. In the other hand, If I have some "List " need to do the Crosstab how can I do? I will be looking forward to seeing your next video.
Use a pivot table.
Thank you for your answer. Actually, I need to code on Apps script to run an operation from List to Crosstab like a pivot table automatically or Should I use a Macro?
Probably just use a Macro if not comfortable writing code.
Since for loops tend to become slower in big datasets, is there another way of doing unpivoting? Will .map() or .forEach() or other array methods work or be even better than using a for loop?
forEach or map are for loops. So long as you're using an array and not individual values, there should be no speed difference.
A Question:
ss.getRange("A3:D3").activate()
ss.getRange("""A"i":D"i""").activate()
If instead the number 3, I want to use a variable, how can I do it? I tried more possibility, aber it's not working. Thanks for your great Videos!!
the correct way is ss.getRange(i,1,1,4).activate()
incorrect way that will still work is ss.getRange("A" + i + ":D" + i).activate()
Great!! Thank you very much!!! You know, what you do. That's awesome.
Sir, Great and useful
how Can I import() last row data from one crosstab spread sheet to other sheet, (by means of difference sheet), please advise
Great video!!! Thank you very much!!
Excellent
What do I need to do if for example I have the first name and the last in 2 different columns? How to keep part of the table "not-unpivoted"
So basically I can add another argument in the push method, I have tried it, it works but is there a way to say from column 0 to 7 instead of adding 8 arguments ?
would you mind sharing this script in a google sheet?
awesome! Is there anywhere i can copy this code from?
function crossTabToList() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CrossTab');
var rs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Results');
var startRow = 2;
var startCol = 1;
var dataArray = ss.getRange('A1:E8').getValues();
var resultsArray = [];
for (var i = 1; i < dataArray.length; i++) {
for (var j = 1; j < dataArray[0].length; j++) {
if (dataArray[i][j] != '') {
resultsArray.push([dataArray[i][0], dataArray[0][j], dataArray[i][j]]);
} // end if
} // end small loop
} // end big for loop
//Logger.log(resultsArray);
rs.getRange(startRow, startCol, resultsArray.length, resultsArray[0].length).setValues(resultsArray);
} // end function
i love javascript but pandas makes it easy to unpivot/melt