share
Stack OverflowHow do I use a dropdown menu to change the cell contents of a range?
[0] [1] Isabella Leonard
[2019-09-13 12:47:41]
[ google-apps-script google-apps-script-editor ]
[ https://stackoverflow.com/questions/57923936/how-do-i-use-a-dropdown-menu-to-change-the-cell-contents-of-a-range ]

So I have a file on Sheets, and in the file is a section of cells that are formatted so when you enter "0", they are empty circles, and when you enter any other number (I just go with "1" to make it easy), the circles are filled. I know I could probably use the formulas in the actual sheet, but I'd like the option to edit the cells individually. I really only need the dropdown to fill them all at once or clear them all to save time. And the dropdown has options "Clear" and "Fill".

I have the dropdown menu in grouped cells AG97:AN97 and I'd like to be able to change the cells in W100:W103 (there are more ranges, but one step at a time.)

I've tried using previous questions to help me, but I can't seem to grasp the code.

 function addValidation() {
    var range = SpreadsheetApp.openById('(SHEETID)');
    var sheet = ss.getSheets()[0];
    var values = range.getValues();


  var data = sheet.getDataRange().getValues();

  var rule = SpreadsheetApp.newDataValidation().requireValueInList(["Clear", "Fill"]).build();


  for(var i=0; i<data.length; i++) {

    if(data[i][22] == "Clear") {


      sheet.getRange(i+100, 7).clear().setDataValidation(rule);
    }
  }
}

I replaced the sheet idea just for the purpose of showing code. I tried to get the clear function to work before I tried working on fill, but no luck. I keep getting "Bad value (line 2, file "Code")"

[+1] [2019-09-13 15:11:18] ziganotschka

"Bad value (line 2, file "Code")" means that you inserted your SpreadsheetID incorrectly.

If your Spreadsheet URl is something like

https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit#gid=0, then the ID is the part designated with X - usualy 44 characters long.

Yuo need to pass it to the method openById() in quotes (or double quotes), but without brackets:

SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

Also, probably you want to assign it to a variable called ss rather than range, given your next line is var sheet = ss.getSheets()[0];.


Yeah, that definitely got rid of the error, so thanks very much! But I still need help with the actual code since I have no idea what I'm doing XD would you be able to help me with that as well? - Isabella Leonard
It would be nice to see a snippet of your spreadsheet to understand what exactly you want to do and mean with dropdowns and circles - ziganotschka
imgur.com/PV6JukU There's a link to it. It's for a D&D game. So when I select "Clear" from the dropdown, I'd like the values of the cells W100:W103 to be set to 0. - Isabella Leonard
I changed "var range" to "var ss" but now I'm getting the error "TypeError: Cannot call method "setValues" of undefined. (line 4, file "Code")" line 4 is var values = range.getValues(); - Isabella Leonard
What is your range? You have to define it. E.g. var range=sheet.getRange("AG97:AN97 "); Have a look here: developers.google.com/apps-script/guides/sheets - ziganotschka
"W100:W103" is my range but I get the error ReferenceError: "range" is not defined. (line 4, file "Code") - Isabella Leonard
Because you did not define it correctly. You need to write something like var ss = SpreadsheetApp.openById('(SHEETID)'); var sheet = ss.getSheets()[0]; var range = sheet.getRange("W100:W103"); - ziganotschka
(1) I figured it out, but thanks for the help. Here's what I did.function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var cell = sheet.getRange("AG97"); var cellContent = cell.getValue(); var lvlone = SpreadsheetApp.getActiveSpreadsheet().getRange("W100:W103"); if(cellContent === "Fill"){ lvlone.setValues([ ["1"], ["1"], ["1"], ["1"] ]);} - Isabella Leonard
This works, you can also write lvlone.setValues([ [1], [1], [1], [1] ]); instead given that integers opposed to strings do not need to go in quotes. - ziganotschka
1