Hugely helpful, had no idea it'd be such a pain to search up a solution for what I thought would be a common need, but your explanation was succinct and simple! Much appreciated (:
This is great, thanks! Any ideas to make it work on the app version of the spreadsheet? It works perfectly on my laptop, but when i open the sheet in the app touching the buttons does nothing.
this is kinda exactly what i needed but missing the important part, i want those 2 buttons but they shoudnt only work on the A2 Box, it should increase or decrease the box i highlighted/clicked before. how would i do that?
unfortunately the limitations in google sheets makes light coding necessary. Complain to Google for not having user friendly features. Its BS to have to go through all this, but I appreciate the tutorial
@@AdmiralArchon not sure what the issue is. AFAIK no spreadsheet program can do things like this without coding. This is very much stretching what a spreadsheet does. The general rule is that a cell can be populated by manual edit OR formula and that formulas SHOW things, they do not DO things. So yeah, light coding. Not a Google issue, a spreadsheet fact.
@@SpencerFarris I was intending to respond to the guy digging at your video if how to do it. You must use "coding" to get Google sheets to cooperate. Excel in fact does an excellent job at adding these features without having to use code. In addition, the time it takes for the script to run in Google sheets is crazy for what it is. I ended up just switching to Excel because I need to tick items quickly up or down. Not wait 2-3 seconds for every tick. Again great job explaining what needs to be done, but Google sheets does this very poorly compared to excel.
Shot in the dark here, how would you make a script to reset a value to a value defined within another cell? for example, L9:L13 go down with their own individual buttons. Id like to press one button and refresh the values in L9:L13 to values held in M9:M13
Great video 1 question. What if you have a row of 10 items you wish to increment/decrement, would you need to make a script for each row? i.e. I have row f3-f12, they do not get incremented at once but individually as needed. I'm guessing I will need to copy and paste the script 12 items and adjust with each item to get the scripts to work for each indivdual cell. Any way to program the script to only work on the active selected cell instead?
That is possible using Spreadsheet.getActiveCell(). But you can also make it work with checkboxes that have a position they know. ruclips.net/video/RfAyBORczHc/видео.html
@@SpencerFarris Thank you for the prompt reply. I will check out this video as well and see if I can make it work. This is part of a vanity project on my end. I'm no script kitty, more of a Jack of all Trades
Good day good sir@@SpencerFarris . My concern is like wise, i would like to have increment and decrement button on 20 cells vertically, is there a way to short cut it ? like do i have to copy and paste it then change each designated cells 20times? i would like button not check boxes sir
i want to increment/decrement F by E on 40 different row. i've tried some things but i cant get it to work, i'm pretty sure i can do with less coding than 2 button for each row. i dont mind if every row increment at the same time if i can reset all E row to 0. to make it more concrete, i want to have an initiative sheet for dnd, and on this sheet i want a column with damage/healing received as value (E), a second column beeing the cumulative damage (F), after its easy i have a total hp column (G) and a dead or alive/remaining hp (H) which use a if/else function. i can get the script to work on a button, for each row, but since i have to draw the button for each and its making different size button my ocd are cranking it up XD (if you could help thanks)
Good video. I have a question. How do I increase a cell formula number daily? For example, let's say I have a formula =SUM(B1:B31), and the next day I want it to be =SUM(B2:B31), and the day after that I want the B2 to be B3 and so on and so forth. Thanks!
@@SpencerFarris Thanks for the quick reply. Yes, it's dates. I'm basically looking for a rolling 7 day total. So let's say for today I want =SUM(B23:B29)(cells B30 and B31 are empty as of today), I'm mostly just concerned with being able to increase the first cell # by 1 each day. OR, any method to achieve the desired outdome. Thanks!
whenever I type exactly what you have at @2:04 my execution log just says: Error ReferenceError: range is not defined myfunction @ macros.gs:5 What do I do??😰😱
That's interesting, should work fine. My best guess is that you copied over the onEdit version rather than the button version. You should be using this for buttons: function incrementA2() { const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange("A2"); range.setValue(range.getValue()+1); } function decrementA2(){ const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange("A2"); range.setValue(range.getValue()-1); }
Great video, thank. I have one key problem, I'd like to use the sheet on my android phone, and there the buttons are not active. Looks like the script isn't running on them. Can this be fixed?
Do you know how to prevent it from opening the Picture Editor by clicking the button multiple times too fast? And is it possible to hide the "running script" message?
@@SpencerFarris you can use a picture you upload yourself, instead of using the drawing function of sheets...you cant double klick that one^^ but the "running script" is more complicated...i am still trying to figure out, how to write an app to avoid it
thanks a lot, this was very useful; I've tried replicate this code to perform other action but replacing 1 to 0,01 (to use as percent) and I've get an error message, so… it's possible to use 0,01?
It's asking for authorization / verification of the script. The script runs as you, or the triggering user, so it's just asking you to say "yes, I authorize you to run as me"
Nice and simple thanks. I want to add multiple counters to a sheet that will refer to different target cells, is there an effective way to do this in one code? I have tried adding a target variable in increment(targetCell), but it throughs and error "Exception: Argument cannot be null: a1Notation" assume because it it empty initially, thank you
@@SpencerFarris Does this also mean it's impossible to do the following: Increment a given cell by the amount in another cell when pressing a button. Thank you! I would love to be able to make it a "general" button that works with whatever you give it.
For sure! Let's assume you want to increase/decrease by the value in D2 function incrementA2() { const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange("A2"); let changeVal = sheet.getRange("D2").getValue(); range.setValue(range.getValue()+changeVal); }
This was extremely helpful! Thank you for the easy to follow video. I'd like to add one more thing when I click the button. To add the current date in the cell next to it. Can you help me with that code?
That's impossible, because the button doesn't have a definite position on the sheet that the script can access. Use checkboxes instead ruclips.net/video/RfAyBORczHc/видео.html
Hello, first of all thanks for the vid. I'm new to coding and I would like to increase the value of 2 fields with +1 instead of only one field. I tried different things but was unable to get it running. Could you help me?
@@DandeFX Here is for 2 different cells. Add as many as you would like in the array. function incrementA2() { const sheet = SpreadsheetApp.getActiveSheet(); const ranges = ["A2","E2"]; for (let i in ranges) sheet.getRange(ranges[i]).setValue(range.getValue()+1); }
the code is not working on a new sheet when its assigned the script it says it cannot be found, But works fine in the copied sheet from your description? What am i missing...? They both have the same script name.
@@SpencerFarris for the making it stop at zero, is there any chance you could dumb that explanation down for someone who has zero knowledge whatsoever and just copied & pasted your script? 😅 it'd also be great to know how to cap the counter based on the value of cell, if that's possible. Again, I know nothing about this stuff
Sure. I would just get all the values in the range, decrement everything, then put them back. Something like this if you want all of A2:A function decrementAll(){ const sheet = SpreadsheetApp.getActiveSheet(); const range = sheet.getRange("A2:A10"); let values = range.getValues(); for (let i = 0; i < values.length; i++) values[i][0]--; range.setValues(values); }
Hugely helpful, had no idea it'd be such a pain to search up a solution for what I thought would be a common need, but your explanation was succinct and simple! Much appreciated (:
Glad it helped!
Simple and effective. Much appreciated!
Thank you so so much. I had no idea this was possible
Apps Script allows abilities some consider... unnatural
This is great, thanks! Any ideas to make it work on the app version of the spreadsheet? It works perfectly on my laptop, but when i open the sheet in the app touching the buttons does nothing.
You have to use the checkbox with onEdit(). Buttons do not work on mobile
this is kinda exactly what i needed but missing the important part, i want those 2 buttons but they shoudnt only work on the A2 Box, it should increase or decrease the box i highlighted/clicked before. how would i do that?
ah found it^^ its sheet.getActiveCell() works perfekt^^ thanks for the vid
You would use .getActiveRange() as the thing to increment/decrement
Solved my big problem! Huge thanks!
nice! Just what I was looking for, but I noticed how much it lags. So it's not going to be as fast as choosing numbers from a menu.
So many variables. RAM, browser usage, Internet connection...
here I thought this is a google sheet tutorial, didn't expect it to be a coding tutorial
Hope you're not disappointed
unfortunately the limitations in google sheets makes light coding necessary. Complain to Google for not having user friendly features. Its BS to have to go through all this, but I appreciate the tutorial
@@AdmiralArchon not sure what the issue is. AFAIK no spreadsheet program can do things like this without coding. This is very much stretching what a spreadsheet does. The general rule is that a cell can be populated by manual edit OR formula and that formulas SHOW things, they do not DO things.
So yeah, light coding. Not a Google issue, a spreadsheet fact.
@@SpencerFarris I was intending to respond to the guy digging at your video if how to do it. You must use "coding" to get Google sheets to cooperate.
Excel in fact does an excellent job at adding these features without having to use code.
In addition, the time it takes for the script to run in Google sheets is crazy for what it is.
I ended up just switching to Excel because I need to tick items quickly up or down. Not wait 2-3 seconds for every tick.
Again great job explaining what needs to be done, but Google sheets does this very poorly compared to excel.
Please make a video to do same thing in appsheet
Very helpful! Could you make a function to increment the value in a highlighted cell as well?
WDYM?
@@SpencerFarris I suppose he means a general increment button, which increments the value of a selected cell
@@marco.castiglia Perhaps, which should be simple because you can access the Active Cell
@@SpencerFarris I'm not really capable of doing so imho. A general management button might be quite a thing, you should give it a try
@@marco.castiglia hm?
Shot in the dark here, how would you make a script to reset a value to a value defined within another cell?
for example, L9:L13 go down with their own individual buttons. Id like to press one button and refresh the values in L9:L13 to values held in M9:M13
ss.getRange("L9:L13").setValues(ss.getRange("M9:M13").getValues())
Love your work mate.
Ken oath
Thanks much!
what if I want to do that for column with over 20 positions. Do I have to write 20 scripts for each cell or can I somehow copy it?
With buttons, yes. But I would absolutely use the checkbox option in my other video
@ can you link me to it?
@@Dunleap ruclips.net/video/RfAyBORczHc/видео.html
Great video 1 question. What if you have a row of 10 items you wish to increment/decrement, would you need to make a script for each row?
i.e. I have row f3-f12, they do not get incremented at once but individually as needed. I'm guessing I will need to copy and paste the script 12 items and adjust with each item to get the scripts to work for each indivdual cell. Any way to program the script to only work on the active selected cell instead?
That is possible using Spreadsheet.getActiveCell(). But you can also make it work with checkboxes that have a position they know.
ruclips.net/video/RfAyBORczHc/видео.html
@@SpencerFarris Thank you for the prompt reply. I will check out this video as well and see if I can make it work. This is part of a vanity project on my end. I'm no script kitty, more of a Jack of all Trades
@@gtkaparosGood luck!
Good day good sir@@SpencerFarris . My concern is like wise, i would like to have increment and decrement button on 20 cells vertically, is there a way to short cut it ? like do i have to copy and paste it then change each designated cells 20times? i would like button not check boxes sir
@@josecarlomendoza1231 You will have to make indiovidual buttons with their own script
i want to increment/decrement F by E on 40 different row. i've tried some things but i cant get it to work, i'm pretty sure i can do with less coding than 2 button for each row. i dont mind if every row increment at the same time if i can reset all E row to 0.
to make it more concrete, i want to have an initiative sheet for dnd, and on this sheet i want a column with damage/healing received as value (E), a second column beeing the cumulative damage (F), after its easy i have a total hp column (G) and a dead or alive/remaining hp (H) which use a if/else function.
i can get the script to work on a button, for each row, but since i have to draw the button for each and its making different size button my ocd are cranking it up XD (if you could help thanks)
Use the checkbox method in my other video
Good video. I have a question. How do I increase a cell formula number daily? For example, let's say I have a formula =SUM(B1:B31), and the next day I want it to be =SUM(B2:B31), and the day after that I want the B2 to be B3 and so on and so forth. Thanks!
This feels like an X/Y problem. I imagine there are dates or some other sequence of numbers dictating which rows should be summed?
@@SpencerFarris Thanks for the quick reply. Yes, it's dates. I'm basically looking for a rolling 7 day total. So let's say for today I want =SUM(B23:B29)(cells B30 and B31 are empty as of today), I'm mostly just concerned with being able to increase the first cell # by 1 each day. OR, any method to achieve the desired outdome. Thanks!
@@Holden2341This will be much easier with SUMIFS() comparing dates
Great!!
Is it possible to send the button to a website created by Google sites?
Yes. Lookup "Google Apps Script open url"
whenever I type exactly what you have at @2:04 my execution log just says:
Error
ReferenceError: range is not defined
myfunction @ macros.gs:5
What do I do??😰😱
That's interesting, should work fine. My best guess is that you copied over the onEdit version rather than the button version. You should be using this for buttons:
function incrementA2() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A2");
range.setValue(range.getValue()+1);
}
function decrementA2(){
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A2");
range.setValue(range.getValue()-1);
}
THANK YOU SO MUCH MAN
YOU'RE SO WELCOME
lol also how would I add text before the number counter?@@SpencerFarris
@@feareddevil Like what, exactly?
@@SpencerFarris a example would be like 5 rubies collected
@@feareddevil Oh... that's honestly quite a bit more difficult. How about you make a test sheet and post it here for us to look at?
Great video, thank.
I have one key problem, I'd like to use the sheet on my android phone, and there the buttons are not active. Looks like the script isn't running on them.
Can this be fixed?
onEdit scripts are the only type that work on mobile
What's onEdit? How do i go about getting this? Thanks!!!
@@dandanai1713 ruclips.net/video/RfAyBORczHc/видео.html
Do you know how to prevent it from opening the Picture Editor by clicking the button multiple times too fast? And is it possible to hide the "running script" message?
Neither of those are possible
@@SpencerFarris all good, i did it 😂👌
@@Rubijuana how???
@@SpencerFarris you can use a picture you upload yourself, instead of using the drawing function of sheets...you cant double klick that one^^ but the "running script" is more complicated...i am still trying to figure out, how to write an app to avoid it
thanks a lot, this was very useful; I've tried replicate this code to perform other action but replacing 1 to 0,01 (to use as percent) and I've get an error message, so… it's possible to use 0,01?
What error did you get?
You may need to check the locale settings OR use .01 instead of ,01
so, it only works for 1 cell, if I have 100 cells, i have to do drawings for each one and enter the code for each one
Use checkboxes :)
ruclips.net/video/RfAyBORczHc/видео.html
is there a way to make multiple buttons like this? e. g. i want to have a "+" and a "-" button each for 2 players, how could i make this work?
Sure. Just name them differently
AMAZING! Thank you so much!
Glad it helped!
How do I run this in the copy I made? It's telling me I need Authorization and to verify some app, but I don't know what app.
It's asking for authorization / verification of the script. The script runs as you, or the triggering user, so it's just asking you to say "yes, I authorize you to run as me"
Brother you saved my day 🎉
Nice and simple thanks. I want to add multiple counters to a sheet that will refer to different target cells, is there an effective way to do this in one code? I have tried adding a target variable in increment(targetCell), but it throughs and error "Exception: Argument cannot be null: a1Notation" assume because it it empty initially, thank you
You cannot pass arguments to a function triggered by a button.
@@SpencerFarris Does this also mean it's impossible to do the following:
Increment a given cell by the amount in another cell when pressing a button.
Thank you!
I would love to be able to make it a "general" button that works with whatever you give it.
@@lyn4739 As long as the cell reference is static, that's fine. So if you always want to increment A2 by whatever is in N2, that's fine.
@@SpencerFarris awesome, thank you!
The increment amount will always be in a specific cell.
I will mess around with it :)
Hi, would it be possible to increase/decrease the cell by the value in a separate cell instead of by increments of one? Thank you!
For sure! Let's assume you want to increase/decrease by the value in D2
function incrementA2() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A2");
let changeVal = sheet.getRange("D2").getValue();
range.setValue(range.getValue()+changeVal);
}
How can you make the buttons works in smartphone or touchscreen devices? thank you for your reply
Buttons do not work on mobile. Use checkboxes and onEdit scripts
@@SpencerFarris Thank you for your response... can you show how to do the script for that?
@@Employee1-rl3wd On the other video you found
This was extremely helpful! Thank you for the easy to follow video. I'd like to add one more thing when I click the button. To add the current date in the cell next to it. Can you help me with that code?
That's impossible, because the button doesn't have a definite position on the sheet that the script can access. Use checkboxes instead
ruclips.net/video/RfAyBORczHc/видео.html
many thanks!
Hello, first of all thanks for the vid.
I'm new to coding and I would like to increase the value of 2 fields with +1 instead of only one field.
I tried different things but was unable to get it running. Could you help me?
@spencerfarris
@@DandeFX Here is for 2 different cells. Add as many as you would like in the array.
function incrementA2() {
const sheet = SpreadsheetApp.getActiveSheet();
const ranges = ["A2","E2"];
for (let i in ranges)
sheet.getRange(ranges[i]).setValue(range.getValue()+1);
}
@@SpencerFarris thanks a lot
when i use menu EXTENSION > Apps Script it error "Bad Request, ERROR 400"
I really can't help with that issue. Try googling "apps script error 400" and see if anything helps
the code is not working on a new sheet when its assigned the script it says it cannot be found, But works fine in the copied sheet from your description?
What am i missing...?
They both have the same script name.
Sorry it's been SO LONG. Are you still having issues?
Can I use this script to run when a bar code is scanned
I've never dealt with bar code systems; IDK
Can this be applied to multiple cells with one click? And can it be made to stop at zero?
Sure, just loop through the ranges and check for 0 at the beginning of the loop
@@SpencerFarris for the making it stop at zero, is there any chance you could dumb that explanation down for someone who has zero knowledge whatsoever and just copied & pasted your script? 😅 it'd also be great to know how to cap the counter based on the value of cell, if that's possible. Again, I know nothing about this stuff
@@wesjgoldberg function decrementA2(){
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A2");
range.setValue(Math.max(range.getValue()-1,0));
}
function incrementA2() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A2");
const maxVal = sheet.getRange("B2").getValue();
range.setValue(Math.min(range.getValue()+1,maxVal));
}
Hi, can im Decrement many cells with one button?
Sure. I would just get all the values in the range, decrement everything, then put them back. Something like this if you want all of A2:A
function decrementAll(){
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A2:A10");
let values = range.getValues();
for (let i = 0; i < values.length; i++)
values[i][0]--;
range.setValues(values);
}
Not sure why but this isn't doing anything
This coming from someone that copied what you did part by part
From here I don't know. Can you share your exact sheet with me?
I got tricked by Discord message sound in your video.
Tricked? WDYM?
It is so slow, so unusable!
Lots of factors go into the speed. I do suggest using checkboxes, instead