Create a Button that works in mobile in Google Sheets
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- In this tip, we will learn how to create a checkbox that acts as a button so we may execute our Google Apps Script functions from the Google Sheets mobile app
➤You can find the template from this video in my patreon page, where you´ll also have priority responses to your questions
practicalsheets.com/patreon
You can also join my academy practicalsheets.com, where you´ll find, not only the templates for this and all the videos, but also Google Sheets Courses, technical service, and more
➤You can also subscribe to the channel where you´ll find a new Sheets video every week.
Here are some of the places where you can follow and support me:
➤Patreon: practicalsheets.com/patreon
➤Webpage: practicalsheets.com
➤Newsletter: practicalsheets.com/newsletter
➤Telegram: practicalsheets.com/telegram
➤Facebook: practicalsheets.com/facebook
➤Twitter: practicalsheets.com/twitter
Any suggestion, question or insights, feel free to comment below
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 !!!
Useful to know that there is a relatively simple workaround.
Thanks.
Thanks for the comment!
Brilliant - just what I needed - thank you
It's very helpful. Thank you very much.
Lovely solution, and very easy-to-follow tutorial. Thank you! :D
That was incredible! Thank you!
Thank you for commenting!
That's perfect, thanks a lot !
Thank you so much! This worked for me. :)
Amazing, this helped me a lot! :)
Thank you so much! This is exactly what I was looking for :)
Excellent!
Hope it was helpful
Very helpful. Thank you!
Thank you!
This has been SO helpfull !! Thank you so much for the video
NEW SUB !
Thank you so much! Excelent information and explanation. Many thanks...
Thank you!
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.
Thanks, great idea
thank you so much this works so well!
Thank you for commenting!
Regards!
Simple and fantastic. Thank you
Thank you so much!
Regards!
Very interesting. Thanks
Very clever workaround, thank you
Thanks for commenting!
Very usefull, thx. Is it possible to use this way for more check boxes and every check box should counting different cell?
That was amazing!! 😍
Thanks!
this Tutorial is useful
Thanks!
Gracias, me sacaste de un apuro,....
Excelente!
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 good,
Thanks a lot
Thank you!
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
thanks if i want to create more buttons which work on mobile what i should do
Could you help to use checked box on mobile to move / copy many rows to another sheet
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!
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
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
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
can you add link so we can copy your script, taht would be very helpfull. thanks
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?
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
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
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!
Can you create a video of how to use this button with your video “WORKOUT LOGGER GOOGLE SHEET”
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!
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
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!
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!
+1 sub
Thank you!
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!
I need a help.can you please help me?
OF course. LEt me know your problem
Regards!
I basically used the same code used here: th-cam.com/video/ZKYvrD-3Ksc/w-d-xo.html
You solved it with that?
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!
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