0
286
ViewsHow to change the range based on dropdown menu selection
I have a Google Sheet where I am tracking baseball stats for my High School Baseball team. I have the script setup for every time a Hit Error or Run is recorded, I have a checkbox that is onEdit(e) to add +1 to the total stat.
//Away Run
function increment1() {
SpreadsheetApp.getActiveSheet().getRange('X3').setValue(SpreadsheetApp.getActiveSheet().getRange('X3').getValue() + 1);
}
function onEdit(e) {
if (e.range.getA1Notation() == 'G10')
SpreadsheetApp.getActiveSheet().getRange('X3').setValue(SpreadsheetApp.getActiveSheet().getRange('X3').getValue() + 1);
}I have a scoreboard section and a dropdown menu with all of the innings listed; I want to associate the checkbox with a specific range for the total runs and also to each range associated to the current dropdown menu that is selected.
Image of the Google Sheet Scoreboard
Just to break it down a bit more for clarity. Currently when "G10" is edited (Check box selected or deselected), "X3" has +1 to the current Value, additionally I want +1 to be added to a different range based on what is selected in the dropdown menu on "I2". So if "Top 1" is selected in the dropdown "P3" would have +1 to the value when "G10" is edited and so on based on the selected Inning
If "Bot 1" is selected then on edit of "G10" +1 to "P4" "Top 2" +1 to "Q3", "Top 3" +1 "R3", "Bot 3" +1 "R4" all the way to "Bot 7" +1 "V4"
Link to a live copy version of the Sheet
[What Happens] [3]
[What I want on one click][4]
[Change of dropdown menu what happens][5]
[Change of Dropdown what I want to happen][6]
[What I want to happen][7]
1 answers
Answer question0
From your following reply,
I have simplified everything all I want to do is click the check box and have a dropdown menu pointing to a range to add 1 digit to the range selected - Pics in order
In this case, how about the following sample script?
Sample script:
Unfortunately, I couldn't understand the cell coordinates of the checkbox and dropdown list. So, in this sample, the checkbox and dropdown list are put to the cells "A1" and "B1", respectively.
function onEdit(e) {
const obj = {
checkbox: "A1", // Please set the cell coordinate of checkbox.
dropdownlist: "B1", // Please set the cell coordinate of dropdown list.
values: { "K2": "K2", "K3": "K3", "L2": "L2", "L3": "L3" } // Please set the values and the cell coordinates in the dropdownlist.
};
const sheetName = "Sheet2";
const range = e.range;
const sheet = range.getSheet();
if (sheet.getSheetName() != sheetName || range.getA1Notation() != obj.checkbox) return;
const checkboxValue = sheet.getRange(obj.dropdownlist).getValue();
if (!obj.values[checkboxValue]) return;
const editCell = sheet.getRange(obj.values[checkboxValue]);
const value = editCell.getValue();
editCell.setValue(value ? value + 1 : 1);
}
- For example, if you want to add
1to the cell "K5" under the value of "K2", please modifyvalues: { "K2": "K2", "K3": "K3", "L2": "L2", "L3": "L3" }tovalues: { "K2": "K5", "K3": "K3", "L2": "L2", "L3": "L3" }.
Testing:
When this script is run, the following result is obtained.
