My god I've been searching for this fo soooo long. It works perfectly well on my phone, and will help all of my coworkers. Thank you so much for sharing this !!!
@@practicalsheets I want to ask about how to make a clickbox with onEdit that can add up (eg. 100+ 200) between 2 columns, and place the result in the colomn..thank you very much
@@mrdeerz4195 In this example the checkbox would be in col C, and it would add values in col A and col B, and paste the result in col D function onEdit() { let activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell() let column = activeCell.getColumn() let sheetName = activeCell.getSheet().getName() let activeValue = activeCell.getValue() if (column == 3 && sheetName == "Counter" && activeValue == true) { activeCell.offset(0,1).setValue(activeCell.offset(0,-2).getValue()+activeCell.offset(0,-1).getValue()) activeCell.setValue(false) } } Hope it is useful
Thank you very much @practicalsheets ... Im sory if im wrong.. But what i mean is .. How to add up / sum (eg. My clickbox in C3.. i want to add C5 number + D3 number and paste the result in D3 too..) pls help me.. 🙏🏻
You cannot trigger the event via the button on a mobile device! That is why he proposed the workaround with the onEdit(e) function which works perfectly.
Amazing. I am not an expert, I am looking forward to applying this to a button to redirect me to a different sheet inside the same worksheet. How can we do that?
This video was great and easy to follow along. Much appreciated actually talking through the meaning of the commands used also! This seems like the ideal fix for my situation , but I was unable to get it to work in the last step (with the checkbox). My script runs within the basic onEdit function, but when I try to tie it to the specific trigger, it no longer executes (nothing happens). I've compared my code to the video's 5-6 times and don't see any differences. Thoughts?
NEED HELP..! Thanks for this awesome function. But It works perfect when i have only one button.. In my case I have two buttons and i have created 2 separate functions and assign each Checkbox a separate function. But when i click the Button A, Nothing happens and When i Click the Button B it works perfectly. Please answer the question. Waiting for your reply
Hello! Instead of onEdit, call your function any other thing. Let´s say function1 Then the other function will be function2 Finally, include both functions in your onEdit like this onEdit(){ function1() function2() } Hope it helps!
Dear sir the check box u explained became a tick box and using that I did bit by bit the same, bt it's not at all working on mobile. Is there a change I need to do?
Hi, totally new to scripts and able to get the first working fine. I have 7 check boxes I needed to add under function onEdit. I've referenced the check box cell in each case. I still can't get it to work. Again, very new to script. In a Mac, trying to make work on an Iphone. Help?
Thanks for this, I have attempted to utilize this in my script but I have not had any luck getting it to work. I am fairly new to the scripting and I am not sure how to go about troubleshooting, I have copied your script exactly other than the counter. any recommendations would be great.
I was able to apply your workaround (thanks). But in my case I have multiple cells throughout my spreadsheet that I would like to increment on demand by clicking on a single checkbox (cell B4 in your example). When B4 is checked, rather than adding 1 to cell B2 (in your example), I want the logic to increment the one cell that had focus right before I clicked on the checkbox in cell B4. Do you know of a way to do that? Thanks again
Mmmm Very interesting I´m not sure how to do it There is a trigger onSelectionChange Maybe we could try that but it may make the sheet really "heavy" Kind Regards
How can I add multiple buttons to the same sheet like this? I've tried but something is interfering to where the last one in the script code is the only one that's actually functioning.
You probably need an else if for every other button, where you check if a different combination of sheet name/cell is active, and then you run a different function inside the else if
You probably need an else if for every other button, where you check if a different combination of sheet name/cell is active, and then you run a different function inside the else if
Fantastic video! I was able to get it to work great on my computer (browser) but alas it's not working on my phone or tablet. Any thoughts? I did swap counter for my own function but it worked on PC no issue there. And if I hit "run" from the scripts page it also worked. 0.o
I only get one script to work at a time, I have no clue to why this happens. I want to have several different buttons that clear different seachbars/ranges. and hope to have a on edit timestamp as well. Do you have any idea to why only one of my functions is functional by any chance? Or where I can find out?
Thank you so much for your video. It didnt work for me. I'm not sure why. My desktop buttons work but I cannot get the script to work for the edit you made. Any thoughts? I would love to share with you the code I am using. I cannot find any information on how to get it to work any where else. Your video was the closest thing I've found.
I also have the same issue, the checkbox worked with the counter function, but then when I use my own function it doesn't work, even though the button (and keyboard shortcut) both work
Hi, thanks for this tutorial it's very helpful, I managed to copy it so that my counter function worked on my tablet, however when I replace counter with my own function in the script, it no longer runs... but that function works fine when placed in a button or as a keyboard shortcut. Can you think of any limitations on why the onEdit function won't run another function? Would it help if I shared the code for that function with you?
@@jonnybolton4659 Hi, I was experiencing the same issue. I had the exact same code as the video but when I wrote my own function it didn't work. I had my cell A1 notation written between single quotation marks like this 'I4', I was so frustrated that just to try I changed them for double quotation marks because I used those in my own function and it worked that way. So my advice is that you check that you are using the same quotation marks (either single or double) in both your original function and your onEdit one. I really hope you manage to make it work. Good luck
Thanks. That wasn't the issue though it was because my function was trying to access other files in the Drive. The onEdit trigger doesn't allow this to happen but you can do this with "Installable triggers", I spent all afternoon going down that rabbit hole!
Hello! I´m glad you figured it out on your own, and apollogies for not getting back earlier. You are right There are 2 kinds of triggers in GAS: manual and installable manual are the functions you write such as onEdit or onOpen These are great because they remain in the file if you make any copies However their big limitation is that they cannot connect to services (They can not send an email, creat an event, or connect to another spreadsheet) This is when the second type of triggers come by. These are called installable triggers. As its name suggests, you need to install them on the "Triggers" menu in your GAS Editor. When you install them or create them they will sak for the permission, thus you won´t need it again The bad part is that they only exist on that file, that means that if you ever do a copy you need to be careful because the triggers will be erased and you need to create them again Hope I shed an additional light Regards!
@@practicalsheets thanks so much! One thing I did notice was when running the sheet in two different accounts, it always accessed the _owners_ files and not the _users_ do you know of a simple fix for this?
For this type of function you need to have an "installable" trigger onEdit wont work because it needs to acces the Spreadsheet service These are the steps 1. Change the name of onEdit function to any name you want, for example function thingsToDoOnEdit(){} 2. In the GAS Editor, at your left go to "Triggers" (A clock icon) 3. CLick the "Add trigger" button 4. Choose your function 5. On "type of event" choose "On edit" 6. Click save 7. enjoy Let me know if it works Regards!
Hi I have used app script for buttons...which can be used for operations from google sheet app in android mobiles. but I came to conclusion that unless we keep open that sheet in web version in any browser any app script doesn't run... that mean app script needs at least one web view login through any browser to be in function....
I don't know what is wrong with this. Within the App Scripts It works. BUT when I click on the button nothing happens. Made just the first function is working?
@@practicalsheets REALLY very grateful for your help and knowledge. Here's what I have If copies and pastes to last row and then clears the data. function NRunCheckBox() {
const ss = SpreadsheetApp.getActiveSpreadsheet(); //Collect the Players const sourceSheet = ss.getSheetByName("Game"); const sourceRange = sourceSheet.getRange ('B2:I2'); const sourceVals = sourceRange.getValues().flat(); const sourceSheet2 = ss.getSheetByName("Game"); const sourceRange2 = sourceSheet2.getRange ('B12:I12'); const sourceVals2 = sourceRange2.getValues().flat();//added // Append the data const destinationSheet = ss.getSheetByName("History"); destinationSheet.appendRow(sourceVals); const destinationSheet2 = ss.getSheetByName("History"); destinationSheet2.appendRow(sourceVals2); var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('B2:I11').activate(); spreadsheet.setCurrentCell(spreadsheet.getRange('I11')); spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true}); }; function onEdit () { let activeCell = SpreadsheetApp.getActiveSpreadsheet ().getActiveCell() let reference = activeCell.getA1Notation() let sheetName = activeCell.getSheet().getName() let activeValue = activeCell.getValue() if (reference == "D20" && sheetName == "Game" && activeValue == true) { NRunCheckBox() ; activeCell. setValue(false); } }
@@practicalsheets I found something interesting. So the script works with the button but not the checkbox. The checkbox is actually executing the previous script I had. Even though I gave the button a different name and am using the same name as the button in the onedit statement.
I GOT IT WORKING!! Yay thank you again. I had saved it as a macro and that was the problem. Changed it to a script. Now all I need is to get the date to append to the data range line.
I feel like it's rare that I am trying to do something and I find the exact solution spelled out exactly how I wanted it to work. Thanks!!
What a kind comment. Thank you so much!
My god I've been searching for this fo soooo long. It works perfectly well on my phone, and will help all of my coworkers. Thank you so much for sharing this !!!
Thank you very much. Excelent. Working on 100%.
Thanks for commenting! Glad it's useful
Kind Regards
automatic subs and like for you.. the best guide ever, thank you very much..
Thank you for the kind words!
@@practicalsheets I want to ask about how to make a clickbox with onEdit that can add up (eg. 100+ 200) between 2 columns, and place the result in the colomn..thank you very much
@@mrdeerz4195 In this example the checkbox would be in col C, and it would add values in col A and col B, and paste the result in col D
function onEdit() {
let activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell()
let column = activeCell.getColumn()
let sheetName = activeCell.getSheet().getName()
let activeValue = activeCell.getValue()
if (column == 3 && sheetName == "Counter" && activeValue == true) {
activeCell.offset(0,1).setValue(activeCell.offset(0,-2).getValue()+activeCell.offset(0,-1).getValue())
activeCell.setValue(false)
}
}
Hope it is useful
Thank you very much @practicalsheets ...
Im sory if im wrong.. But what i mean is ..
How to add up / sum (eg. My clickbox in C3.. i want to add C5 number + D3 number and paste the result in D3 too..)
pls help me.. 🙏🏻
@@practicalsheets pls help me ..🙏🏻
Great one, super helpful. I tried to make the button work in mobile but met a dead end. Amazing bro🎉
Thank you!
Any specific error?
You cannot trigger the event via the button on a mobile device! That is why he proposed the workaround with the onEdit(e) function which works perfectly.
Thank you so much! Excelent information and explanation. Many thanks...
Thank you!
Useful to know that there is a relatively simple workaround.
Thanks.
Thanks for the comment!
Simple and fantastic. Thank you
Thank you so much!
Regards!
Very usefull, thx. Is it possible to use this way for more check boxes and every check box should counting different cell?
Amazing. I am not an expert, I am looking forward to applying this to a button to redirect me to a different sheet inside the same worksheet. How can we do that?
Actually, you can do this without having to code.
I realize I don´t have a video on this. I´ll get into it!
Kind Regards
This video was great and easy to follow along. Much appreciated actually talking through the meaning of the commands used also!
This seems like the ideal fix for my situation , but I was unable to get it to work in the last step (with the checkbox). My script runs within the basic onEdit function, but when I try to tie it to the specific trigger, it no longer executes (nothing happens). I've compared my code to the video's 5-6 times and don't see any differences. Thoughts?
Please let me see your code to see if we can find the mistake
Regards!
Very clever workaround, thank you
Thanks for commenting!
thank you so much this works so well!
Thank you for commenting!
Regards!
Gracias, me sacaste de un apuro,....
Excelente!
Thanks, very usefull.
I will try.
Excellent!
Let me know if it works
Kind Regards
Lovely solution, and very easy-to-follow tutorial. Thank you! :D
NEED HELP..!
Thanks for this awesome function. But It works perfect when i have only one button..
In my case I have two buttons and i have created 2 separate functions and assign each Checkbox a separate function. But when i click the Button A, Nothing happens and When i Click the Button B it works perfectly.
Please answer the question. Waiting for your reply
Hello!
Instead of onEdit, call your function any other thing. Let´s say function1
Then the other function will be function2
Finally, include both functions in your onEdit like this
onEdit(){
function1()
function2()
}
Hope it helps!
This has been SO helpfull !! Thank you so much for the video
NEW SUB !
Dear sir the check box u explained became a tick box and using that I did bit by bit the same, bt it's not at all working on mobile.
Is there a change I need to do?
If you want, you could share the code
Kind Regards
It's very helpful. Thank you very much.
Very helpful. Thank you!
Thank you!
Hi, totally new to scripts and able to get the first working fine. I have 7 check boxes I needed to add under function onEdit. I've referenced the check box cell in each case. I still can't get it to work. Again, very new to script. In a Mac, trying to make work on an Iphone. Help?
Please let me see your code, to see where your mistake may be
Kind Regards
That was incredible! Thank you!
Thank you for commenting!
Brilliant - just what I needed - thank you
Thanks for this, I have attempted to utilize this in my script but I have not had any luck getting it to work. I am fairly new to the scripting and I am not sure how to go about troubleshooting, I have copied your script exactly other than the counter. any recommendations would be great.
Hello!
Were you able to solve it?
Regards!
Hello, if I want for each row function chech box, could you please help me,how to do it? Thank you very much
Creating the checkboxes is not difficult. The question is what function do you want to execute with each box
Kind Regards
I was able to apply your workaround (thanks). But in my case I have multiple cells throughout my spreadsheet that I would like to increment on demand by clicking on a single checkbox (cell B4 in your example). When B4 is checked, rather than adding 1 to cell B2 (in your example), I want the logic to increment the one cell that had focus right before I clicked on the checkbox in cell B4. Do you know of a way to do that? Thanks again
Mmmm
Very interesting
I´m not sure how to do it
There is a trigger onSelectionChange
Maybe we could try that but it may make the sheet really "heavy"
Kind Regards
How can I add multiple buttons to the same sheet like this? I've tried but something is interfering to where the last one in the script code is the only one that's actually functioning.
You probably need an else if for every other button, where you check if a different combination of sheet name/cell is active, and then you run a different function inside the else if
Thank you so much! This worked for me. :)
Thanks a lot
Thank you!
Thanks! How will it be implemented if there are several buttons and correspondingly several scripts (functions) in the table?
same question
You probably need an else if for every other button, where you check if a different combination of sheet name/cell is active, and then you run a different function inside the else if
can you add link so we can copy your script, taht would be very helpfull. thanks
Hello!
Thanks for the interest
You may download all templates in the patreon page
practicalsheets.com/patreon
Kind Regards
Thank you so much! This is exactly what I was looking for :)
Excellent!
Hope it was helpful
That was amazing!! 😍
Thanks!
Amazing, this helped me a lot! :)
Fantastic video! I was able to get it to work great on my computer (browser) but alas it's not working on my phone or tablet. Any thoughts? I did swap counter for my own function but it worked on PC no issue there. And if I hit "run" from the scripts page it also worked. 0.o
What about a confirmation dialog or popup on mobile? Does that work?
Unfortunately no
Regards!
@@practicalsheets Thank you. It does work using Desktop mode, but it's super cumbersome.
@@LostOrStolen You are right. It's horrible!
That's perfect, thanks a lot !
I only get one script to work at a time, I have no clue to why this happens. I want to have several different buttons that clear different seachbars/ranges. and hope to have a on edit timestamp as well. Do you have any idea to why only one of my functions is functional by any chance? Or where I can find out?
Hello!
You should have only one onEdit
And them imside this onEdit you may include all of the functions you want to execute
Regards!
thanks if i want to create more buttons which work on mobile what i should do
Thanks, great idea
Very interesting. Thanks
Thanks!
this Tutorial is useful
Could you help to use checked box on mobile to move / copy many rows to another sheet
Very good,
Thank you so much for your video. It didnt work for me. I'm not sure why. My desktop buttons work but I cannot get the script to work for the edit you made. Any thoughts? I would love to share with you the code I am using. I cannot find any information on how to get it to work any where else. Your video was the closest thing I've found.
Did you manage?
Regards!
I also have the same issue, the checkbox worked with the counter function, but then when I use my own function it doesn't work, even though the button (and keyboard shortcut) both work
Did you get an answer for that?
@@jeanaimegakwerere8591 yeah. See my other comment
Hi, thanks for this tutorial it's very helpful, I managed to copy it so that my counter function worked on my tablet, however when I replace counter with my own function in the script, it no longer runs... but that function works fine when placed in a button or as a keyboard shortcut.
Can you think of any limitations on why the onEdit function won't run another function? Would it help if I shared the code for that function with you?
Ahh, I think it's because my function imports data from another file - looks like onEdit cannot access other files? Is there a workaround?
@@jonnybolton4659 Hi, I was experiencing the same issue. I had the exact same code as the video but when I wrote my own function it didn't work. I had my cell A1 notation written between single quotation marks like this 'I4', I was so frustrated that just to try I changed them for double quotation marks because I used those in my own function and it worked that way.
So my advice is that you check that you are using the same quotation marks (either single or double) in both your original function and your onEdit one.
I really hope you manage to make it work. Good luck
Thanks. That wasn't the issue though it was because my function was trying to access other files in the Drive. The onEdit trigger doesn't allow this to happen but you can do this with "Installable triggers", I spent all afternoon going down that rabbit hole!
Hello!
I´m glad you figured it out on your own, and apollogies for not getting back earlier.
You are right
There are 2 kinds of triggers in GAS: manual and installable
manual are the functions you write such as onEdit or onOpen
These are great because they remain in the file if you make any copies
However their big limitation is that they cannot connect to services (They can not send an email, creat an event, or connect to another spreadsheet)
This is when the second type of triggers come by. These are called installable triggers. As its name suggests, you need to install them on the "Triggers" menu in your GAS Editor. When you install them or create them they will sak for the permission, thus you won´t need it again
The bad part is that they only exist on that file, that means that if you ever do a copy you need to be careful because the triggers will be erased and you need to create them again
Hope I shed an additional light
Regards!
@@practicalsheets thanks so much! One thing I did notice was when running the sheet in two different accounts, it always accessed the _owners_ files and not the _users_ do you know of a simple fix for this?
+1 sub
Thank you!
How about a function that access another file in drive? This code doesn’t work for me.
For this type of function you need to have an "installable" trigger
onEdit wont work because it needs to acces the Spreadsheet service
These are the steps
1. Change the name of onEdit function to any name you want, for example
function thingsToDoOnEdit(){}
2. In the GAS Editor, at your left go to "Triggers" (A clock icon)
3. CLick the "Add trigger" button
4. Choose your function
5. On "type of event" choose "On edit"
6. Click save
7. enjoy
Let me know if it works
Regards!
Hi
I have used app script for buttons...which can be used for operations from google sheet app in android mobiles.
but I came to conclusion that unless we keep open that sheet in web version in any browser any app script doesn't run...
that mean app script needs at least one web view login through any browser to be in function....
Hello!
Nice comment!
As a rule of thumb, I try to avoid Google Sheets in cell phone, and prefer to use AppSheet or build a webapp
Kind Regards
Can you create a video of how to use this button with your video “WORKOUT LOGGER GOOGLE SHEET”
In mobile, custom dialog is still not showing up, is there a solution for that?
As far as I know, custom dialogs won´t work in mobile
Regards!
Procuro algo assim para menu de navegação. Alguém já desenvolveu algo ?.
I don´t think it is possible to do a menu in Mobile, but I may be wrong
Regards!
ഐ dontknow how to make buton plesehelp me
Just insert any image "over the cells" and now you have a button
Kind Regards
I need a help.can you please help me?
OF course. LEt me know your problem
Regards!
I don't know what is wrong with this. Within the App Scripts It works. BUT when I click on the button nothing happens. Made just the first function is working?
Double check that you included the exact name of the function without parenthesis in your button
Kind Regards
@@practicalsheets REALLY very grateful for your help and knowledge. Here's what I have If copies and pastes to last row and then clears the data.
function NRunCheckBox() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
//Collect the Players
const sourceSheet = ss.getSheetByName("Game");
const sourceRange = sourceSheet.getRange ('B2:I2');
const sourceVals = sourceRange.getValues().flat();
const sourceSheet2 = ss.getSheetByName("Game");
const sourceRange2 = sourceSheet2.getRange ('B12:I12');
const sourceVals2 = sourceRange2.getValues().flat();//added
// Append the data
const destinationSheet = ss.getSheetByName("History");
destinationSheet.appendRow(sourceVals);
const destinationSheet2 = ss.getSheetByName("History");
destinationSheet2.appendRow(sourceVals2);
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B2:I11').activate();
spreadsheet.setCurrentCell(spreadsheet.getRange('I11'));
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};
function onEdit () {
let activeCell = SpreadsheetApp.getActiveSpreadsheet ().getActiveCell()
let reference = activeCell.getA1Notation()
let sheetName = activeCell.getSheet().getName()
let activeValue = activeCell.getValue()
if (reference == "D20" && sheetName == "Game" && activeValue
== true) {
NRunCheckBox() ;
activeCell. setValue(false);
}
}
@@practicalsheets I found something interesting. So the script works with the button but not the checkbox. The checkbox is actually executing the previous script I had. Even though I gave the button a different name and am using the same name as the button in the onedit statement.
I GOT IT WORKING!! Yay thank you again. I had saved it as a macro and that was the problem. Changed it to a script. Now all I need is to get the date to append to the data range line.
@@alicenigl5862 Hello!
Do you by any chance have 2 onedits?
Regards!
I basically used the same code used here: czcams.com/video/ZKYvrD-3Ksc/video.html
You solved it with that?
That IS NOT a button but a checkbox. What a clickbait.
Sorry if you thought it was a mislead. Is the closest to a button you'll be able to do in mobile in Sheets
Kind Regards
@@practicalsheets of course, but the title can be "Create a checkbox that works like a button" for example, that's legit. Otherwise it's clickbait.
@@Nezahual Duly noted. Thank you for the feedback. Kind Regards!