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.

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.

Awesome man, very clear illustration, thanks a lot

A nice demo, thank you. :-)

now this is what i missed as a newbie to app scripts, thx bro!

Very useful, thanks a lot!

Thank you very much !!!

Great!

yes and yes and yes. Thank you so much!!

one of the best and best app script tutorial

wonderful

BTW, you can switch to the sheet, while running the code and watch what happens as the code runs

@tarawiselove

2 ай бұрын

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.

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

🙏🏻

Woah, so that's why..

I like arryformula script, (let me use & check) 👌

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

@ExcelGoogleSheets

4 жыл бұрын

Share your code.

Could you share the sheet for everyone to practice? Thanks in advance :D

Awesome! Is it faster to run these scripts compared to using normal formulas copied down (or arrayformulas in the first row)?

@ExcelGoogleSheets

5 жыл бұрын

Not sure, probably the same.

@gabikralj94

Жыл бұрын

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.

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.

@gabikralj94

Жыл бұрын

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

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 ?

@kwanfong7291

2 жыл бұрын

it is a great video btw .

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!

@ExcelGoogleSheets

4 жыл бұрын

Interesting. I see no difference in that regard.

how to post 2dumentional array to sheet using api? Please!

Why not return just r if it is arleady an array?

return [ r[0].split(", ") ] is okay? I think this takes more short time.

@ExcelGoogleSheets

5 жыл бұрын

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.

@5953kim

5 жыл бұрын

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

@ExcelGoogleSheets

5 жыл бұрын

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.

@5953kim

5 жыл бұрын

Okay. I understnd what you mean. Thank you.

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.

@ExcelGoogleSheets

3 жыл бұрын

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 kzread.info/head/PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD

@ExcelGoogleSheets

3 жыл бұрын

The problem with questions is that they are usually too broad, so impossible to answer because it depends on too many variables.

@ExcelGoogleSheets

3 жыл бұрын

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 :)

@ExcelGoogleSheets

3 жыл бұрын

I'll probably do a video to explain different function syntax possible in JavaScript.

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??

@johanvandervorst

5 жыл бұрын

If you had not noticed, this video is about the use of JavaScript Arrays and not calculating with formula's...

@lb4082

5 жыл бұрын

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.

Why don't you attach a link to the example file?

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".

@walterpaiva719

4 жыл бұрын

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.

@ExcelGoogleSheets

4 жыл бұрын

remove .toString() from here var data = activeSheet.getRange(2,3,2,1).getValues().toString();

@walterpaiva719

4 жыл бұрын

@@ExcelGoogleSheets Thanks!

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!

@ExcelGoogleSheets

4 жыл бұрын

You can use getFormulas() instead of getValues() and then use getValues() in spots when there are no formulas.

@robertdonato5444

4 жыл бұрын

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?

@ExcelGoogleSheets

4 жыл бұрын

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()

@ExcelGoogleSheets

4 жыл бұрын

You could also just copy/paste with script BTW, that way you won't have to deal with arrays at all.

@bdonato11

4 жыл бұрын

@@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.

@ExcelGoogleSheets

3 жыл бұрын

Same with VBA, it's just less noticeable.

Kind of dumb to reallocate memory for each iteration in the for loop?