Google script trying to retrieve what column the selected name is from in the drop down menu-CodePudding

So I am trying to retrieve the column that the selected name in the drop down menu is from. What I'm trying to do is calculate how much damage each character does in my dnd game. So I want to be able to select the character, input how much damage they did their turn, then press the damage button, and the character's damage total will be updated. As you can see I had a button for each cell but the drop down menu looks nicer. I'm just not sure how to go about retrieving the information I need.

Image: https://i.stack.imgur.com/DbreQ.png

Here is the script I have. I know that the getColumn part is wrong, it's just a placeholder as of right now.

function Damage() 

var column = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getColumn(4,7);  //trying to get column from drop down menu)

currentValue =SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(4,column).getValue(); //get current value based on column the drop down menu provided

newValue = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(4,8).getValue();  //get value from H4 (H = column 8)

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(4,6).setValue(currentValue newValue); //set B4 to the sum of those two values

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(4,8).setValue("0"); //Reset H4
}

CodePudding user response：

Try this:

function damage() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues().flat();
  //Logger.log(JSON.stringify(hA));//please provide this value
  let col = {};
  hA.forEach((h, i) => { col[h] = i   1 })
  const column = sh.getRange(4, 7).getDisplayValue();
  //Logger.log(column);//please provide this value
  currentValue = sh.getRange(4, col[column]).getValue();
  newValue = sh.getRange(4, 8).getValue();  //get value from H4 (H = column 8)
  sh.getRange(4, col[column]).setValue(currentValue   newValue);
  sh.getRange(4, 8).setValue("0");
}