Thank you. This is also a fundamental knowledge that a GAS dev must know. Try not to interact with GG Sheet (using GAS API) if you can. Instead, read all data in one time, process it in javascript then return the processed data to the GG Sheet.
Yet another great series! I learn something eye opening from each one. Please keep them coming, and thank you for so graciously sharing your knowledge and expertise.
In case anyone is reading this, even though it's years later, you don't want to switch back to watch what happens until you know for sure that the changes to the script are working without errors. The error notification disappears after a few seconds, so you might miss it if you're busy trying to see the magic happen on the sheet itself. Then, you'll have to run the faulty script again to see what went wrong... waste of time when you could have caught it the first time and saved yourself a double dose of disappointment by simply watching the script first as demonstrated in the video. It's a best practice.
Somehow I find it SO MUCH EASIER to deal with GAS/Sheets data as mapped arrays instead of the native nested ones. It's sad that Google waited so many years to updates GAS to ES6!
Thanks a lot for that tutorial with JS. BTW how we can do some more advanced math with arrays? For example, 2 or 3 numbers are divide by num of columns to get average, we can do that with for loop but that is not the case in the map method as I think
I believe for loop works fine. but for loop call the google sheet API every single time will slow down the script. Do you think set var of the spreadsheet range, do the mechanic on the var and setvalues at the end help ?
In the first example when I add new rows will the Profit be auto calculated or do I need to run the script each time? If the script has to be run each time then “Arrayformula” option would be better for simple functions like Profit.
Well, one thing you should certainly do is let the script calculate the number of rows in your range by using get last row method or a similar method instead of just hardcoding the number of rows like in the video, 4989 and such. Then you have few options - you can either create a button on the sheet itself and assign that script to run when you click it, or put an entire script inside an onEdit(e) function to let it run automatically whenever you edit the sheet
Hi, with the new version 8, editor & way to write the scripts are changed. I'd like to know if you plants to update some video concerning the way to write arrays methods. The VAR declaration method was substitute by CONST, arrays with callback are update with ARROWS method, method to write LOOPS is no more longer (for var i=0) but data.map(callback). Personally i still confuse which kind of array should i use to get the result i want, map, filter, indexOf, every, some, foreach... Do you think you plant a serie of videos where you maybe would answer questions from your followers with real example? This would be just for private and absolute NOT commercial porposes. I would really appreciate a kind of videos concerning update of the way to write array method and answer ours questions...I'm sure i'm not the only one would appreciate that. By the way, a very big thank you for you effort and for share your knowledgees, with all humans beans.
Nothing really changed, even though it may feel that way. It doesn't matter which way you use, those are really syntax differences that make no difference in the way your code runs. You can write your code the old way and it will work absolutely fine. If you're not sure, just use the old way, it's really that simple. Don't worry about using const or let, just use var. These things make no difference for simple scripts people write for small automation. I've covered all of these already (map, filter, indexOf, every, some, sort) and why you should or shouldn't use them here czcams.com/play/PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD.html
At the moment creating the same videos all over again just to write "const" instead of "var" or "(d)=>{}" instead of "function(d){}" doesn't seem to make a lot of sense, especially considering the fact that writing "function(d){}" works just fine, and there is absolutely no advantage in using "(d)=>{}" syntax instead, other than trying to look cool :)
Yes, that would be better if we assume we never have any issues with our data. Otherwise if we accidentally get 3 columns by splitting that would be a problem.
That's correct. My point is that if we get an array of 2 values in one row and then an array of 3 values in the other, then we will have to think how we will be outputting that result on the spreadsheet.
Great video again ! Using .map just made my day but I'm stuck because if have a custom function an i copy the code in the .map(function XXX) i got it working, but by calling it i end up with nulls Dataset is small so i could go with loop through the data in my sheet but I would love to get it and understand the good way. if anyone has ressources to point me to learn around this so I can go further i would be glad
Here's the thing, it's all a matter of preference. Much like all the stuff in Google sheets can be done using formulas, you can create a script that will make all the changes you want and create your own custom looking spreadsheet just by running the script, it's that flexible. I would probably make the script do what I want in case I need to work on the same spreadsheet with multiple people, just so I don't have to worry about protecting some ranges and not letting my coworkers fuck up the formulas. If I'm the only one working on a spreadsheet, I'll use formulas and arrayformulas for most of the stuff I want, and for more complicated stuff instead of trying to figure out a custom complex formula I'll create a script and set it to auto run.
Yes it would be easier for this one specific action(function) . But you'll be able to do way more crazy sh*#$t once you learn the basics of app scripts.
Does anyone know how to check to see if the cell is a formula or just data? I am trying to transfer data from one sheet to another, but if there is a formula, I want to copy paste the formula. Thanks!
getFormulas() returns blank for anything that's not a formula. You'll need to get an array with getFormulas() and then iterate through that array and replace all blanks with results from getValues()
Thank you. This is also a fundamental knowledge that a GAS dev must know. Try not to interact with GG Sheet (using GAS API) if you can. Instead, read all data in one time, process it in javascript then return the processed data to the GG Sheet.
Yet another great series! I learn something eye opening from each one. Please keep them coming, and thank you for so graciously sharing your knowledge and expertise.
one of the best and best app script tutorial
now this is what i missed as a newbie to app scripts, thx bro!
Awesome man, very clear illustration, thanks a lot
yes and yes and yes. Thank you so much!!
BTW, you can switch to the sheet, while running the code and watch what happens as the code runs
In case anyone is reading this, even though it's years later, you don't want to switch back to watch what happens until you know for sure that the changes to the script are working without errors. The error notification disappears after a few seconds, so you might miss it if you're busy trying to see the magic happen on the sheet itself. Then, you'll have to run the faulty script again to see what went wrong... waste of time when you could have caught it the first time and saved yourself a double dose of disappointment by simply watching the script first as demonstrated in the video. It's a best practice.
wonderful
Very useful, thanks a lot!
A nice demo, thank you. :-)
I like arryformula script, (let me use & check) 👌
Great!
Thank you very much !!!
Somehow I find it SO MUCH EASIER to deal with GAS/Sheets data as mapped arrays instead of the native nested ones. It's sad that Google waited so many years to updates GAS to ES6!
Interesting. I see no difference in that regard.
Thanks a lot for that tutorial with JS. BTW how we can do some more advanced math with arrays? For example, 2 or 3 numbers are divide by num of columns to get average, we can do that with for loop but that is not the case in the map method as I think
I believe for loop works fine. but for loop call the google sheet API every single time will slow down the script. Do you think set var of the spreadsheet range, do the mechanic on the var and setvalues at the end help ?
it is a great video btw .
Why not return just r if it is arleady an array?
In the first example when I add new rows will the Profit be auto calculated or do I need to run the script each time? If the script has to be run each time then “Arrayformula” option would be better for simple functions like Profit.
Well, one thing you should certainly do is let the script calculate the number of rows in your range by using get last row method or a similar method instead of just hardcoding the number of rows like in the video, 4989 and such. Then you have few options - you can either create a button on the sheet itself and assign that script to run when you click it, or put an entire script inside an onEdit(e) function to let it run automatically whenever you edit the sheet
🙏🏻
Hi, with the new version 8, editor & way to write the scripts are changed. I'd like to know if you plants to update some video concerning the way to write arrays methods. The VAR declaration method was substitute by CONST, arrays with callback are update with ARROWS method, method to write LOOPS is no more longer (for var i=0) but data.map(callback). Personally i still confuse which kind of array should i use to get the result i want, map, filter, indexOf, every, some, foreach... Do you think you plant a serie of videos where you maybe would answer questions from your followers with real example? This would be just for private and absolute NOT commercial porposes. I would really appreciate a kind of videos concerning update of the way to write array method and answer ours questions...I'm sure i'm not the only one would appreciate that. By the way, a very big thank you for you effort and for share your knowledgees, with all humans beans.
Nothing really changed, even though it may feel that way.
It doesn't matter which way you use, those are really syntax differences that make no difference in the way your code runs. You can write your code the old way and it will work absolutely fine. If you're not sure, just use the old way, it's really that simple. Don't worry about using const or let, just use var. These things make no difference for simple scripts people write for small automation.
I've covered all of these already (map, filter, indexOf, every, some, sort) and why you should or shouldn't use them here czcams.com/play/PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD.html
The problem with questions is that they are usually too broad, so impossible to answer because it depends on too many variables.
At the moment creating the same videos all over again just to write "const" instead of "var" or "(d)=>{}" instead of "function(d){}" doesn't seem to make a lot of sense, especially considering the fact that writing "function(d){}" works just fine, and there is absolutely no advantage in using "(d)=>{}" syntax instead, other than trying to look cool :)
I'll probably do a video to explain different function syntax possible in JavaScript.
Woah, so that's why..
return [ r[0].split(", ") ] is okay?
I think this takes more short time.
Yes, that would be better if we assume we never have any issues with our data. Otherwise if we accidentally get 3 columns by splitting that would be a problem.
No problem.
Although a cell value is "A,B,C", split method return a array with 3 elements.
CELL.split(",") RETURN 0=A>,1=B,2=C
That's correct. My point is that if we get an array of 2 values in one row and then an array of 3 values in the other, then we will have to think how we will be outputting that result on the spreadsheet.
Okay.
I understnd what you mean.
Thank you.
how to post 2dumentional array to sheet using api? Please!
Great video again ! Using .map just made my day but I'm stuck because if have a custom function an i copy the code in the .map(function XXX) i got it working, but by calling it i end up with nulls
Dataset is small so i could go with loop through the data in my sheet but I would love to get it and understand the good way. if anyone has ressources to point me to learn around this so I can go further i would be glad
Share your code.
Awesome! Is it faster to run these scripts compared to using normal formulas copied down (or arrayformulas in the first row)?
Not sure, probably the same.
Here's the thing, it's all a matter of preference. Much like all the stuff in Google sheets can be done using formulas, you can create a script that will make all the changes you want and create your own custom looking spreadsheet just by running the script, it's that flexible. I would probably make the script do what I want in case I need to work on the same spreadsheet with multiple people, just so I don't have to worry about protecting some ranges and not letting my coworkers fuck up the formulas. If I'm the only one working on a spreadsheet, I'll use formulas and arrayformulas for most of the stuff I want, and for more complicated stuff instead of trying to figure out a custom complex formula I'll create a script and set it to auto run.
This really cool but, is not easier just do “=E2-F2” and just copy this formula into the cells below? I mean, what’s the benefit to use code??
If you had not noticed, this video is about the use of JavaScript Arrays and not calculating with formula's...
Yes it would be easier for this one specific action(function) . But you'll be able to do way more crazy sh*#$t once you learn the basics of app scripts.
Could you share the sheet for everyone to practice? Thanks in advance :D
Really good video, but it's not running here. when I do "r[0].split" it says that split is not a function. It only shows "splice".
Here's my code
function arrayMapMethod(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var data = activeSheet.getRange(2,3,2,1).getValues().toString();
var results = data.map(function(r){return [r[0].split(", ")[0],r[0].split(", ")[1]];});
activeSheeet.getRange(2, 4,results.length, results[0].length,2).setValues(results);
}
The row and column are different because it's another dataset.
remove .toString() from here
var data = activeSheet.getRange(2,3,2,1).getValues().toString();
@@ExcelGoogleSheets Thanks!
Why don't you attach a link to the example file?
Does anyone know how to check to see if the cell is a formula or just data? I am trying to transfer data from one sheet to another, but if there is a formula, I want to copy paste the formula. Thanks!
You can use getFormulas() instead of getValues() and then use getValues() in spots when there are no formulas.
Thanks. But I want to know how to check the cell/array to see if it is a formula. I can't find the right script to check the cell. Any ideas?
getFormulas() returns blank for anything that's not a formula. You'll need to get an array with getFormulas() and then iterate through that array and replace all blanks with results from getValues()
You could also just copy/paste with script BTW, that way you won't have to deal with arrays at all.
@@ExcelGoogleSheets Thanks! I'll give this a try. I have thousands of rows so the copy/paste might not work.
If I hadn't watched this video I might never know. I don't suppose VBA has such limitations.
Same with VBA, it's just less noticeable.
Kind of dumb to reallocate memory for each iteration in the for loop?