huge thanks for the very useful video.though i got a bit of anxiety here regarding how there was a typo in caps in the center of screen the whole time but you didn't correct it. i assume you didn't want it to interrupt the recording but i'm curious too. did you see the typo?
Brilliant video. Very well explained. Thank you so much! How do you write a function to color a column in, say green, based on the values of another column. E.g. if col. E contains PAID, then G should color green. Is that possible? I've tried so many things, none worked.
This video is amazing as all other videos! But i am curious if i have to achieve absolute reference in script like (=$A$2+$B$2*C3) instead of (=A2+B2*C2) how do i do that? I am unable to do that.. Any help please
Hi, I have been trying to replicate this UDF that you showed us here. But the function doesn't load up, it says "your dont have permission to call Spreadsheet.openbyid" how do I get around it? Thank you.
Nice, but how do you do it from the android app for Sheets? From Android, you can't access the web interface to Sheets .. it just throws you back into the android app if you try.
Hello, thank you so much for this videos. I'm really enjoying and appreciating them! Now I'm curious about APPS Scripts and tried to do a Function that cleans a range (this video ideas + the previous video), but in the last line says that the range gives an error. It must be something stupid but I don't find it. The idea is that we select the range when we write =cleanHere("here_we_select_the_range"). A small push? /** * This should clean the range that we introduce * * @param x The range that must be cleaned * @customfunction */ function cleanHere(x) { var zone = x; var app = SpreadsheetApp; var activeSheet = app.getActiveSpreadsheet().getActiveSheet(); activeSheet.getRange(zone).clearContent(); } Thanks in advanced!!!
1. You can run a script to clear contents, but your custom function won't have permissions to clear the cells from the user side. 2. When you pass a range to a functions it's treated as JavaScript array, not a range. You can pass the range as string though cleanHere("A1:D6"), but it still won't work because of the reason mentioned in 1.
I see... Well, I tried ;) Thank you very much for your answer! Btw, your way of explaining things is really good. You also inspire me to go always further. I'm glad you keep sharing your knowledge with the world! :) Thanks!!! :)
This is how I have it coded but I'm not sure if this how it would be done hopefully my logic makes sense lol: /** * calculates the roi by taking arg1-arg2/arg2 * * @param arg1 the number subtracted by arg2 * @param arg2 will divide by the result of arg1 - arg2 * @customfunction */ function roi(arg1,arg2) { var result=arg1-arg2/arg2
getActiveSheet was only working on the 1st sheet for me. Didn't work when i selected another sheet but did work when i moved the sheet2 or 3 to be the 1st sheet in the book. code was the same as in the video. Any one have any ideas on this? Although i will prob steer clear of using activesheet as it would lead to issue. Naming the sheet you want code to be executed on is much more targeted
getActiveSheet gets the sheet bound to the script, not the sheet open in the browser. The script is bound to the spreadsheet where you did tools->script editor.
isn't it useless and weird why videos on this topic made their example so simple that is far from any use case scenario . I don't need to go through all that to multiply and add 2 numbers. how do you do square roots??, powers??, trig functions etc.. make your examples a bit useful for Gods sake!!!
You are god. Perfect for intermediate programmers!
He is not a god but a sperm
I'm so glad I found your tutorials. You are an amazing teacher! Thank you.
Keep on making these videos, super helpful!
JSDoc is what you are looking for. Good videos. Thanks.
Yes!!
U are the google sheets god
perfect tutorials!!! thank you!
Am I really gonna trust a guy who “hopefully” spelled multiply right… yes
great!
🙏🏻
Very informative. 👍 nicely explained
Thanks a lot 😊
I want to use inbuilt functions like Date(), Year(), Rows(), Average etc. in the script. How I can do the same?
You can't use those within a script.
huge thanks for the very useful video.though i got a bit of anxiety here regarding how there was a typo in caps in the center of screen the whole time but you didn't correct it. i assume you didn't want it to interrupt the recording but i'm curious too. did you see the typo?
Thanks
thanks you so much
hiii what do i do for use custom formula with arrayformula..And thank you for your videos... really helpful ...
mutltiply
its been bugging me the entire video
very helpful!!!
Is it possible to call native functions like LEN LEFT RIGHT MID in a custom function?
No.
All of those are available in JavaScript under different names though.
Brilliant video. Very well explained. Thank you so much!
How do you write a function to color a column in, say green, based on the values of another column. E.g. if col. E contains PAID, then G should color green. Is that possible? I've tried so many things, none worked.
You don't need a function for this. Watch my "conditional formatting" videos.
This video is amazing as all other videos!
But i am curious if i have to achieve absolute reference in script like (=$A$2+$B$2*C3) instead of (=A2+B2*C2) how do i do that? I am unable to do that.. Any help please
I don't really understand what you mean. Please share an example code you've tried.
Hi, I have been trying to replicate this UDF that you showed us here. But the function doesn't load up, it says "your dont have permission to call Spreadsheet.openbyid" how do I get around it? Thank you.
You can't. UDFs can't refer to other spreadhsheet in the code. Instead you should use IMPORTRANGE function to provide the data to your UDF.
Appreciate your feedback. I have learned a lot from your vidoes. Infact for anything Google sheets I refer to your channel. 👌👌👌
Nice, but how do you do it from the android app for Sheets? From Android, you can't access the web interface to Sheets .. it just throws you back into the android app if you try.
Turn on desktop mode under the three dots menu
Can you add css into sheets, such as image-rendering?
That "mutltiply" really triggered my ocd.
Hello, thank you so much for this videos. I'm really enjoying and appreciating them!
Now I'm curious about APPS Scripts and tried to do a Function that cleans a range (this video ideas + the previous video), but in the last line says that the range gives an error. It must be something stupid but I don't find it. The idea is that we select the range when we write =cleanHere("here_we_select_the_range"). A small push?
/**
* This should clean the range that we introduce
*
* @param x The range that must be cleaned
* @customfunction
*/
function cleanHere(x) {
var zone = x;
var app = SpreadsheetApp;
var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
activeSheet.getRange(zone).clearContent();
}
Thanks in advanced!!!
1. You can run a script to clear contents, but your custom function won't have permissions to clear the cells from the user side.
2. When you pass a range to a functions it's treated as JavaScript array, not a range. You can pass the range as string though cleanHere("A1:D6"), but it still won't work because of the reason mentioned in 1.
I see... Well, I tried ;) Thank you very much for your answer!
Btw, your way of explaining things is really good. You also inspire me to go always further. I'm glad you keep sharing your knowledge with the world! :) Thanks!!! :)
can we fetch a range of cell data as an argument?
how would i go about executing this: result=arg1-arg2/arg2?
This is how I have it coded but I'm not sure if this how it would be done hopefully my logic makes sense lol:
/**
* calculates the roi by taking arg1-arg2/arg2
*
* @param arg1 the number subtracted by arg2
* @param arg2 will divide by the result of arg1 - arg2
* @customfunction
*/
function roi(arg1,arg2) {
var result=arg1-arg2/arg2
return result;
}
looks right to me.
then in spreadsheet =roi(A1,A2)
wizardry
How would you deploy the function as an add-on?
Here is an example github.com/googleworkspace/apps-script-samples/tree/master/sheets/dateAddAndSubtract
@@ExcelGoogleSheets are you planning on doing a video about this? I think it would help a lot of people...
@@thomasprevarin8992 No plans in the near future.
getActiveSheet was only working on the 1st sheet for me. Didn't work when i selected another sheet but did work when i moved the sheet2 or 3 to be the 1st sheet in the book.
code was the same as in the video.
Any one have any ideas on this?
Although i will prob steer clear of using activesheet as it would lead to issue. Naming the sheet you want code to be executed on is much more targeted
getActiveSheet gets the sheet bound to the script, not the sheet open in the browser. The script is bound to the spreadsheet where you did tools->script editor.
If =MULTIPL2NUMBERS(B2, B3) isn't working try: =MULTIPL2NUMBERS(B10;B11).
Had this exact same problem. Couldn't write other than B2,B3 in script, but when in sheet it did work only with B2;B3. Strange.
this doesn't work anymore (in year 2021) Google has changed how you do things...
Yes it does.
isn't it useless and weird why videos on this topic made their example so simple that is far from any use case scenario . I don't need to go through all that to multiply and add 2 numbers. how do you do square roots??, powers??, trig functions etc.. make your examples a bit useful for Gods sake!!!