0

I need to do the same operations on different cells and I am trying to write a generic script. I give a trivial example that explains my problem below.

Let's say that I want to click on a button and decrease the value of the cell A1 by 1. That's straightforward. I define the following function

function dec_cell_def(){
  var cell = fr = SpreadsheetApp.getActiveSheet().getRange("A1")
  cell.setValue(cell.getValue() - 1)
}

Then assign the "script" dec_cell_def to any button and I am done.

Let's say now, that I want to do this for many different cells. I could of course write a function for each one, but this is hardly a solution. So I thought the following would work. I define the function cell_dec that take as an argument the label of a cell and returns a function that decreases the value of the cell:

function dec_cell(label){
  () => {
    var cell = fr = SpreadsheetApp.getActiveSheet().getRange(label)
    cell.setValue(cell.getValue() - 1)
  }
}

However when I assign the "script" dec_cell("A1") to a button, I am told that the script does not exist. What is the proper way to do that?

PS. I know that I can get the selected cell and act on it, but I would like to have a fixed target for my function.

Edit: I meant that I want to do this for few cells but not all at once. I would like to create a button for each cell and I would like to avoid copy-pasting the same code. I think that none of the suggested questions answer that.

tst
  • 1,117
  • 2
  • 10
  • 21
  • See linked answers. You can dynamically create functions in a loop(say `dec_cell_a1`,`dec_cell_b1`,...) and assign them. If that's not what you're looking for, [edit] your question to explain why the answers don't satisfy your question. – TheMaster Jan 20 '21 at 20:23
  • @TheMaster don't feel it is quite the answer here. Even if the OP makes the menus, the current code is not going to work for multiple cells. – Marios Jan 20 '21 at 20:38
  • 1
    @Marios If OP feels otherwise, I'll reopen this question. I interpreted `I want to do this for many different cells.` to mean multiple buttons individually acting on their respective cells. Your solution doesn't do that. – TheMaster Jan 20 '21 at 20:47
  • @TheMaster I might be mistaken. By the way, [here](https://stackoverflow.com/a/64976479/11225291) I have written a similar solution. By using `onSelectionChange` trigger you can click on a cell and increment the value next to it. The solution can be easily modified, instead of setting the word "button" you can set an cell image of a button there to make it more fancy. Check this out if you have some free time. – Marios Jan 20 '21 at 20:52
  • @TheMaster I added an explanation. I hope it is clear now – tst Jan 21 '21 at 02:24
  • I think it's clear from your explanations, That the duplicates were the answer. The first question creates new functions on the go, so that you can assign them manually or programmatically. But the easiest way is to use a trigger: `onEdit`+ checkbox or `onSelectionChange`+image as suggested in the second and third duplicate answers. Try and see. – TheMaster Jan 21 '21 at 05:30
  • @TheMaster I understand that the questions are related, but I would argue that they are not duplicate, in the sense that I have spent a couple of hours and I cannot figure out the solution. – tst Jan 21 '21 at 15:09
  • @tst If you have a specific problem implementing the solution(s) provided, you need to explain more than `none of the suggested questions answer that`. Show what you've tried and why it doesn't work. Or even ask a new question explaining the specific aspect. Currently @Marios seemed to have directed you towards the linked answer and it is working for you. Is that right? Or do you still have problems? – TheMaster Jan 21 '21 at 15:25
  • @TheMaster I may do that when I will look into that again, I don't think I can waste more time with it now – tst Jan 21 '21 at 22:09

0 Answers0